Printable View
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.
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...
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.
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?
What REFERENCES are you using for EXCEL? Probably your issue...
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.
Can't fix it without the source code....sorry.
I will post the source code tonight.
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.
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).
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...
--Quoted from si_the-geek's signature.Quote:
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:
- Dim oXLApp as Excel.Application
- Dim oXLBook as Excel.Workbook
- Dim oXLSheet as Excel.Worksheet
- ...
You need to change it to:
VB Code:
- Dim oXLApp as Object
- Dim oXLBook as Object
- Dim oXLSheet as Object
- ...
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:
- Set oXLApp = New Excel.Application
You should replace it with:
VB Code:
- 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).
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
'======================
Don't overlook checking out the Late Binding tutorial but the quick fix could be as easy as;
Code:Dim xlApp As Object 'Excel object
Set xlApp = CreateObject("excel.application")
Dim xlBook As Object ' Workbook object
Dim xlSheet As Object 'Worksheet object
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.
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).Code: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
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
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 ?
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:
I didn't include all of the necessary routines, but I can if you think you'll use them.Code: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
Also, when using Excel, be sure to do something like the following:
Code: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
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
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 ?
No VBA
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?Quote:
Originally Posted by si_the_geek
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:
There ya go. :)Code: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
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).
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'd be surprised if you need any Excel 2013 specific code (or even anything Excel 2007 specific).Quote:
Originally Posted by Pasvorto
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.
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.
Thanks to everyone. I got it to work over the weekend.