VB6 32bits accessing Office 64bits

[Thierry69]

Well, on a client site, they are migrating all the Office 32 bits to 64 bits.
And that could be a problem, as maybe some application written in VB6 are using Outlook/Excel/Word 32 bits, but will be 64 bits.

So, it will be incompatible with VB6 App.

Is someone here faced this? Or found a solution?

My first idea is writing a wrapper in .NET that will access 64 Office, and communicate with VB6 App.
This will reduce de rewriting of applications.

I will start to analyze all this, but if someone as an existing solution, it is welcome.

Thanks

[Arnoutdv]

According to me 32bit applications can not communicate with 64bit applications.
So we always advice customers to use 32bit versions of MS-Office, if they want to use the modules which rely on MS-Office automation.

[Thierry69]

I know Arnoutdv, but I think that a .NET Wrapper (in 64 bits) could do the job, then a XMLRPC communication between VB6 & .NET (http://comxmlrpc.sourceforge.net/)
Or some other way of communication.
Not yet investigated deeper

[DEXWERX]

Well, on a client site, they are migrating all the Office 32 bits to 64 bits.

That's dumb... Even Microsoft doesn't recommend that.
Last I checked the default install media was 32bit, and you had to jump through hoops to get 64bit installs, but it's been a few years since I looked.

That means you're also throwing out any 32bit only ODBC drivers.

What was the business case for this? 6GB spreadsheets?

/endrant.


ActiveX EXEs are out of process - so you can use those to interface with 64bit.

[Elroy]

I've faced this problem too, but only minimal. In the corporate setting, all of my clients intend to stick with 32-bit Office for the foreseeable future. However, I do have a couple of client employees who have personal computers on which they wish to run my software.

For the most part, I've pulled all the macros (i.e. VBA) out of my Office files and pulled it into my VB6 project. And, having done that, I've solved some of my problems. For one, if my VB6 project can find a copy of Office 32-bit on the machine, it can still create Office files. And I tend to use Office for several reports (with .DOTx or .XLSx templates stored in my executable's resources).

However, there is one specific Excel file that will always have some VBA macros in it. In fact, it can be used to get quite a bit of work done without even using my VB6 program. In one case in particular (a client employee), she wanted to use this file on her personal computer (with Office 64-bit). So, I took the time to "smarten it up", and now it works.

There were, I'd say, about a dozen API calls in it, several having to do with common dialog. The secrets were in the new LongPtr type and the PtrSafe directive in API calls. The LongPtr type was a somewhat neat trick by Microsoft. Here's what it does. If we're on 32-bit Office, it creates our old friend the Long. If we're on a 64-bit Office, it creates the new LongLong type. Therefore, the bytes of pointers are different depending on which bit Office software we're on.

You can also just directly create LongLong types, but I had no need for that.

I haven't done much testing to see how well VB6 will interface with Office 64-bit (if at all). I just don't know if any Automation can be accomplished, as I don't have Office 64-bit installed on my development machine. I thought one client employee actually got it going, but I'm not at all certain about that.

And just for reference, all I use is Word and Excel (from VB6). I've only dabbled with trying to automate PowerPoint, Access, and other Office programs ... with none of that being in my primary project.

Anyway, I think that's where we stand.

Elroy

[Elroy]

Thierry,

One more thought. I don't believe there's a problem with installing both Office 32-bit and Office 64-bit on the same machine. If they're upgrading, it's possible they have enough old Office 32-bit licenses for the new machines. If you just make sure their file associations are set to Office 64-bit, they may be happy with that solution.

That way, all your Automation from VB6 to Office (including running VBA macros), could be accomplished with Office 32-bit, and then use ShellExecute to show them things with Office 64-bit.

Just An Idea,
Elroy

p.s. You may still want to upgrade any VBA macros with LongPtr and PtrSafe, so they can use them when the files are called up with ShellExecute (or through their own double-click).

[Thierry69]

>DEXWERX, the client is using Excel in an INTENSIVE way, with very heavy excel sheets and very complex calculation (Financial)

I am migrating an Access App to support both 32 & 64 bits, by changing API calls

ActiveX could also be a solution for vb6 App that access Word/Excel/Outlook directly, I'll test that.
I will create a Virtual machine with Office 64 installed inside

Elroy, you can't install on the same computer Office 32 & Office 64.

[Krool]

You can access a Vb6 Ax-dll within office 64 bits.
However, there was a registry tweak necessary in some syswow64 node. I did that already and worked.

[Thierry69]

Krool, was it this thread : http://www.vbforums.com/showthread.p...e-and-VB6-dlls

[dilettante]

Most of the "better" automation servers ("ActiveX EXEs") like MS Office programs register at installation for access from both 32- and 64-bit automation clients.

So if your VB6 programs need to puppet-string EXcel or something it shouldn't be an issue.

The problem comes from trying to go the other way around: Office VBA trying to make use of a VB6 DLL/OCX.

One way around that is to compile them as ActiveX EXEs. Another is a custom installer that registers your VB6 DLL to use either the system proxy stub or a custom proxy, which a thin adapter that makes it look like an ActiveX EXE for crossing the bitness barrier.

[The trick]

There is no problem to use 64-bit office from VB6 app because the office is the out-of-process server. There is no problem to use VB6 Dlls from 64 bit office too (using DllSurrogate). There is no problem to use COM objects between 64/32 bits processes too, marshaling does all things.

[Elroy]

There is no problem to use 64-bit office from VB6 app because the office is the out-of-process server. There is no problem to use VB6 Dlls from 64 bit office too (using DllSurrogate). There is no problem to use COM objects between 64/32 bits processes too, marshaling does all things.

Well there you go.

I suspect I really do need to do more testing of my Office Automation (from VB6) with a 64-bit version of Office. I've been told it works fine, but I do make extensive use of Automation in certain places.

It's good to know that Office 64-bit could use a VB6 DLL, but personally, I don't envision a need for that. Maybe others do.

[Thierry69]

Well, as Elroy, more testing are need, but good to know

[LameNRG]

I apologize for resurrecting this post, however, I am in a situation where I am not incredibly well versed in VB6. My company is rolling out Office 64, while I am working on a migration of a VB6 application to .NET, I have no way to control what software is deployed on company computers. In anyone's testing, has anyone documented what works and what doesn't. I still must maintain this application for the foreseeable future, until my migration is complete.

[Arnoutdv]

At this moment we and most of our clients use Office 64.
Normal Excel automation from within a VB6 application still works.

[Elroy]

Yeah, since 2019, I've now got several clients using my primary VB6 application with Office 64-bit installed. My application does substantial automation of Office (mostly Word and Excel) programs, and there's no problem at all.

The only problem I've run into is where there's some VBA code in some template that Office is directed to use, but that's really a separate issue. For VBA code that you want to be compatible with both Office 32-bit and Office 64-bit, you must learn about the LongPtr and PtrSafe stuff when API calls are made. If there are no API calls, even that works just fine in either Office 32-bit or Office 64-bit.

[LameNRG]

Okay, that's good to hear, I did some initial testing and I didn't have any issues that I could find. However, as a large team of one, I just wanted to make sure that there were not any issues that I wasn't aware of. I have been lurking on the forum conducting research and testing for a while. Visio x64 is my next challenge, that is the only thing that I am not able to get working/automated.

[Episcopal]

Taking advantage of the Thread ... what is the difference between 32bit and 64bit access? What is the advantage for each other?

[Elroy]

Taking advantage of the Thread ... what is the difference between 32bit and 64bit access? What is the advantage for each other?

Personally, I'm not sure I understand the question. What's the difference between installing MS-Office-32-bit versus MS-Office-64-bit, given that the Windows OS is 64-bit? Hmmm, I guess you'd have to ask Microsoft that one.

If you're talking about VB6 automating MS-Office-32-bit versus MS-Office-64-bit, there's not much difference at all. That's sort of the point of this thread.

[VB6 Programming]

If you're talking about VB6 automating MS-Office-32-bit versus MS-Office-64-bit, there's not much difference at all. That's sort of the point of this thread.

One issue is Outlook. You can't send emails directly from a VB6 app if the Outlook version is 64 bit.
If you use a third-party tool such as Outlook Redemption or vbMAPI to get around issues with the Outlook Object Model then the bitness of your app has to match the bitness of Outlook - which rules out VB6.

[fabel358]

This is my experience: I have office 2013 64 bit and VB6 which performs some data export functions both on Word, Excel and Powerpoint documents and everything works fine as with office 32 bit. for example

.ParagraphFormat.Alignment = WDALIGNPARAGRAPHCENTER; .TypeText ; oRange.ConvertToTable vbTab, NumColumns:=1, Format:=GridWDStyle, ApplyBorders:=True, ApplyShading:=True, ApplyFont:=False, ApplyColor:=True, ApplyHeadingRows:=True, ApplyLastRow:=True, ApplyFirstColumn:=True, ApplyLastColumn:=True, AutoFit:=True

and so on...

[Episcopal]

Personally, I'm not sure I understand the question. What's the difference between installing MS-Office-32-bit versus MS-Office-64-bit, given that the Windows OS is 64-bit? Hmmm, I guess you'd have to ask Microsoft that one.

If you're talking about VB6 automating MS-Office-32-bit versus MS-Office-64-bit, there's not much difference at all. That's sort of the point of this thread.

None of the answers. I mean about the data limit in gigabytes, writing and reading speed, and the like.

[Arnoutdv]

Reviving this old thread.

My application is able to create Excel files (xlsx) without the need for Excel Automation
But in some cases there is also the need to create an Excel Pivot Table or an Excel Chart.
For this I use basic Excel Automation.

Now one of my clients is experiencing problems when Excel Automation is needed.
All clients (including my own company) use Excel 64 bit, but this client uses Excel 32 bit.
Also I don't have access to Windows installation with Excel 32 bit installed for local testing (not easy even to get a VM..)

The call to pOpenExcel doesn't give an error, but just returns Nothing for xlApp
Does anyone have an idea what could be improved in the method I use?



This is the reference I use:
[x] Microsoft Excel 16.0 Object Library

This is the routine I use to get an instance of Excel

Private Function pOpenExcel(xlApp As Excel.Application, sCallingMethod As String) As Boolean

10      On Error Resume Next
20      Err.Clear
        
30      Set xlApp = CreateObject("Excel.Application")
40      If Err.Number = 429 Then
50        On Error GoTo errHandler
60        Set xlApp = New Excel.Application
70      End If
        
80      pOpenExcel = True
        
90      GoTo Proc_Finish
        
errHandler:
100     DebugLog "Error: " & Err.Number & " on line: " & Erl & vbLf & _
                 "Description: " & Err.Description & vbLf & _
                 "Calling method: " & sCallingMethod & vbLf & _
                 "in Function pOpenExcel() of Form frmHyperCube", False
110     DebugLog TranslateItem(Me.Name, "excelneeded", "", "[For this action Excel needs to be installed!]"), True

Proc_Finish:
120     On Error GoTo 0
End Function

The calling routine:

Private Sub pCreatePivotTable() 
  Dim xlApp As Excel.Application
  Dim oXW   As Excel.Workbook
  Dim oXS   As Excel.Worksheet

  If Not pOpenExcel(xlApp, "pCreatePivotTable") Then Exit Sub

  ..
End Sub

[Thierry69]

Have you tried without any explicit reference ?
In my apps, I don't have any references set. Everything is done dynamically and no problems.
NB : I don't create Pivot Tables within my code
Maybe a solution could be to call a batch file that call a macro doing within the excel ?

[Arnoutdv]

You mean completely remove the reference to Excel and use "Object" instead everywhere?
I will make an isolated test application for the user.
One with Excel.Application and another version which use Object instead of Excel references.
Thanks for the suggestion!

[Thierry69]

Exactly
I have 32 Office bits nmy computer, and most of the clients are 64 bits
and no problems

[zx81sp]

Use "Object", all works fine between vb6 & Excel64.

Example:

Dim xlApp As Object
Dim xlWorkbook As Object
Dim xlWorksheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Open(excelFilePath)
Set xlWorksheet = xlWorkbook.Worksheets("MiSheet")

...

[Arnoutdv]

@zx81sp, I've have the opposite problem, one of my clients is using Excel32.
Then my code just returns an empty xlApp, without raising any errors.

But I made a test app and shared it with the client in which I use As Object instead of Excel.*
Now have to wait till after the weekend to get some feedback.

[fafalone]

I know there's some crossover but I think the ability to make true 64bit Office addins and controls is probably twinBASIC's biggest initial market

VBA is very much still widely used, not a tiny niche like VB6.

I've already got code that will compile in both VB6 and twinBASIC, then the resulting OCX's be used in Office or .NET 64bit.

[xiaoyao]

Use "Object", all works fine between vb6 & Excel64.

Example:

Dim xlApp As Object
Dim xlWorkbook As Object
Dim xlWorksheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlWorkbook = xlApp.Workbooks.Open(excelFilePath)
Set xlWorksheet = xlWorkbook.Worksheets("MiSheet")

...

Dim xlApp As Object
Dim xlWorkbook As Object

The main reason is different versions of Excel, which can cause incompatibilities. It's not caused by 32-bit or 64-bit.
So if you can, it's better to load dynamically. DLL, OCX list, registration-free

For example, it is automatically extracted from Excel installed on the client computer. Manifest File
mt.exe excel.exe…… excel.mainfset

[zx81sp]

@zx81sp, I've have the opposite problem, one of my clients is using Excel32.
Then my code just returns an empty xlApp, without raising any errors.

But I made a test app and shared it with the client in which I use As Object instead of Excel.*
Now have to wait till after the weekend to get some feedback.

I use that code with 32&64 bits and different versions (2013, 2016, 365...) with no issues but sometimes a bad Office installation causes the problem. In those cases I've deleted completly Office (no only uninstall), reinstall it (same version) and all start to work fine.

[Arnoutdv]

I got positive feedback from the client, the routines which use Object instead of Excel.* references work fine!
Thanks for all the feedback!

[Schmidt]

I got positive feedback from the client, the routines which use Object instead of Excel.* references work fine!
Thanks for all the feedback!

Yep, for OutOfProcess-COM-Marshalling - especially when performed via IDispatch (As Object) -
the "bitness" of the two communicating Processes is irrelevant.

A different story are "Office-VBA-internal" attempts to access an InProcess-Dll or -OCX (with non-matching bitness)...

Olaf

[Elroy]

Yeah, my primary application uses late binding to automate Word and Excel in very extensive ways, and I haven't had a problem with it in years, even though most of my clients moved on to 64-bit Office many years ago.

Never really tried it with early binding though, as I wanted my software to be independent of any specific version of Office.

[Arnoutdv]

Can I keep the reference to the Microsoft Excel 16.0 Object Library, or not?
If not then I have to replace all used Excel constants from the library with literal values

[Thierry69]

No reference to Excel.
Yes, you need to implement enum etc...

[VanGoghGaming]

I think constants and enums are always safe to be retrieved from the reference. You cannot get an automation error from using a constant...

[fafalone]

From Excel or in general?

const ushort bye;

Unless you've aliased ushort, the above const in a typelib = automation error.

[VanGoghGaming]

LoL yeah, I was talking about constants and enums from established references (like ADODB, etc). Even if you declare objects "As Object" you can still keep the reference to avoid re-declaring all those constants and enums.

[Elroy]

LoL yeah, I was talking about constants and enums from established references (like ADODB, etc). Even if you declare objects "As Object" you can still keep the reference to avoid re-declaring all those constants and enums.

Hmmm, I've never done that. I always just re-declare all the constants. I tend to declare them as I need them.

Example for my Excel automation (also have a similar set for Word):

Public Const msoAutomationSecurityByUI = 2          ' Uses the security setting specified in the Security dialog box.
Public Const msoAutomationSecurityForceDisable = 3  ' Disables all macros in all files opened programmatically, without showing any security alerts.
Public Const msoAutomationSecurityLow = 1           ' Enables all macros. This is the default value when the application is started.
'
Public Const msoConnectorStraight = 1
Public Const msoLineSolid = 1
Public Const msoLineDash = 4
Public Const msoLineSysDash = 10
'
Public Const xlOn = 1&
Public Const xlOff = -4146&
'
Public Const xlExcel8 = 56&
Public Const xlOpenXmlWorkbookMacroEnabled = 52&
Public Const xlOpenXMLTemplateMacroEnabled = 53&
Public Const xlOpenXmlTemplate = 54&
'
Public Const xlPicture = -4147&
Public Const xlPrinter = 2&
'
Public Const xlCalculationManual = -4135&
Public Const xlCalculationAutomatic = -4105&
'
Public Const xlCategory = 1&
Public Const xlValue = 2&
'
Public Const xlSolid = 1&
'
Public Const xlPasteValues = -4163
'
Public Const xlCustom = -4114
'
' Alignment constants.
Public Enum ExcelAlignHorz
    xlLeaveAloneH = 0&
    xlHAlignGeneral = 1&
    xlHAlignLeft = -4131&
    xlRight = -4152&
    xlCenterH = -4108&
End Enum
#If False Then ' Intellisense fix.
    Public xlLeaveAloneH, xlHAlignLeft, xlRight, xlCenterH
#End If
Public Enum ExcelAlignVert
    xlLeaveAloneV = 0&
    xlTop = -4160&
    xlBottom = -4107&
    xlVAlignCenter = -4108&
End Enum
#If False Then ' Intellisense fix.
    Public xlLeaveAloneV, xlTop, xlBottom, xlVAlignCenter
#End If
'
' Line constants.
Public Enum ExcelLineStyle
    xlContinuous = 1&   ' Not dashed.
    xlNone = -4142&     ' No line.
    xlDash = -4115&
    xlDot = -4118&
    xlDouble = -4119&
End Enum
#If False Then ' Intellisense fix.
    Public xlContinuous, xlNone, xlDash, xlDot, xlDouble
#End If
'
Public Enum ExcelLineWeight
    xlHairline = 1&     ' Almost fuzzy line.
    xlThin = 2&         ' The DEFAULT Excel line.
    xlMedium = -4138&   ' Slightly thicker.
    xlThick = 4&        ' VERY thick, seldom used.
End Enum
#If False Then ' Intellisense fix.
    Public xlHairline, xlThin, xlMedium, xlThick
#End If
'
Public Enum ExcelBorders
    xlEdgeLeft = 7&
    xlEdgeTop = 8&
    xlEdgeBottom = 9&
    xlEdgeRight = 10&
    xlInsideVertical = 11&
    xlInsideHorizontal = 12&
End Enum
#If False Then ' Intellisense fix.
    Public xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal
#End If
'
Public Enum ExcelBorderBits ' This is not Excel standard but makes specifying borders easier.
    xlLeftBorder = 1&
    xlTopBorder = 2&
    xlBottomBorder = 4&
    xlRightBorder = 8&
    xlInsideVertBorder = 16&
    xlInsideHorzBorder = 32&
End Enum
#If False Then ' Intellisense fix.
    Public xlLeftBorder, xlTopBorder, xlBottomBorder, xlRightBorder, xlInsideVertBorder, xlInsideHorzBorder
#End If
'
Public Enum ExcelColors ' Used with ColorIndex.
    xlAutomatic = -4105&    ' This is default (black).
    xlBlack = 1&
    xlLightGray = 15&
    xlMidGray = 48&
    xlWhite = 2&
    xlRed = 3&
    xlGreen = 4&
    xlBlue = 5&
    xlCyan = 8&
    xlYellow = 6&
    xlMagenta = 7&
    xlCyanDark = 14&
    xlYellowDark = 12&
    xlMagentaDark = 13&
End Enum
#If False Then ' Intellisense fix.
    Public xlAutomatic, xlBlack, xlLightGray, xlMidGray, xlWhite, xlRed, xlGreen, xlBlue, xlCyan, xlYellow, xlMagenta, xlCyanDark, xlYellowDark, xlMagentaDark
#End If
'
Public Enum WrapText
    xlWrapLeaveAlone = 9&
    xlWrapYes = -1&
    xlWrapNo = 0&
End Enum
#If False Then ' Intellisense fix.
    Public xlWrapLeaveAlone, xlWrapYes, xlWrapNo
#End If
'
Public Enum ExcelFontStyle
    xlStyleLeaveAlone = 0&
    xlRegular = 1&
    xlBoldItalic = 2&
    xlBold = 3&
    xlItalic = 4&
End Enum
#If False Then ' Intellisense fix.
    Public xlStyleLeaveAlone, xlRegular, xlBoldItalic, xlBold, xlItalic
#End If
'
Public Enum ExcelFontName
    xlFontLeaveAlone = 0&
    xlArial = 1&            ' This is the Excel default.
    xlTimes = 2&
    xlMsSanSerif = 3&
    xlSmallFonts = 4&
End Enum
#If False Then ' Intellisense fix.
    Public xlFontLeaveAlone, xlArial, xlTimes, xlMsSanSerif, xlSmallFonts
#End If
'
Public Enum ExcelDirection
    xlDown = -4121&
    xlUp = -4162&
    xlToLeft = -4159&
    xlToRight = -4161&
End Enum
#If False Then ' Intellisense fix.
    Public xlDown, xlUp, xlToLeft, xlToRight
#End If
'
' General IntelliSense fixes for Excel Automation.
#If False Then
    Dim ClearContents, Rows, Columns, Range, UsedRange, Resize, Value, AutomationSecurity
    Dim Workbooks, Workbook, FontStyle, ClearFormats, Borders, Color, ColorIndex, HorizontalAlignment, VerticalAlignment
    Dim LineStyle, DELETE, CheckCompatibility, Application, SaveAs, Save
    Dim Worksheets, ChartObjects, ChartObject, Chart, Sheet, MaximumScale, MinimumScale, PrintObject, ControlFormat
    Dim EntireRow, EntireColumn, Cells, axes, DELETE, ConvertToShape, Transparency, ForeColor, Solid, AddNodes
    Dim Quit, NumberFormat
#End If
'