Can VBA in MSAccess create code/macros in an Excel Workbook

[ian_add]

I am creating a workbook using VBA in MS Access, populating the workbook with data from Access.

I have created a bunch of columns which I am setting hidden as default for the user to unhide if required. All works fine.

I want to make it easier for the user by creating a buton on the worksheet which unhides and rehides the columns rather than depending on them using the Excel skills they may lack.

I have the button and code behind it working as I want on a test workbook.

How can I get VBA code inside Access to create this as it creates the workbook. I can see how to create the button itself, it is getting the code behind it into the workbook that has me puzzled.

 

[Micron]

I would bet your money you cannot create code in Excel by using Access code. You can probably run a 'macro' in a workbook from Access but I cannot imagine that you could write one. Perhaps I have not interpreted your situation correctly, because I cannot imagine who does not possess the 'skill' to click a button that you already have on your workbook.

 

[ian_add]

I would bet your money you cannot create code in Excel by using Access code. You can probably run a 'macro' in a workbook from Access but I cannot imagine that you could write one. Perhaps I have not interpreted your situation correctly, because I cannot imagine who does not possess the 'skill' to click a button that you already have on your workbook.

To be clear, I Create a new workbook three times a week, the format of which changes fairly substantially each time. I need to automate the creation of the button and its macro code if at all possible.

 

[Legacy 456155]

You can use the Microsoft Visual Basic for Applications Extensibility library to generate code. However, you must have the ability to set permissions to allow its use. See Trust Center -> Macro Settings -> Developer Macro Settings -> Trust Access to the VBA project object model.

That aside, you should be able to create a template of sorts that already has the code within and fill in the blanks with your exported data. I don't know what "format of which changes fairly substantially" means. Perhaps your code could be adaptive enough to account for these variances? Please expound a bit.

 

[Micron]

Then perhaps you ought to be doing TransferSpreadsheet function in Access (apparently it IS possible to use the range argument of an existing sheet contrary to popular belief). Or you could use vba to open a template file, transfer data and then do a Save As on the template. That should take care of the coding need but you'd still have to worry about Trusted Locations.

You can use the Microsoft Visual Basic for Applications Extensibility library to generate code

If you can use Access to write code and have Access save that code in a new Excel workbook I'd like to learn that technique. I'll have to google that.

 

[xenou]

I would also suggest you have a template workbook with the code in it, and use that as your "new workbook" when you populate the workbook with data from access.