[RESOLVED] Excel upgrade

[Pasvorto]

I have an application that is written in VB6. In a number of places it either creates or updates an Excel spreadsheet. Everything was working just fine until IT upgraded the users from MS Office 2007 to MS Office 2013. Now it doesn't work. Is there a place where I can see what code needs to be changed to use MS Office 2013? I am assuming the logic that gets the data, etc. will not change, only the code that creates or updates the spreadsheet.

[Arnoutdv]

When running in these kind of troubles I always use Virtual Machines with different versions of Excel installed.
Because if you update your automation code to work with 2013, maybe you break functionality for users still running Excel 2007.

Also try to specify what doesn't work...

[Pasvorto]

The development PC is a virtual machine with 2007 on it. I was hoping to do some sort of check before processing. If dir <> = blah, blah, blah and have 2 subroutines, one for 2007 and one for 2013.

[Arnoutdv]

Up until now I didn't find the need to have a different automation code base for different versions of Excel.
But maybe the functionality I use for automation is not that complex.

Can describe what doesn't work anymore?

[SamOscarBrown]

What REFERENCES are you using for EXCEL? Probably your issue...

[Pasvorto]

I wish I had the source code at hand, but I don't. However, on the Windows 7 PC with Excel 2013 it throws an error at the first line of code that is creating the workbook.

[SamOscarBrown]

Can't fix it without the source code....sorry.

[Pasvorto]

I will post the source code tonight.

[Magic Ink]

Very likely you are automating Excel using Early Binding which locks you into compatibility with a single version of Excel ie. the one installed when you compile it.
To have compatibility across multiple versions of Excel you will need to use Late Binding.

[si_the_geek]

I haven't tested against Excel 2013, but I agree that it does seem like the issue is that you are not Late Binding.

For an explanation of how to use Late Binding, see my Excel tutorial (link in my signature).

[SamOscarBrown]

Just in case you couldn't find si's great tutorial...here is the section that describes how to convert your Early Bound code to Late Bound...

Unless you can guarantee that all of your users have the same version of Excel installed as you do, I would strongly recommend converting to Late-bound for software that you release "into the wild".

The down-side to using this is that you no longer get some of the nice features of the VB IDE (like the drop-down lists of properties and methods that appear when you type oXLApp. ), so it may be a good idea to convert to this method once your code is finished.
There are four steps to convert your code:
1) Replace Excel data types with Object.
If you have:
VB Code:

1. Dim oXLApp as Excel.Application
   
2. Dim oXLBook as Excel.Workbook
   
3. Dim oXLSheet as Excel.Worksheet
   
4. ...

You need to change it to:
VB Code:

1. Dim oXLApp as Object
   
2. Dim oXLBook as Object
   
3. Dim oXLSheet as Object
   
4. ...
   

Note that you should also do this with any other variables that you have declared as Excel.something
2) Change the initialisation of the application object.
If you have: VB Code:

1. Set oXLApp = New Excel.Application

You should replace it with:
VB Code:

1. Set oXLApp = CreateObject("Excel.Application")

Note: if Excel isn’t installed, this line of code will cause an error – you should deal with this by using error handling in an appropriate way for your project.
3) Define the constants that you have used.
Excel constants (e.g.: xlLeft) are defined in the object library; however you will no longer have a link to this, so they wont be defined any more!
There are two main options here; the first is to use the Object Browser to find the values/declarations for each one you have used (very slow, and prone to errors!), and the other (much simpler) is to add a pre-made module to your project which declares them all for you. You can find a link to one which Microsoft produced in the “useful functions and downloads” section (post #13).
4) Remove "Excel object Library" from the list in "Project" -> "References"
Your code is now late-bound, and should work with all versions of Excel that support automation (as long as you haven’t used special functionality which wasn’t available in earlier versions).

--Quoted from si_the-geek's signature.

[Pasvorto]

Here is my code

Dim xlApp As Excel.Application 'Excel object
Set xlApp = CreateObject("excel.application")
Dim xlBook As Excel.workbook ' Workbook object
Dim xlSheet As Excel.Worksheet 'Worksheet object
Dim iworksheetcount As Integer
'
'------------------------------
'start Excel using automation
'------------------------------
'
xlApp.Workbooks.Add
Set xlBook = xlApp.Workbooks(1)
Set xlSheet = xlBook.Worksheets(1)

... some code

xlBook.Worksheets.Add
xlBook.Worksheets(1).Name = "Line" & rs1![line]

...some code

For Each xlSheet In xlBook.Sheets
'-------------------------------------------------
' Add column headings
'-------------------------------------------------
'set font & color
'
xlSheet.Range("A1:M1").Font.Bold = True
xlSheet.Range("A1:M1").Font.ColorIndex = 1
xlSheet.Range("A1:M1").Interior.ColorIndex = 4
' 1 = BLACK, 2 = WHITE, 3 = RED, 4 = GREEN)
'
'set column alignment
'
' MsgBox "Doing alignment"

xlSheet.Range("A1").HorizontalAlignment = xlLeft
xlSheet.Range("B1").HorizontalAlignment = xlRight
xlSheet.Range("C1").HorizontalAlignment = xlLeft
xlSheet.Range("D1").HorizontalAlignment = xlCenter
xlSheet.Range("E1").HorizontalAlignment = xlRight
xlSheet.Range("F1").HorizontalAlignment = xlRight
xlSheet.Range("G1").HorizontalAlignment = xlRight
xlSheet.Range("H1").HorizontalAlignment = xlCenter
xlSheet.Range("I1").HorizontalAlignment = xlRight
xlSheet.Range("J1").HorizontalAlignment = xlRight
xlSheet.Range("K1").HorizontalAlignment = xlRight
xlSheet.Range("L1").HorizontalAlignment = xlRight
xlSheet.Range("M1").HorizontalAlignment = xlLeft
'
'set heading text
'
' MsgBox "doing headers"

xlSheet.Range("A1").Value = "Assignments"
xlSheet.Range("B1").Value = "Line #"
xlSheet.Range("C1").Value = "Description"
xlSheet.Range("D1").Value = "Job #"
xlSheet.Range("E1").Value = "Order Qty"
xlSheet.Range("F1").Value = "Status"
xlSheet.Range("G1").Value = "Conf Ship Date"
xlSheet.Range("H1").Value = "Must Run"
xlSheet.Range("I1").Value = "Staffing"
xlSheet.Range("J1").Value = "Labor Hrs"
xlSheet.Range("K1").Value = "# of Shifts"
xlSheet.Range("L1").Value = "Goal"
xlSheet.Range("M1").Value = "Comment"
'
'set column widths
'
' MsgBox "column width"

xlSheet.Columns(1).ColumnWidth = 25
xlSheet.Columns(2).ColumnWidth = 10
xlSheet.Columns(3).ColumnWidth = 25
xlSheet.Columns(4).ColumnWidth = 10
xlSheet.Columns(5).ColumnWidth = 10
xlSheet.Columns(6).ColumnWidth = 10
xlSheet.Columns(7).ColumnWidth = 20
xlSheet.Columns(8).ColumnWidth = 10
xlSheet.Columns(9).ColumnWidth = 10
xlSheet.Columns(10).ColumnWidth = 10
xlSheet.Columns(11).ColumnWidth = 10
xlSheet.Columns(12).ColumnWidth = 10
xlSheet.Columns(13).ColumnWidth = 25
'
'test to see if i can change colors
'
' MsgBox "color test"

xlSheet.Range("A2").Value = "test black"
xlSheet.Range("A2").Font.Bold = False
xlSheet.Range("A2").Font.ColorIndex = 1

xlSheet.Range("A3").Value = "test red"
xlSheet.Range("A3").Font.Bold = True
xlSheet.Range("A3").Font.ColorIndex = 3

xlSheet.Range("A4").Value = "test back to black"
xlSheet.Range("A4").Font.Bold = False
xlSheet.Range("A4").Font.ColorIndex = 1
'
Next xlSheet

... soem code

xlBook.SaveAs ("C:\TECHNI\PROD_TEST.xls")

MsgBox "Spreadsheet created. Look for C:\Techni\Prod_test.xls"
xlApp.Quit
Set xlApp = Nothing
'======================

[Magic Ink]

Don't overlook checking out the Late Binding tutorial but the quick fix could be as easy as;

 Dim xlApp As Object     'Excel object
 Set xlApp = CreateObject("excel.application")
 Dim xlBook As Object   ' Workbook object
 Dim xlSheet As Object    'Worksheet object

[Elroy]

Just checking into this one.

Yes, I agree with Sam. I've used Excel and Word automation for years with VB6, and I've found that "late binding" is definitely the way to go. I don't even have any reference to either Excel or Word in my VB6 programs. Here's a cut-down version of how I get Excel going from VB6.

Public Function ExcelApp() As Object
    ' This provides late binding of Microsoft Excel so that
    ' the version doesn't need to be known before binding.
    '
    ' Be SURE to execute a obj.Quit at some point to remove the copy of Excel from memory.
    Dim obj As Object
    '
    On Error Resume Next
    '
    Err.Clear
    Set obj = CreateObject("Excel.Application")
    '
    If Err <> 0 Then
        obj.Quit
        Set obj = Nothing
        MsgBox "Error.  Microsoft Excel was not found on this computer.  Microsoft Excel must be installed on this computer for this program to execute this feature.", vbCritical, "Error."
    End If
    '
    Set ExcelApp = obj
End Function

And I just keep a shortcut to the Excel VBA help on my desktop so that I've got easy access to it. Also, the macro-record functionality in Excel is great. I often use it to create a macro and then copy-paste that code into my VB6 project to do certain things (patching up the top level Excel object, of course).

Are you also using Excel macros (in addition to your VB6 program)? If so, I can tell you that there were LOTS of bugs in the Excel 2007 VBA. They were fixed (for the most part) in Excel 2010 and beyond.

Best Of Luck,
Elroy

[Bobbles]

I have a friend who uses a program I wrote for him, which automates Excel.
I have been begging him to NOT upgrade to 2013, as I am frightened that MS will break backwards compatibility in the 2013 version (Just a gut feeling I have, based on MS's disdain for backwards compatibility, elsewhere).

Are my fears wrong ?

[Elroy]

The following is something else I do (specifically the "UserWontCloseExcel" function). I find that it's quite confusing for the user to watch Excel being automated. Furthermore, they can foul things up if they click around on Excel while I'm trying to use it.

To circumvent any problems, I always make them get out:

Option Explicit

Public Enum MsgStyleEnum
    mbYesNoCancel = &H3&
    mbYesNo = &H4&
    mbRetryCancel = &H5&
    mbOKCancel = &H1&
    mbOkOnly = &H0&
    mbAbortRetryIgnore = &H2&
End Enum
Public Enum MsgIconEnum
    mbCritical = 16
    mbExclamation = 48
    mbInformation = 64
    mbQuestion = 32
    mbSystemModal = 4096
End Enum

Public Function UserWontCloseExcel(hWndOfForm As Long) As Boolean
    Dim ret As String
    Dim msg As String
    '
    Do
        If ExcelIsOpen Then
            msg = "Microsoft Excel appears to be open.  You need to save your work and close this application before running this procedure."
            msg = msg & vbCrLf & vbCrLf & "Another option is to KILL Excel.  However, BE CAREFUL if you choose this option." & vbCrLf & "You will NOT be asked to save any work before Excel is terminated !!"
        Else
            Exit Function ' We're fine, so get out.
        End If
        '
        ret = MsgBoxEx(hWndOfForm, mbAbortRetryIgnore, App.Title, msg, mbCritical, "Retry", "Cancel", "KILL", 3000)
        If ret = "Cancel" Then ' User said: forget it.
            UserWontCloseExcel = True
            Exit Function
        End If
        '
        If ret = "KILL" Then KillProcess "excel.exe" ' It kills all occurences.
        ' If we're looping, Excel was open, and the messagebox timed out.
    Loop
End Function

Public Function ExcelIsOpen() As Boolean
    ExcelIsOpen = (hWndFromMidTitle("Microsoft Excel") <> 0)
End Function

Public Function MsgBoxEx(hWndOwner As Long, Style As MsgStyleEnum, Title As String, message As String, Optional Icon As MsgIconEnum, Optional ButA As String, Optional ButB As String, Optional ButC As String, _
                         Optional MilliSeconds As Long) As String
    ' Didn't include, but willing to.
End Function

Public Sub KillProcess(sTheProcess As String)
    ' Didn't include, but willing to.
End Sub

Public Function hWndFromMidTitle(sTitlePiece As String)
    ' Didn't include, but willing to.
End Function

I didn't include all of the necessary routines, but I can if you think you'll use them.

Also, when using Excel, be sure to do something like the following:

Dim xls as Object
Set xls = ExcelApp() ' From above post.
xls.Visible = False

...
' And then, at some point.
xls.quit ' Be sure to do this BEFORE you lose the Excel reference.
set xls = Nothing

Regards,
Elroy

[Elroy]

Just read Bobbles post, and I can say that I've got several clients using Excel 2013, and automation is working perfectly fine for me. For years, I would shudder when clients upgraded MS-Office, but the only really bad upgrade I've had was with Office 2007. I now forbid my clients from using that version.

The only somewhat large hurdle I've had to jump over with automation (and it's not exactly automation) is getting my Excel VBA macros to work in all environments. Getting the exact same VBA macros to work on Excel 32-bit and Excel 64-bit, specifically when they have API calls, is a bit tricky, but not impossible.

It's sort of interesting, but I actually automate Excel 64-bit macros from VB6 using the "xls.Application.Run ..." feature. I do try my best to get everything done from the VB6 side, but it's just not practical in certain situations.

Regards,
Elroy

[Bobbles]

Thanks for the feedback Elroy
I believe there are two versions of 2013 - purchased(installable), and leased (possibly web based ? ?)
Will it matter which version he gets ?

[Pasvorto]

No VBA

[Pasvorto]

I haven't tested against Excel 2013, but I agree that it does seem like the issue is that you are not Late Binding.

For an explanation of how to use Late Binding, see my Excel tutorial (link in my signature).

I was looking at your tutorial. For now I would like to develop on a PC that has Excel 2007 but use the Excel 2013 coding. Do you think that is possible? Once I have 2013 on the dev machine there is no going back. I would like to compile a 2013 version and test it before I pull the plug on 2007. Am I making sense?

[Elroy]

Okay, I think Excel 2007 will work just fine so long as you aren't using any macros in it. It was the VBA (i.e., macros) that I had all kinds of problem with.

@Bobbles, I have absolutely no experience with the Office 365 stuff. So long as it's a version of Office/Excel that's installed on the machine (i.e., not web based), I haven't had any problems with VB6 to Excel automation for any version. That includes 64-bit versions. The only little-bit of a hiccup is the .SaveAs feature, and also getting all the extensions straight: .XLS vs .XLSX. The later .SaveAs features allow for both methods. Let me dig out my saving procedure:

Option Explicit
'
Public Const xlExcel8 = 56&
Public Const xlOpenXmlWorkbookMacroEnabled = 52&
'

Public Sub ExcelSave(wbk As Object, Optional bForceOldFormat As Boolean = True)
    If bForceOldFormat Then
        If Int(wbk.Application.Version) > 11 Then ' It's Office 2007 or greater.
            wbk.CheckCompatibility = False
            wbk.Save
        Else
            wbk.Save
        End If
    Else
        wbk.Save
    End If
End Sub

Public Sub ExcelSaveAs(wbk As Object, sFileSpec As String, Optional bForceOldFormat As Boolean = True)
    If bForceOldFormat Then
        If Int(wbk.Application.Version) > 11 Then ' It's Office 2007 or greater.
            wbk.CheckCompatibility = False
            wbk.SaveAs sFileSpec, xlExcel8
        Else
            wbk.SaveAs sFileSpec
        End If
    Else
        If UCase$(Right$(sFileSpec, 4)) = "XLSM" Then
            wbk.SaveAs sFileSpec, xlOpenXmlWorkbookMacroEnabled
        Else
            wbk.SaveAs sFileSpec
        End If
    End If
End Sub

There ya go.

[Elroy]

Actually, that code in my latest post illustrates the only bit of a pain with late-binding, you don't get the constants. But it's easy to get them. Just pop open Excel, open the VBA, do ctrl-G to get the debug window, and then say: Print xlConstantOfInterest

Then, just declare it in your VB6 project. And PLEASE don't just paste in the numbers. Be kind to someone coming in behind you (or even yourself 6 months down the road).

[si_the_geek]

Many of the constants are easy to get - my tutorial contains a download for a VB6 module containing many of them (but I haven't checked the download recently).

I was looking at your tutorial. For now I would like to develop on a PC that has Excel 2007 but use the Excel 2013 coding. Do you think that is possible? Once I have 2013 on the dev machine there is no going back. I would like to compile a 2013 version and test it before I pull the plug on 2007. Am I making sense?

I'd be surprised if you need any Excel 2013 specific code (or even anything Excel 2007 specific).

The vast majority of code that people write to automate Excel is compatible with all versions from Excel 2000 upwards. I could be wrong, but I think my tutorial (written in 2006) was actually based on Excel 2000, and I still haven't heard of any problems with it.

[Elroy]

If we're still talking about automating from VB6, all I can say is that my primary application does precisely that (and in a vast variety of ways). Furthermore, I have clients using everything from Excel 2003 to Excel 2013 (including some 64-bit versions), and everything works perfectly. If we're not talking about any VBA macros, the only got'cha I can think of is the way the Excel files are saved, which I addressed in post #21.

[Pasvorto]

Thanks to everyone. I got it to work over the weekend.