# VB6 automation error with Excel 2010 (excel 14.0)



## surveyor1 (Mar 1, 2016)

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 (Mar 1, 2016)

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


----------



## surveyor1 (Mar 1, 2016)

RoryA said:


> 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 (Mar 1, 2016)

Is the Office 2010 suite 32bit or 64bit?


----------



## surveyor1 (Mar 1, 2016)

RoryA said:


> 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 (Mar 1, 2016)

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


----------



## surveyor1 (Mar 1, 2016)

RoryA said:


> 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 (Mar 6, 2016)

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 (Mar 7, 2016)

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 (Mar 7, 2016)

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)"


----------



## surveyor1 (Mar 1, 2016)

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 (Mar 7, 2016)

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


----------



## NdNoviceHlp (Mar 8, 2016)

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 (Mar 8, 2016)

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 (Mar 11, 2016)

NdNoviceHlp said:


> 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
> ...



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 (Mar 11, 2016)

mole999 said:


> 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 (Mar 28, 2016)

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 (Mar 28, 2016)

NdNoviceHlp said:


> 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.


----------

