Sending an Access module into Excel using VBA (1 Viewer)

[BigMikey]

Hi,
Is there a way to somehow "export" an access VBA module into Excel, and then run it. I have a multiuser database that creates an Excel file in a user's computer and exports data into the spreadsheet. I would then like to change the formating of the spreadsheet (ie add some borders and change cell sizes). It would be best if I could maybe write the code in an access module and then send that module to the spreadsheet and make it one of it's own.. or do something of that nature. This would prevent any issues that access might have with Excel VBA if I was to manipulate the spreadsheet using Access.
Please help,
Mike.

 

[AlanS]

It's easy enough to simply copy code from an Access module and paste into an Excel module, but you'll still need to deal with differences between the Access object model (tables, fields, controls, records, etc.) and the Excel object model (sheets, cells, rows, columns, etc.).

What you may want to at least consider is opening an Excel document object directly from Access VBA. I don't have much experience with that, but I know it's possible.

Good luck.

 

[BigMikey]

Actually I've been controlling the Excel object from Access, but I've been running into trouble with Access not understanding some of the Excel constants in my code. So I was looking to send a macro from my database into an Excel file and then run it. I need to be able to automate the transfer so it's all done automatically and in the background for the users.
But I figured out how to get around the constants thing with another post you helped me with.

It would still be nice to figure out how to do that though..
Thanks,
Mike.

 

[VBAhole22]

One thing I have had luck with doing is using Excel's macro recorder to save the VBA needed to do your customizations and then just pulling that code into your Access module. Granted the macro recorder gives you bloated code but if you know what to cut out you can trim it significantly. One issue you do run into, and you must know this already, your hands get tied when it comes to debugging once you start working with the Excel object. You can put lotsa stops and watches but you also run into a lot of crashes.
I don't know how to transfer VBA code form Access to Excel but couldn't you cut and paste and then compile?

 

[Pat Hartman]

To get Access to recognize Excel constants, open any code module. Go to Tools/References and find the Microsfot Excel object library and select it.