Run Excel Macros in Access

[jo15765]

I coded a project using VBA in Excel. Now they want me to create a form in Access to do the exact same thing. Instead of converting the code to functions etc etc. Can I just call the macros from Excel within Access? The macros are saved in my personal.xls file

[boblarson]

I wouldn't try that. If the macros are in YOUR personal.xls file, what happens when you no longer work there? Also, how would others run the database if the needed stuff is in your file?

Build them into Access. You should be able almost copy and paste or export the modules from Excel and import into Access and then modify them as needed to fit from running within Access.

[jo15765]

That's a great point! I didn't think about others trying to run the macros. I'll just (as you said) export the modules from Excel and Import into Access.

Hopefully it will be a smooth transition.

[boblarson]

There will probably be changes to be made but they are probably going to be minor (most likely).

[jo15765]

Will access recognize commands in my macros such as (just an example code I have)

Set wb = Workbooks.Open

[boblarson]

No it won't. You would need:

Dim objXL As Object
Dim wb As Object
 
Set objXL = CreateObject("Excel.Application")
 
Set wb = objXL.Workbooks.Open("FileNameAndPathHere")
 
' etc.

[boblarson]

Oh and you should read this about how to deal with Excel in Access:

http://www.btabdevelopment.com/ts/excelinstance

[jo15765]

Ay yaya! I am entering uncharted territory here! I will def give it a go.

[jo15765]

I'll post this in a new thread...