Opening a workbook containing macros from VB

[Brian Walters]

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

 

[Bob Phillips]

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

 

[Bob Phillips]

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.