Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop blank workbook from opening when opening an existing | Excel Discussion (Misc queries) | |||
Excel 09 File containing macros is not opening | Excel Discussion (Misc queries) | |||
when opening an Excel Workbook, another blank workbook also opens | Excel Discussion (Misc queries) | |||
when opening an Excel Workbook, another blank workbook also opens | Excel Discussion (Misc queries) | |||
Macros Disabled when opening file | Excel Discussion (Misc queries) |