Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Access Can't Run Excel Macro Through Automation
using access 2007 and excel 2007, can't run a macro in excel from access
here is the error message: "The macro may not be available in this workbook or all macros may be disabled." the workbook that contains the macro is in a location that is trusted by both access and excel here's the code: module level declaration Private ExcApp As New Excel.Application procedure code the following works ExcApp.Visible = True ExcApp.Workbooks.Open FileSpecification (the AutoOpen macro runs ok here) ret = ShowWindow(ExcApp.hwnd, SW_RESTORE) ret = MoveWindow(ExcApp.hwnd, L, T, R, B, True) this doesn't work (see error message above) ExcApp.Run "MacroName" can anybody help? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Access Can't Run Excel Macro Through Automation
Prefix MacroName with filename and an exclamation mark. Depending on the
file name you might also need to embrace it with apostrophes, best to include them to be on the safe side, eg sMacro = "'" & sWBname & "'" & "!" & "MacroName" ExcApp.Run sMacro where sWBname is the workbook name excluding path Regards, Peter T "scott w t" wrote in message ... using access 2007 and excel 2007, can't run a macro in excel from access here is the error message: "The macro may not be available in this workbook or all macros may be disabled." the workbook that contains the macro is in a location that is trusted by both access and excel here's the code: module level declaration Private ExcApp As New Excel.Application procedure code the following works ExcApp.Visible = TrueExcApp.Run "MacroName" ExcApp.Workbooks.Open FileSpecification (the AutoOpen macro runs ok here) ret = ShowWindow(ExcApp.hwnd, SW_RESTORE) ret = MoveWindow(ExcApp.hwnd, L, T, R, B, True) this doesn't work (see error message above) can anybody help? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Access Can't Run Excel Macro Through Automation
Hi Peter. Thanks. I tried your suggestion both with and without specifiying
the path, but no luck. Here's my slightly simplified code with some extra spaces that aren't really there for readability: Dim str_Macro As String str_Macro = " 'FileName.xlsm' " & "!" & " 'MacroName' " ExcelApplication.Run str_Macro Did I execute your suggestion as intended? Thanks. "Peter T" wrote: Prefix MacroName with filename and an exclamation mark. Depending on the file name you might also need to embrace it with apostrophes, best to include them to be on the safe side, eg sMacro = "'" & sWBname & "'" & "!" & "MacroName" ExcApp.Run sMacro where sWBname is the workbook name excluding path Regards, Peter T "scott w t" wrote in message ... using access 2007 and excel 2007, can't run a macro in excel from access here is the error message: "The macro may not be available in this workbook or all macros may be disabled." the workbook that contains the macro is in a location that is trusted by both access and excel here's the code: module level declaration Private ExcApp As New Excel.Application procedure code the following works ExcApp.Visible = TrueExcApp.Run "MacroName" ExcApp.Workbooks.Open FileSpecification (the AutoOpen macro runs ok here) ret = ShowWindow(ExcApp.hwnd, SW_RESTORE) ret = MoveWindow(ExcApp.hwnd, L, T, R, B, True) this doesn't work (see error message above) can anybody help? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Access Can't Run Excel Macro Through Automation
MacroName should NOT be embraced with the apostrophes, only the filename and
then only then necessary depending on what characters it contains, spaces & certain punctuation (no harm to include the apostrophes around the filename if not required). Looks like you are using XL2007 which I don't have so perhaps there's something else you need to do. Save a workbook named (say) Book1.xls with a macro named say Test Sub Test() Msgbox "hello" End sub Save the workbook, close and reopen it In another workbook Sub RunTest dim sMacro as string sMacro = "Book1.xls!Test" Application.Run sMacro End Sub Normally that should work, and with a filename like that without the apostrophes, at least it should in any pre-2007 version of XL Assuming it does, close Book1.xls. Within Excel, automate a new instance of XL, make it visible, load Book1.xls (with code) and xlApp.Run the macro in the other instance. If necessary, first bring the other instance to the foreground so you can see the msgbox. Regards, Peter T "scott w t" wrote in message ... Hi Peter. Thanks. I tried your suggestion both with and without specifiying the path, but no luck. Here's my slightly simplified code with some extra spaces that aren't really there for readability: Dim str_Macro As String str_Macro = " 'FileName.xlsm' " & "!" & " 'MacroName' " ExcelApplication.Run str_Macro Did I execute your suggestion as intended? Thanks. "Peter T" wrote: Prefix MacroName with filename and an exclamation mark. Depending on the file name you might also need to embrace it with apostrophes, best to include them to be on the safe side, eg sMacro = "'" & sWBname & "'" & "!" & "MacroName" ExcApp.Run sMacro where sWBname is the workbook name excluding path Regards, Peter T "scott w t" wrote in message ... using access 2007 and excel 2007, can't run a macro in excel from access here is the error message: "The macro may not be available in this workbook or all macros may be disabled." the workbook that contains the macro is in a location that is trusted by both access and excel here's the code: module level declaration Private ExcApp As New Excel.Application procedure code the following works ExcApp.Visible = TrueExcApp.Run "MacroName" ExcApp.Workbooks.Open FileSpecification (the AutoOpen macro runs ok here) ret = ShowWindow(ExcApp.hwnd, SW_RESTORE) ret = MoveWindow(ExcApp.hwnd, L, T, R, B, True) this doesn't work (see error message above) can anybody help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Access Can't Run Excel Macro Through Automation
Thanks again, Peter. I tried what you recommended but it didn't work.
However, it inspired me to go through the Excel macro and comment everything out and then add it back in line by line and access it from Access through automation to see where it got hung up. And I found it. I had a macro and a range with the same name. Happy new year, Scott "Peter T" wrote: MacroName should NOT be embraced with the apostrophes, only the filename and then only then necessary depending on what characters it contains, spaces & certain punctuation (no harm to include the apostrophes around the filename if not required). Looks like you are using XL2007 which I don't have so perhaps there's something else you need to do. Save a workbook named (say) Book1.xls with a macro named say Test Sub Test() Msgbox "hello" End sub Save the workbook, close and reopen it In another workbook Sub RunTest dim sMacro as string sMacro = "Book1.xls!Test" Application.Run sMacro End Sub Normally that should work, and with a filename like that without the apostrophes, at least it should in any pre-2007 version of XL Assuming it does, close Book1.xls. Within Excel, automate a new instance of XL, make it visible, load Book1.xls (with code) and xlApp.Run the macro in the other instance. If necessary, first bring the other instance to the foreground so you can see the msgbox. Regards, Peter T "scott w t" wrote in message ... Hi Peter. Thanks. I tried your suggestion both with and without specifiying the path, but no luck. Here's my slightly simplified code with some extra spaces that aren't really there for readability: Dim str_Macro As String str_Macro = " 'FileName.xlsm' " & "!" & " 'MacroName' " ExcelApplication.Run str_Macro Did I execute your suggestion as intended? Thanks. "Peter T" wrote: Prefix MacroName with filename and an exclamation mark. Depending on the file name you might also need to embrace it with apostrophes, best to include them to be on the safe side, eg sMacro = "'" & sWBname & "'" & "!" & "MacroName" ExcApp.Run sMacro where sWBname is the workbook name excluding path Regards, Peter T "scott w t" wrote in message ... using access 2007 and excel 2007, can't run a macro in excel from access here is the error message: "The macro may not be available in this workbook or all macros may be disabled." the workbook that contains the macro is in a location that is trusted by both access and excel here's the code: module level declaration Private ExcApp As New Excel.Application procedure code the following works ExcApp.Visible = TrueExcApp.Run "MacroName" ExcApp.Workbooks.Open FileSpecification (the AutoOpen macro runs ok here) ret = ShowWindow(ExcApp.hwnd, SW_RESTORE) ret = MoveWindow(ExcApp.hwnd, L, T, R, B, True) this doesn't work (see error message above) can anybody help? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
In Access Can't Run Excel Macro Through Automation
And I found it. I had a macro and a
range with the same name. Ah, that old chestnut. I should have thought of that! Regards, Peter T "scott w t" wrote in message ... Thanks again, Peter. I tried what you recommended but it didn't work. However, it inspired me to go through the Excel macro and comment everything out and then add it back in line by line and access it from Access through automation to see where it got hung up. And I found it. I had a macro and a range with the same name. Happy new year, Scott <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automation to Excel from Access | Excel Discussion (Misc queries) | |||
Excel/Access automation | Excel Programming | |||
Access automation from Excel | Excel Programming | |||
access 97 to excel 97: execute macro automation | Excel Programming | |||
access 97 to excel 97: execute macro automation | Excel Programming |