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

 

[mole999]

have a look at the ENVIRONS() that should return OS etc

 

[NdNoviceHlp]

vba - How to detect if the computer is 32-bit or 64-bit? - Stack Overflow
I'm going to try some of the stuff at the link. I'll post back if something "borrowed" or new seems to work. Dave

 

[NdNoviceHlp]

This all seems to work. I'd be very interested to know what if any of this meets your requirements. HTH. Dave

Public Function isWin64bit() As Boolean
  isWin64bit = 0 < Len(Environ("ProgramW6432"))
End Function

Sub GetVersion()
Dim mVers As String
#If Win64 Then
mVers = "Win64" ' Win64=true, Win32=true, Win16= false
MsgBox "Version: " & mVers, vbInformation, "Version"
#ElseIf Win32 Then
mVers = "Win32"  ' Win32=true, Win16=false
MsgBox "Version: " & mVers, vbInformation, "Version"
#ElseIf Win16 Then
mVers = "Win16"  ' Win16=true
MsgBox "Version: " & mVers, vbInformation, "Version"
#End If
End Sub

Sub GetVersion2()
#If Win64 Then
    #If VBA7 Then ' HKEY_LOCAL_MACHINE\SOFTWARE\YourApp
    MsgBox "Win 64 and Office 64"
    #Else ' HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\YourApp
    MsgBox "Win 64 and Office 32"
    #End If
#ElseIf Win32 Then ' HKEY_LOCAL_MACHINE\SOFTWARE\YourApp
MsgBox "Win 32 and Office 32"
#ElseIf Win16 Then
MsgBox "Win 16"
#End If
End Sub

To test and operate...

If Not isWin64bit Then
MsgBox "32 bit"
Else
MsgBox "64 bit"
End If

Call GetVersion

Call GetVersion2

 

[surveyor1]

This all seems to work. I'd be very interested to know what if any of this meets your requirements. HTH. Dave

Public Function isWin64bit() As Boolean
  isWin64bit = 0 < Len(Environ("ProgramW6432"))
End Function

The isWin64bit function works fine in vb6 but I'm not trying to determine the version of windows but the version of office.

The other two tests don't work because with VB6 running on windows 10 64 bit, you get the same result as VB6 running on windows XP 32 bit. That is
Win64 false
Win32 true
Win16 false

 

[surveyor1]

have a look at the ENVIRONS() that should return OS etc

I used ENVIRON() as per the excerpt below but not without a surprise. Both Environ("ProgramFiles") and Environ("ProgramFiles(x86)") returned "C:\Program Files (x86)" on my machine here. In order to return "C:\Program Files" I had to use Environ("ProgramW6432")


sVER is the excel version in use. IsWin64bit() is as described earlier in the thread

If IsWin64bit And Val(sVER) > 13 Then
    'check to see if excel 64 bit is being used in Excel 2010 (excel 14) or later
    sEXCELPROG32 = Environ("ProgramFiles(x86)") & "\Microsoft Office\Office" & Val(sVER) & "\EXCEL.EXE"
    sEXCELPROG64 = Environ("ProgramW6432") & "\Microsoft Office\Office" & Val(sVER) & "\EXCEL.EXE"
    If fs.FileExists(sEXCELPROG32) = True Then
        '32 bit excel installed so all is okay
        bX64 = False
    ElseIf fs.FileExists(sEXCELPROG64) = True Then
        '64 bit excel installed so warn about limitation
        bX64 = True
    Else
        'shouldn't get this far
        MsgBox "Note: A 32 bit version of Excel must be installed on the system for full functionality."
    End If
End If

 

[NdNoviceHlp]

Thanks for posting your outcome. Unfortunately, your code doesn't work in newer versions of office. I messed around with this a fair bit and seem to have come up with, what is for me, the most robust and logically appealling. It should work for all XL versions and Windows operating systems and it is not dependent on finding files that MS may rename or remove. HTH. Dave

 Public Function isWin64bit() As Boolean
'check Windows OS for 64 bit installation (true=64bit)
isWin64bit = 0 < Len(Environ("ProgramW6432"))
End Function

Public Function CheckApplication() As Boolean
'checks for XL office installation
'checks OS for 64 bit
'checks for 32bit Excel installation
'returns False for either No installation OR 64 bit instal
'ie. If Not CheckApplication Then exit program
Dim ObjExcel As Object, L As Long
CheckApplication = True
'check for office installation
On Error GoTo ErFix
Set ObjExcel = CreateObject("EXCEL.APPLICATION")
'check Windows OS for 64 bit installation
If isWin64bit Then
'.hInstance returns a correct handle only in the 32-bit version of Excel.
'The HinstancePtr property works correctly in both 32 & 64bit versions of Excel.
'ie. If hinstance errors it must be a 64 bit instal (L remains set at -1)
L = -1
On Error Resume Next
L = ObjExcel.hInstance
On Error GoTo 0
If L = -1 Then
MsgBox "64 bit XL on 64 bit OS" & vbCrLf _
& "This program requires a 32 bit Excel installation!"
CheckApplication = False
'Else
'MsgBox "32 bit XL on 64 bit OS"
End If
'Else
'MsgBox "32 bit XL on 32 bit OS"
End If
ObjExcel.Quit
Set ObjExcel = Nothing
Exit Function

ErFix:
On Error GoTo 0
ObjExcel.Quit
Set ObjExcel = Nothing
MsgBox "Microsoft Excel not installed!"
CheckApplication = False
End Function

 

[surveyor1]

Thanks for posting your outcome. Unfortunately, your code doesn't work in newer versions of office. I messed around with this a fair bit and seem to have come up with, what is for me, the most robust and logically appealling. It should work for all XL versions and Windows operating systems and it is not dependent on finding files that MS may rename or remove. HTH. Dave

Yes, I agree it's significantly better than looking for MS default locations of files and I've adopted that part of it. You will need bypass the check on earlier versions of office though. Office 2000 generates an error on .hInstance. I don't know about 2003. Not that you need to check for a 64 bit versions of office2000 anyway. I just used the .version property to perform the check only on office 2010 or later.