9 Replies - 9396 Views - Last Post: 29 January 2011 - 01:34 AM Rate Topic: -----

#1 tsorby  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 15
  • Joined: 22-January 11

Excel worksheet and VB6.0

Posted 22 January 2011 - 06:51 PM

I'm trying to create a program for my wifi antenna(s). I'm have a problem in VB opening my excel worksheet, an error say the file can't be found..I've pointed to it a thousand times and it just wont open.
I'm using excel 2007 and VB6.0
Is This A Good Question/Topic? 0
  • +

Replies To: Excel worksheet and VB6.0

#2 tsorby  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 15
  • Joined: 22-January 11

Re: Excel worksheet and VB6.0

Posted 22 January 2011 - 09:25 PM

Let me explain a bit more of what I'm doing.....I have a excel worksheet called "fracdec", with three columns of info, A is fractions, B is the decimal value, C is four values that I will use to raise or lower my antenna. I know exactly where the file is, but I cant get it past the cant find file, or is it renamed bit. I just need a little push in the right direction to get going.
This all started because my wifi is weak, LOS is not that far but my signal strength is too weak to connect for any lenght of time, unless I move the antenna, si I want to move it remotely.
Was This Post Helpful? 0
  • +
  • -

#3 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Excel worksheet and VB6.0

Posted 23 January 2011 - 02:09 AM

fracdec is the name of the workbook(the file on your disk) or the name of the sheet(the sheet inside the workbook that has another name)?

This post has been edited by Ionut: 23 January 2011 - 02:10 AM

Was This Post Helpful? 0
  • +
  • -

#4 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Excel worksheet and VB6.0

Posted 24 January 2011 - 01:36 AM

i am not sure what you are try to do?
but any way first post your code here otherwise we are not able to detect the error in your code?
Was This Post Helpful? 0
  • +
  • -

#5 tsorby  Icon User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 15
  • Joined: 22-January 11

Re: Excel worksheet and VB6.0

Posted 25 January 2011 - 10:08 PM

Well people,
I need to explain what I want to do...
Excel 2007 has a worksheet called"fracdec.xls" this holds the fractions(column A) from 0 to 8 inches...from "A2" through "A514". I want this visible in a text box called txtCP, I have two command buttons, one ^, and the other v, these will raise or lower this value in txtCP....the command buttons controlled by (column C)...this column holds the decimal values(4) of which I want to add or subtract from(1/64,1/16,1/8,1/4). I want to assign these values to variable CDcol using option buttons. (column B)holds the decimal values of column A.
So basically the math part will be.....AFcol=BDcol+/-CDcol, where CDcol is contrlled by opt1-4.
The optimum will be adding a progress bar to show the signal strength...
Here is the whole project code so far.

Option Explicit

Dim excelApp As Excel.Application
Dim excelWB As Excel.Workbook
Dim excelWS As Excel.Worksheet
Dim AFcol As Integer
Dim BDcol As Integer
Dim CDcol As Integer
Dim i, h, j As Integer


Private Sub Form_Load()
' Then set the variable with the GetObject method.
Set excelApp = CreateObject("Excel.Application")


Set excelWB = GetObject _
("E:\twinportssystems\antennaproject\vbantenna\fracdec.xlsx")

excelApp.Visible = False

excelApp.DisplayAlerts = False



Dim AFcol(0 To 8)
Dim i As Integer
For i = 0 To 8
' Fill the array with five hundred fourteen values from column A of
' the worksheet.
AFcol(i) = excelWB.Worksheets(1) _
.Range("A" & i + 1).Value

Next i




Dim BDcol(0 To 8)
Dim h As Integer
For h = 0 To 8
' Fill the array with five hundred fourteen values from column B of
' the worksheet.
BDcol(h) = excelWB.Worksheets(1) _
.Range("B" & h + 1).Value
Next h



Dim CDcol(1 To 5)
Dim j As Integer
For j = 1 To 5
' Fill the array with five values from column C of
' the worksheet.
CDcol(j) = excelWB.Worksheets(1) _
.Range("C" & j + 1).Value

Next j

End Sub

Private Sub CPlbl_Click()
If AFcol = 0 Then CPlbl "Your at the Bottom!"
If AFcol = 4 Then CPlbl "Your at the Midle!"
If AFcol = 8 Then CPlbl "Your at the Top!"
End If
End Sub

Private Sub TxtCP_Change(Index As Integer)
Set excelWB.Worksheets(1).Cells("A2").Value = AFcol
AFcol = 0
TxtCP.Text = AFcol
End Sub

Private Sub Opt1_Click()
Set excelWB.Worksheets(1).Cells("C2").Value = Opt1
If Opt1 = True Then Opt1 = CDcol

End If

End Sub
Private Sub Opt2_Click()
Set excelWB.Worksheets(1).Cells("C3").Value = Opt2
If Opt2 = True Then Opt2 = CDcol
End If

End Sub

Private Sub Opt3_Click()
Set excelWB.Worksheets(1).Cells("C4").Value = Opt3
If Opt3 = True Then Opt3 = CDcol
End If

End Sub

Private Sub Opt4_Click()
setexcelWB.Worksheets(1).Cells("C5").Value = Opt4
If Opt4 = True Then Opt4 = CDcol
End If

End Sub

Private Sub CmdUp_Click()
AFcol = BDcol + CDcol
End Sub

Private Sub CmdDown_Click()
AFcol = BDcol - CDcol
End Sub

Private Sub CmdClose_Click()

excelWB.Close

Excel.Application.Quit

Set excelApp = Nothing



End Sub

Private Sub Form_Terminate()
Dim f As Form

For Each f In Forms

If f.hWnd <> Me.hWnd Then

Unload f

Set f = Nothing

End If

Next f
Unload Me

End Sub


Private Sub CloseWorkSheet()
On Error Resume Next
'Close the Excel workbook
Excel.Workbook.Close
'Quit from Excel application
Excel.Quit
End Sub

Private Sub ClearExcelMemory()
'Clean-up the memory, check first, whether
'the Excel object exists or not in the memory ...
If Not excelWS Is Nothing Then Set excelWS = Nothing
If Not excelWB Is Nothing Then Set excelWB = Nothing
If Not excelApp Is Nothing Then Set excelApp = Nothing

End Sub


Thanks for your help
Was This Post Helpful? 0
  • +
  • -

#6 bohemian9485  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 38
  • Joined: 05-August 09

Re: Excel worksheet and VB6.0

Posted 26 January 2011 - 12:30 AM

Why did you used this

Set excelWB = GetObject _
("E:\twinportssystems\antennaproject\vbantenna\fracdec.xlsx")



when you already declared your Excel object in

Set excelApp = CreateObject("Excel.Application")



Shouldn't it be:

Set excelApp = CreateObject("Excel.Application")
Set excelWB = excelApp.Workbooks.Open("E:\twinportssystems\antennaproject\vbantenna\fracdec.xlsx")


Was This Post Helpful? 1
  • +
  • -

#7 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Excel worksheet and VB6.0

Posted 26 January 2011 - 08:17 AM

Actually, you will be running the macro from an excel application(aka a workbook). I don't think it's necessary to declare another ExcelApplication objece because will you'll have two excel.exe processes started, so a waste of resources.
Dim myWorkbook As Workbook
Set myWorkbook = Workbooks.Open(path)



To check if a file exists on disk, use Dir function
if Dir("E:\twinportssystems\antennaproject\vbantenna\fracdec.xlsx") <> vbNullString then
    Set myWorkbook = Workbooks.Open(path)
end if 


This post has been edited by Ionut: 26 January 2011 - 08:17 AM

Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Excel worksheet and VB6.0

Posted 29 January 2011 - 12:29 AM

View PostIonut, on 26 January 2011 - 08:17 AM, said:

Actually, you will be running the macro from an excel application(aka a workbook). I don't think it's necessary to declare another ExcelApplication objece because will you'll have two excel.exe processes started, so a waste of resources.
Dim myWorkbook As Workbook
Set myWorkbook = Workbooks.Open(path)



To check if a file exists on disk, use Dir function
if Dir("E:\twinportssystems\antennaproject\vbantenna\fracdec.xlsx") <> vbNullString then
    Set myWorkbook = Workbooks.Open(path)
end if 



You have to declare an Excel.Application object variable. If you already have Excel open, you can use GetObject to point your variable to an existing instance of Excel.Application. Otherwise you have to use CreateObject and then send commands through that. Bohemian's is the right way to go about it.
Was This Post Helpful? 1
  • +
  • -

#9 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Excel worksheet and VB6.0

Posted 29 January 2011 - 01:26 AM

Quote

You have to declare an Excel.Application object variable. If you already have Excel open, you can use GetObject to point your variable to an existing instance of Excel.Application. Otherwise you have to use CreateObject and then send commands through that. Bohemian's is the right way to go about it.


I did some tests and I saw that for an Excel configured to SDI, Bohemian was correct. I've been working with macros inside MDI Excel and what I said applies. Thanks for pointing that out.
Was This Post Helpful? 0
  • +
  • -

#10 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Excel worksheet and VB6.0

Posted 29 January 2011 - 01:34 AM

View PostIonut, on 29 January 2011 - 01:26 AM, said:

Quote

You have to declare an Excel.Application object variable. If you already have Excel open, you can use GetObject to point your variable to an existing instance of Excel.Application. Otherwise you have to use CreateObject and then send commands through that. Bohemian's is the right way to go about it.


I did some tests and I saw that for an Excel configured to SDI, Bohemian was correct. I've been working with macros inside MDI Excel and what I said applies. Thanks for pointing that out.

That's interesting. You can probably just open a workbook for the same reason that you can double click an xls file and have Excel open. I didn't think about that either. Then you could probably use GetObject to reference the Excel Application object, because it would be open due to having opened the workbook. Can't test it on my machine right now, but it seems logical.

tsorby, you're doing the equivalent of opening up Excel and then attempting to access a file without first opening it. You can't do that. So open your workbook. Then you wouldn't use GetObject either, you can just reference the workbooks collection.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1