Opening a workbook containing macros from VB
 

I am trying to open an Excel workbook that contains macros
from Access97 VB. I use the coding:

Set MyExcel = CreateObject("Excel.Application")
With MyExcel
.Workbooks.Open pathname & "Book1.xls"

to open the file, but I keep getting an error that the
method is invalid. Does anyone know how to open an Excel
file that contains macros using VB and be able to enable
the macros and use them? Thanks!!

Opening a workbook containing macros from VB
 

Brian ,

I ran your code, albeit in Excel creating a new instance of Excel, and as
long as I defined a value for pathname it worked fine. That should be the
same as for Access VBA I think you might need to post more code to see the
problem.

This is my code, with a line to run a macro in that workbook.

Dim my As Object
Dim pathname As String

pathname = "C:\myTest\"
Set myExcel = CreateObject("Excel.Application")
With myExcel
.Workbooks.Open pathname & "myFile.xls"
End With
myExcel.Visible = True
myExcel.Run ("test")


--

HTH

Bob Phillips

"Brian Walters" wrote in message
...
I am trying to open an Excel workbook that contains macros
from Access97 VB. I use the coding:

Set MyExcel = CreateObject("Excel.Application")
With MyExcel
.Workbooks.Open pathname & "Book1.xls"

to open the file, but I keep getting an error that the
method is invalid. Does anyone know how to open an Excel
file that contains macros using VB and be able to enable
the macros and use them? Thanks!!

Opening a workbook containing macros from VB
 

Brian,

I guess TransferSpreadsheet is an Access function, and I am not an Access
user I am afraid.

If no-one sorts it in the next couple of days, you could send me the Access
file and I will do my best to look at it.

--

HTH

Bob Phillips

"Brian Walters" wrote in message
...
Thanks for replying Bob!! I don't think it is the opening
of the file that is the problem anymore. I do a
TransferSpreadsheet command to the excel file just before
I open it. It seems as if the TransferSpreadsheet is not
letting go of the excel file and then I try to open it and
I get an automation error.

Here's the code.

Private Sub cmdShow_Click()
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel97, "OneShowCalcParticipant", wcDir
& "Book1.xls", True
Set MyExcel = CreateObject("Excel.Application")
With MyExcel
.Workbooks.Open FileName:=wcDir & "Book1.xls"
End With
MyExcel.Visible = True
End Sub


-----Original Message-----
Brian ,

I ran your code, albeit in Excel creating a new instance
of Excel, and as
long as I defined a value for pathname it worked fine.
That should be the
same as for Access VBA I think you might need to post
more code to see the
problem.

This is my code, with a line to run a macro in that
workbook.

Dim my As Object
Dim pathname As String

pathname = "C:\myTest\"
Set myExcel = CreateObject("Excel.Application")
With myExcel
.Workbooks.Open pathname & "myFile.xls"
End With
myExcel.Visible = True
myExcel.Run ("test")


--

HTH

Bob Phillips

"Brian Walters" wrote in
message
...
I am trying to open an Excel workbook that contains
macros
from Access97 VB. I use the coding:

Set MyExcel = CreateObject("Excel.Application")
With MyExcel
.Workbooks.Open pathname & "Book1.xls"

to open the file, but I keep getting an error that the
method is invalid. Does anyone know how to open an
Excel
file that contains macros using VB and be able to enable
the macros and use them? Thanks!!


.