[RESOLVED] VB6 & Excel newbie question

[Aelanna]

I am relatively new with working with Office from inside VB, and have never done this before, so would like some tips.

I have VB6 and Office 97. What I want is to know how to do basic stuff to an Excel sheet from inside a VB program, such as:

open an existing workbook
Find/Go to a particular worksheet inside that workbook
Find the last row in use on that sheet
Find the contents of a cell in that row
Copy a formula from one cell to another, so that the formula in the new cell has references to the new row it sits in
Write values from variables into cells on the sheet
How to get the current date and put it into a cell
How to set the format of a cell or range of cells

What I'm doing is trying to write a program that will update a bunch of workbooks/sheets from inside one program, rather than my opening each sheet or book and manually typing in changes.

[RobDog888]

To automate Excel from VB6....

Option Explicit
'Add a reference to MS Excel xx.0 Object Library
Private moApp As Excel.Application
 
Private Sub Command1_Click()
    Dim oWB As Excel.Workbook
    
    moApp.Visible = True
    Set oWB = moApp.Workbooks.Open("C:\Book1.xls")
    oWB.Sheets(2).Activate
    MsgBox "Last used row: " & oWB.Sheets(2).Cells.SpecialCells(xlCellTypeLastCell).Row
    MsgBox "Contents: " & oWB.Sheets(2).Cells.SpecialCells(xlCellTypeLastCell).Value
    
    oWB.Close SaveChanges:=False
    Set oWB = Nothing
End Sub
 
Private Sub Form_Load()
    Set moApp = New Excel.Application
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    If TypeName(moApp) <> "Nothing" Then
        moApp.Quit
    End If
    Set moApp = Nothing
End Sub

[Aelanna]

Ok, where you have:

oWB.Sheets(2).Activate

What if I want to reference a sheet by its name, which is stored in a string text variable? Such as 'Alydar286'?

It looks like you answered the first half of my question list, and that in itself is a help, thanks! Now, to get at that other half...

[RobDog888]

You can reference sheet names instead of index numbers.

Application.WorkBooks("Book1.xls").Sheets("Alydar286").Select 'Or.Activate

[RobDog888]

Here are some more answers.

Dim strName As String
strName = "RobDog888"
oWB.Sheets(2).Cells(1, 1).Value = strName
oWB.Sheets(2).Cells(2, 1).Value = Date

[Aelanna]

Thank you! My project is humming right along except for a snag I just hit...

I have Office 2000 AND Office 97 loaded on my machine. The reason is I have older programs that need Office 97 and won't function with Office 2000 documents.

Now, when I use this code to open up an existing spreadsheet, it opens it in Excel 2000. How can I tell it to open it using Excel 97 instead?

[RobDog888]

Hard way: ShellExecute API to start Excel 97 and pass the workbook to open as a parameter. Only issue is knowing the fill path to the 97 location. Then once its started you will have to GetObject to attach to the Application Instance.

Easy way: place code in your app to make it late bound and use which ever version is on the users system. Then select case on the version and only do things that that version supports.

[Aelanna]

Hard way: ShellExecute API to start Excel 97 and pass the workbook to open as a parameter. Only issue is knowing the fill path to the 97 location. Then once its started you will have to GetObject to attach to the Application Instance.

How do you do this?

Easy way: place code in your app to make it late bound and use which ever version is on the users system. Then select case on the version and only do things that that version supports.

It's not a matter of things that are supported, it's the format. I suppose it would be ok to open and use 2000 to make simple changes, but then the program would have to Save As the file to 97 format, or the other older programs would no longer be able to open it.

[RobDog888]

Easy way: Use latebinding so the version could be any. Then when you need to save the workbook, save it using the 95/97 Excel file format.

ActiveWorkbook.SaveAs Filename:="C:\Book1.xls", FileFormat:=xlExcel9795

[Aelanna]

Well, then that leads me to the question, what is latebinding?

[RobDog888]

Ok, Early Binding vs. Late Binding...

'Early Binding
'Add a reference to MS Excel xx.0 Object Library
Private moApp As Excel.Application
 
'Late Binding
'No reference added to Excel at all
Private moApp As Object

Early binding locks in your reference to that version of Excel. Late binding does not. It only binds to the version that is available on the system.

With Early binding you get the intellisense popups showing the functions, methods, and properties of your object variable. Late binding does not support any intellisense since it doesnt know what it is until runtime.

[Aelanna]

Early binding locks in your reference to that version of Excel. Late binding does not. It only binds to the version that is available on the system.

Ok, the problem with that is, my system has BOTH versions available, so it's going with the newer version. I tried chang my reference from version 9 (Office 2000) to version 8 (Office 97) and it still opened the sheet using Excel 2000.

I'm thinking I may have to just uninstall Office 2000, since the only thing I use it for anyway is on occasion opening an Access 2000 database. But I'd like to be able to just tell my program use THIS version of Excel!

[RobDog888]

Then were back to the ShellExecute API thing. If this is for deployment then you will also have to dynamically find the location of Excel in order to shell it.

[Aelanna]

No, this particular program will only be used on my own system. But I do know the exact path to my Excel 97 executable. So, how do I go about doing this?

[RobDog888]

Here is the code that will open an excel workbook using the specified version.

Option Explicit
 
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
 
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
 
Private Sub Command1_Click()
    ShellExecute Me.hwnd, "Open", "Excel.exe", "D:\Book1.xls", "D:\Program Files\Microsoft Office\OFFICE11", SW_SHOWMAXIMIZED
End Sub

[Aelanna]

Ok, now how do I get to where I can move around in the workbook to different sheets and put stuff in cells, etc.? You mentioned GetObject earlier?

[RobDog888]

I'm working on an example but its a bit more complex. Will be back in a few.

[Aelanna]

Ok, I'll be waiting. The project works well to this point. I pull information out of a database to populate some combo lists, select the stuff I want on the form, enter a few strings into some textboxes, click the button, and the correct version of Excel now comes up and opens the sheet I want to work on. Just need to figure out how to send my info to the sheet.

[RobDog888]

Thats an easy one. To save the workbook you do this

Application.WorkBooks("Book1.xls").Save
 
'Or without the prompt dialog if its a new workbook.
Application.WorkBooks("Book1.xls").SaveAs FileName:="C:\Book1.xls"
Application.WorkBooks("Book1.xls").Saved = True

[Aelanna]

It's not the saving I need, it's the stuff before the saving. I have all these variables now with info in them that I need to put into the sheet into cells. I need to do all the stuff that I was talking about at the beginning of the thread: finding the last row, putting values into cells, copying formulas from one cell to another, etc.

[RobDog888]

Oh, ok. Then its post #5 to write values to a particular cell and sheet.

[Aelanna]

Ok, cutting and pasting some relevant code from other posts, I have something roughly like this:

Private moApp As Excel.Application
 
Private Sub Command1_Click()
    Dim oWB As Excel.Workbook
    Dim strName As String
    
    moApp.Visible = True
    Set oWB = moApp.Workbooks.Open("C:\Book1.xls")
    
    oWB.Sheets(2).Activate
    Application.WorkBooks("Book1.xls").Sheets("Alydar286").Select 'Or.Activate
    
    MsgBox "Last used row: " & oWB.Sheets(2).Cells.SpecialCells(xlCellTypeLastCell).Row
    MsgBox "Contents: " & oWB.Sheets(2).Cells.SpecialCells(xlCellTypeLastCell).Value
 
    strName = "RobDog888"
    oWB.Sheets(2).Cells(1, 1).Value = strName
    oWB.Sheets(2).Cells(2, 1).Value = Date
    
    Application.WorkBooks("Book1.xls").Save
 
    'Or without the prompt dialog if its a new workbook.
    Application.WorkBooks("Book1.xls").SaveAs FileName:="C:\Book1.xls"
    Application.WorkBooks("Book1.xls").Saved = True
 
    oWB.Close SaveChanges:=False
    Set oWB = Nothing
End Sub
 
Private Sub Form_Load()
    Set moApp = New Excel.Application
End Sub

Now, will these sorts of things work given that I had to use the API call to ShellExecute in order to open Excel and the sheet rather than the normal way? Also, with the mention of GetObject, I'm confused as to whether I can use the regular code to do what I want to do once the sheet is opened with the API call, or if I have to somehow use GetObject and do something else.

[RobDog888]

You dont need the SaveAs code if you want to save the workbook as the same name. In that case just the .Save is sufficient.

We are only going to need the api if you need to open a certain version of excel. If not then the default version that openss will be easiest and allow us to use the code like we are.

[Aelanna]

Right, but in my case it was determined that I do need the API unless I uninstall Office 2000 from my system. My program now uses the API to open an instance of Exel 97 and opens the Workbook in question that I need. For ease of illustration, I think I'll just post my code here:

Private Sub Command1_Click()
 
    Dim Excel As Excel.Application
    Dim Book As Excel.Workbook
    Dim Row As Integer
    Dim Column As String
    Dim Msg, Style, Title, Response
 
    Msg = "Process this sale ?"
    
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "Process Sale"
 
    Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then    ' User chose Yes.
    
            Set Excel = New Excel.Application
             
            ShellExecute Me.hwnd, "Open", "Excel.exe", "C:\FHR\StableBankAccounts.xls", "C:\Program Files\Microsoft Office\Office", SW_SHOWMAXIMIZED
            
            Excel.Visible = True
            
            ' *** Place for code to do cell manipluations
    
        End If
 
End Sub

This is my code as it currently sits. It opens Excel 97 and opens the workbook 'StableBankAccounts', which consists of multiple sheets, all with names such as 'Alydar286'. What I want to do is go to a particular sheet by name, find the last row, put my variables into cells on that row, copy a forumla from one cell to another, then save the sheet and close everything.

[RobDog888]

It will end up looking something like this without the api part complete.

Private Sub Command1_Click()
 
    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    Dim iRow As Integer
    Dim strColumn As String
    Dim strMsg As String, strStyle As String, strTitle As String 
    Dim iResponse As Integer
    Dim lRet As Long
 
    strMsg = "Process this sale ?"
    
    strStyle = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    strTitle = "Process Sale"
 
    iResponse = MsgBox(Msg, Style, Title)
        If iResponse = vbYes Then    ' User chose Yes.
    
            'Set oExcel = New Excel.Application 'Not needed until later (API part)
             
            lRet = ShellExecute(Me.hwnd, "Open", "Excel.exe", "C:\FHR\StableBankAccounts.xls", "C:\Program Files\Microsoft Office\Office", SW_SHOWMAXIMIZED)
            
            ' ToDo: *** Place API code to get the Excel App object
 
 
            'oExcel.Visible = True
            'Set oBook = oExcel.WorkBooks(StableBankAccounts.xls)
            ' *** Place for code to do cell manipluations
            oBook.Sheets(1).Cells(1, 1).Value = "Something"
            'Do other writting to cells
            '...
            '...
            'Save updates and clean up obejcts
            oBook.Save
            oBook.Close
            Set oBook = Nothing
            oExcel.Quit
            Set oExcel = Nothing
        End If
 
End Sub

[Aelanna]

Ok, now you've confused me again. I thought the ShellExecute WAS the api part. What's missing?

[RobDog888]

Yes, it is "part" of the APIs needed. The other part is iterating through the processes and only checking the Excel ones for the correct workbook, just in case you have more then one instance open.

[RobDog888]

The GetObject function does not discriminate on the process, only the class type and server name - "Excel.Application"

[Aelanna]

Ahhh, ok, I see. I will wait for that part, then.

[Aelanna]

Ok, Rob, I don't need the API any more.

While waiting for your reply, I decided to see what would happen if I didn't use the API at all or the ShellExecute and did it the usual way, and let Excel 2000 do it. I got it to open my workbook, find the proper sheets, put the proper data in the proper cells, and save the sheet using SaveAs. Then I tried opening it in Excel 97, and it still opens fine and works like normal! So I guess I don't need to specify Excel 97 after all, and we can consider this question at last resolved!

Of course, I now have another question, but that's a topic for another thread.