Open Excel File

[piowtrpolo]

I am new to VB6 and need some quick help. Well I hope this isn't to hard to do. All I need the program to do is open an excel file I already have on my desktop. I have a program already that opens a new excel file every time, but I don't need that. Also if possible I would like it to start on sheet 2 instead of sheet 1. I'll post the code I have. Please tell me if it has to be redone in order for this to work. Thank you.

Option Explicit
'Variables for Excel
'Microsoft Excel 9.0 Object Library
Dim exlApp As Excel.Application
Dim exlWSheet As Object
Dim exlRow As Integer
Dim exlCol As Integer
Dim strExcelPath As String

Private Sub Form_Load()

'Start Excel and open new spreadsheet
Set exlApp = New Excel.Application
exlApp.Workbooks.Add
Set exlWSheet = exlApp.Workbooks(2).Worksheets(1)
exlApp.Visible = True
exlRow = 1
exlCol = 1

End Sub

 

[Swi]

This should suite your needs:

Dim oXLApp As New Excel.Application
Dim oXLBook As New Excel.Workbook
Set oXLBook = oXLApp.Workbooks.Open("C:\Test.xls")
oXLBook.Worksheets(2).Select
oXLApp.Visible = True
Set oXLBook = Nothing
Set oXLApp = Nothing

Try the macro recorder for futher help.

Swi

 

[Golom]

Dim exlApp As New Excel.Application
exlApp.WorkBooks.Open ("C:\myPath\myWorkBook.xls")

 

[piowtrpolo]

For some reason neither idea seemed to open up the folder I would like to open up. I'm going to post the entire program. Maybe there was something some where else which I missed than you may need to help me out.

'Tate Allen - Cognex Corp
'Feb 14, 2003
'Demo code that shows how to use In-Sight in conjuction with Excel using VB
'when camera is triggered, the In-Sight spreadsheet will be updated
'and cause data to spew forth majestically from the TCPDevice to local_Port
'the data will be caught by wsTCPDevice_DataArrival and then be put into Excel

Option Explicit
'Variables for Excel
'Microsoft Excel 9.0 Object Library
Dim exlApp As Excel.Application
Dim exlWSheet As Object
Dim exlRow As Integer
Dim exlCol As Integer
Dim strExcelPath As String

'Flag for wsNative reply
Dim dataArrivalFlag As Boolean
Dim nativeReply As String

'Local port that TCPDevice function
'on In-Sight will connect to
Dim local_Port As Integer
  'dateStr = Now


Private Sub cmdStartServer_Click()
'Sets wsTCPDevice to listen for connection requests on local_Port
'that port is determined by the TCPDevice function in the In-Sight spreadsheet

On Error GoTo probablyPortInUse

If Not wsTCPDevice.State = sckClosed Then

    wsTCPDevice.Close

End If

wsTCPDevice.LocalPort = txtLocalPort
wsTCPDevice.Listen
cmdStartServer.Caption = "Listening......"

Exit Sub
    
probablyPortInUse:
    If Err.number = 10048 Then
        MsgBox "Error: local port is already in use. Please enter another local port number and start server again", , "Error: " & Err.number & " " & Err.description
    Else
         MsgBox "Error", , "Error: " & Err.number & " " & Err.description
    End If

End Sub

Private Sub Form_Load()

'Start Excel and open new spreadsheet
Set exlApp = New Excel.Application
exlApp.Workbooks.Open ("C:\Documents and Settings\lab\Desktop\75W Data.xls")
Set exlWSheet = exlApp.Workbooks(1).Worksheets(2).Select
exlApp.Visible = True
exlRow = 1
exlCol = 1

End Sub



Private Sub Form_Unload(Cancel As Integer)

On Error GoTo exlerror

Set exlWSheet = Nothing

exlApp.Quit
Set exlApp = Nothing
End

exlerror:
    'do NOTHING
    End
End Sub

Private Sub mnuExit_Click()
    Form_Unload 1
    End
End Sub



Private Sub wsTCPDevice_DataArrival(ByVal bytesTotal As Long)

Dim str As String

str = ""
    
wsTCPDevice.GetData str
putDataInExcel (str)

End Sub


Private Sub wsTCPDevice_ConnectionRequest(ByVal requestID As Long)

If wsTCPDevice.State <> sckClosed Then wsTCPDevice.Close

wsTCPDevice.Accept (requestID)

If wsTCPDevice.State = sckConnected Then
    cmdStartServer.Caption = "Connected"
    cmdStartServer.Enabled = False
Else
    cmdStartServer.Enabled = True
    cmdStartServer.Caption = "Click to Start Excel Server"
End If
    
End Sub


Private Sub wsTCPDevice_Error(ByVal number As Integer, description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, CancelDisplay As Boolean)

wsErrorHandler number, description

End Sub

Private Sub wsErrorHandler(number As Integer, description As String)

MsgBox description, , "Connection Error... winSock Connection has been closed"
wsTCPDevice.Close
cmdStartServer.Enabled = True
cmdStartServer.Caption = "Click to Start Excel Server"

End Sub

Private Function putDataInExcel(data As String)
'Called from wsTCPDevice_DataArrival
'parses out data from In-Sight and puts it directly into excel spreadsheet

On Error GoTo excelError

Dim index As Integer
'Dim dataval As String
Dim delim As String
Dim term As String
term = vbCrLf
delim = txtDelimiter

'reset exlRow
exlRow = 1

'get index of first delimiter
index = InStr(data, delim)
While index <> 0
    'pick off data and put into excel
    exlWSheet.Cells(exlCol, exlRow) = Left(data, index - 1)
    'trim data off string
    data = Mid(data, index + 1)
    exlRow = exlRow + 1
    index = InStr(data, delim)
Wend
'trim off termininator and
'get last piece of data into excel
exlWSheet.Cells(exlCol, exlRow) = Mid(data, index + 1, InStr(data, term) - 1)

'add dateStamp if box is checked
If chkDateStamp.Value = 1 Then
    exlRow = exlRow + 1
    exlWSheet.Cells(exlCol, exlRow) = Now
End If

exlCol = exlCol + 1
Exit Function

excelError:
    'Excel is probably closed
    MsgBox "Error: Excel was shut down", , "Error: " & Err.number & " " & Err.description

End Function
[\code]

 

[piowtrpolo]

Alright I got it to work. Now all I need to do is make a new .exe file out of this. Anyone know how to do that?

 

[johnwm]

File menu - Make .exe

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[Swi]

piowtrpolo,

Were any of these posts helpful? Did you accomplish the task at hand?

Swi