ActiveX problem - Excel 97 and 2000

[supanoods]

Hi,

The code below transfers 2 queries to a .csv file, and opens a .xls file that then manipulates the .csv data.

It is an Access97 DB, and it works perfect on my WinXP/Office2000 PC - but not at all on any PC's that have Office97 installed.

I get an ActiveX error, or an 'automation' error. I think that is something to do with the .ole reference files (obviously 97 uses Excel8.ole and 2000 uses excel9.ole).

xlApp appears as 'nothing' when the error is debugged.

Is there anyway around this error! Its driving me nuts!!!!


Code:

' Declare the Objects etc
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Dim stDocName1 As String
Dim stDocName2 As String
Dim xlQRYOutputName As String
Dim xlFileName As String

' Define Access Query Names
stDocName1 = "MBPTOT" ' Query that holds the information regarding the report
stDocName2 = "MBPINFO" ' Query that hold information reagrding the Graph axis etc

' Define Query Data Output file (.csv) and Excel Report file/program
xlQRYOutputName = "c:\eiddata\parqry.csv"
xlFileName = "c:\eiddata\eidpar.xls"

' Transfer the Access Query data to the Query Output Excel file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, stDocName1, xlQRYOutputName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, stDocName2, xlQRYOutputName

' Open Excel Report Program
Set xlApp = GetObject(xlFileName)
xlApp.Application.Visible = True
xlApp.Parent.Windows(1).Visible = True
xlApp.Application.ScreenUpdating = True

' Clear the Objects
Set xlApp = Nothing
Set xlWB = Nothing
Set xlWS = Nothing

Thanks in advance

"If it aint broke - dont fix it!

 

[PHV]

You may try this:
' Open Excel Report Program
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(xlFileName)
xlWB.Windows(1).Visible = True
xlApp.ScreenUpdating = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[supanoods]

PHV,

Thanks for the help - but unfortunately it has now posed me with another problem.

The Excel sheet is opening (and on Office 97 PC's too) which is great, and exactly what I wanted. However, the Excel sheet I am opening should be running an 'Autpen' macro (to update the graph it generates) and now it only opens the file - no macro!!! It works fine if you manually run the macro.

So I guess my next question is - How can I tell Access to run my Excel Macro???

Thanks again

"If it aint broke - dont fix it!

 

[supanoods]

PHV,

Sorted!!!!

Read this thread - thread707-1063185

And it pointed me in the right direction, i just ran the macro from Access - problem solved

Thanks for you help!



"If it aint broke - dont fix it!