VB6 automation error with Excel 2010 (excel 14.0)

[surveyor1]

I have an old vb6 file conversion program (to convert text in fixed width fields to and from excel) which is generally maintained on a windows xp virtual machine with office 2000 installed. The program calls Excel 2000 using early binding with a reference to the Excel 9.0 object library and all has worked okay for years even when the compiled version is used on other machines with excel 2003, 2007 etc installed instead of excel 2000. However, I've run into a problem recently when running with Excel 2010 (excel 14.0) and I don't know if it's syntax or perhaps some security setting in excel which is causing the remote procedure call to fail with a 462 server unavailable error.

The offending line in the original code (having set all the references) says

Set eBQ = appExcel.Workbooks.Open(sFILE)

and I have also tried altering the syntax as below but get the same result

Set eBQ = appExcel.Workbooks.Open(FileName:=sFILE)

Incidentally, if I temporarily alter the line in the IDE to say "Set eBQ = appExcel.Workbooks.Add" the command is accepted so I can create a new workbook but I can't edit an existing one or so it seems.

I have also tried late binding by declaring all the references as object but got precisely the same result

I have also installed vb6 alongside Excel 2007 (excel 12.0) on a different windows 10 machine and the program works okay which narrows it all down to Excel 2010. I have tried the interpreted version on the VB6 IDE and the compiled version with the same error 462 result.

Can anyone help me resolve this issue?

Thanks in Advance

 

[RoryA]

Are you opening local or networked files? Does it make a difference?

 

[surveyor1]

Are you opening local or networked files? Does it make a difference?

They are local files that fail here but I don't think it makes a difference.

I tried isolating the failing code as a separate vb6 project and the complete code below fails when trying to open an excel workbook with Excel 2010.

Public Sub Main()


On Error GoTo cleanup


Dim sBILLDIR As String
Dim sFULL1 As String
Dim nSHEETS As Long


sBILLDIR = "c:\users\public\data\port\bill"
OpenMenu:


frmMake.CDL1.Flags = cdlOFNExplorer + cdlOFNHideReadOnly
'frmMake.CDL1.MaxFileSize = 256
frmMake.CDL1.Filter = "Excel files|*.xls;*.xlsb;*.xlsx;*.xlsm"
frmMake.CDL1.InitDir = sBILLDIR
frmMake.CDL1.FileName = ""
frmMake.CDL1.ShowOpen
sFULL1 = frmMake.CDL1.FileName




'Dim appExcel As Object (Doesn't work with late binding either)
'Dim eBQ As Object
'Dim eSH As Object
'Set appExcel = CreateObject("Excel.Application")


Dim appExcel As Excel.Application
Dim eBQ As Excel.Workbook
Dim eSH As Excel.Worksheet
Set appExcel = CreateObject("Excel.Application")




Set eBQ = appExcel.Workbooks.Open(FileName:=sFULL1)   ' FAILS HERE
'Set eBQ = appExcel.Workbooks.Open(sxeFULL1)


eBQ.Activate
nSHEETS = eBQ.Worksheets.Count
Set eSH = eBQ.Worksheets(nSHEETS)


cleanup:
Set appExcel = Nothing
Set eBQ = Nothing
Set eSH = Nothing








End Sub

 

[RoryA]

Is the Office 2010 suite 32bit or 64bit?

 

[surveyor1]

Is the Office 2010 suite 32bit or 64bit?

Hmmm! It's 64 bit and all the others that it works okay on are 32 bit.

 

[RoryA]

Do you have a 32bit installation you can test with?

 

[surveyor1]

Do you have a 32bit installation you can test with?

Not here, unfortunately. I'll have to see if I can get hold of one.

 

[surveyor1]

I got hold of the 32bit version of excel 2010 & 2013 and it all works swimmingly with those so you were right. An incompatibility with 64 bit excel is the problem.


In the absence of a solution I'll have to flag it up and bypass the bits that don't work. I'm planning on determining if the 64 bit version of excel is in use by getting the version number from application.version (which works in 32 bit and 64 bit) then checking to see if excel.exe is in the appropriate directory of Program Files or Program Files (x86). Unless someone has a better idea?

 

[NdNoviceHlp]

Here's some VB6 code. HTH. Dave

Public Function CheckApplication() As Boolean
'check for XL office installation (paid for)
'check for 32bit installation
'returns False for either No installation OR No 32bit
'ie. If Not CheckApplication Then exit program
Dim ObjExcel As Object
CheckApplication = True
'check for office installation
On Error GoTo ErFix
Set ObjExcel = CreateObject("EXCEL.APPLICATION")
'check office version, Windows OS and for 32 bit installation
#If Win32 Then
'MsgBox ObjExcel.Version & " Version 32 bit with " & ObjExcel.OperatingSystem
#Else
MsgBox "This Program requires a 32-bit Office Installation!"
CheckApplication = False
#End If

ObjExcel.Quit
Set ObjExcel = Nothing
Exit Function
ErFix:
On Error GoTo 0
MsgBox "Office Installation Error."
CheckApplication = False
End Function

To operate....

If Not CheckApplication Then
MsgBox "Office Installation Error."
Exit Sub
End If

 

[surveyor1]

I had something similar in mind but conditional checking for win32 would also rule out 64 bit windows users running 32 bit versions of excel and it works okay on those. After checking that excel actually exists as per your code I need to check for 64 bit excel and currently I can't think of a better way than checking to see if the files are in "Program Files" or "Program Files (x86)"