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" <(E-Mail Removed)> wrote in message
> news:006C5A6F-A949-47E0-B4EA-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:59AA3C8F-4315-4FA0-9835-(E-Mail Removed)...
> > > > 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?
> > > >
> > >
> > >
> > >
>
>
>
|
Similar Threads
