autorun macro on excel file

[Daniel M]

I have a macro i have created in excel and I have an access db that i want to
call an excel file run this macro against it.

Now i do not want to run this macro from my personal.xls or have to drop
this in the xlstart folder. what i would like is to just create a macro.xls
file with the macros in and when i call excel from access open the
"logfile.xls" and run a macro on it from the "macro.xls" file. Alternately if
i can copy the macro from access to excel when opening "logfile.xls" that
would work to.

The logfile.xls comes from another person without any macros in it. right
now i format the file with my macro and resave it. but i want to do this from
access.
further i want to copy this to the server so that anyone can do this by
hitting the command button, so it cannot reside on my local machine.

Any ideas?

I have the following code in access but it doesnt even seem to open the file
with my personal.xls.


Private Sub Command3_Click()
Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
'Open the Workbook
.Workbooks.Open "C:\testlog.XLS"
.ActiveWorkbook.RunAutoMacros xlAutoOpen
x = .Run("log2sms", 0)
End With
Set objXL = Nothing

End Sub

 

[Roger Converse]

Were you able to get this to work? I am trying to get my DB to do pretty
much the same thing.

When I take what you have, I get compile errors though.

Thank you,
Roger

 

[Daniel M]

Yes i was able to get it working, i did do things a bit differently though. i
didnt want the macro embedded in the excel file i was using or have it locked
to a specific pc.

I created a xls file and put all my macros into it. I then saved it as an
add-in (file save as...) From there i had access open the excel file, open
the add-in, then call the macro. All of this was done hidden in the
background and works great.

I couldnt get access to load the excel add-in when it launched the file so i
had to specifically call the add-in. because of this, you could probably just
save it as an excel file and call it.

if you need some code examples of what i did, let me know and i'll paste
some in for you.

 

[Roger Converse]

If you could post some code, that would be terrific. Taking what you had
below, I created:

strExpOpenPOFilePath = Me.txtExpOpenPOFP.Value & " " & Format(Date,
"mmddyyyy") & ".xls"
strFilename = "Open PO Rerport for " & Format(Date, "mmddyyyy") & ".xls"

Set objXl = CreateObject("Excel.Application")
With objXl.Application
..Visible = True
..Workbooks.Open "C:\Documents and Settings\RConverse\Application
Data\Microsoft\Excel\XLstart\personal.xls"
..Workbooks.Open strExpOpenPOFilePath
..RunAutoMacros = Run("macOPenPO", 1)
End With

Set objXl = Nothing

It opens the spreadsheets just fine, but it doesn't locate the macro and I
get an error. I know the macro is in my personal.xls spreadsheet as are the
rest.

Thanks,
Roger

 

[Daniel M]

ah! i have the code on my other machine so i will have to wait until tonight.
however i may still be able to help...

change .RunAutoMacros = Run("macOPenPO", 1)
to .Run "macOPenPO"

I also had a problem with it finding my macro. I had a module1 i used for
test but then i had a module called macros where all my real macros were. it
found all the macros in module1 but not in macros. i had to do this...

..Run "macros.macOPenPO"

This found it and it worked fine. let me know if this still doesnt help.

 

[Roger Converse]

This worked very well. Thank you a bunch!

Thank you,
Roger