VBA generated excel file does not save Modules

[vgarzon]

Hi all,

I have an issue with an Excel file that I'm generating through a VBA code in Access.

I generate a report of some data that is in Access 2007, on an Excel file and add a "processing view" button to do some operations.

To add that button, I add a module to the Excel file in this way:

'Add the view button
 appExcel.ActiveSheet.Buttons.Add(520, 110, 130, 25).Select
 appExcel.ActiveSheet.Shapes("Button 1").Select
 appExcel.Selection.Characters.Text = "Processing View"
 appExcel.VBE.ActiveVBProject.VBComponents.Import "C:\BLA\Change_Views_Script.bas"
 appExcel.ActiveSheet.Shapes("Button 1").Select
 appExcel.Selection.OnAction = "Change_Views"

And when the file is generated, everything works fine.

The file is also saved in this way:

appExcel.ActiveWorkbook.SaveAs FileName:="C:\BLA\abc": FileExtStr = ".xlsb": FileFormatNum = 50

The problem is when I close that generated file, and then I open it again. When clicking the button I get the message:

Cannot run the macro 'Change_Views'. The macro may not be available in this workbook or all macros may be disabled

And effectively, I check the code and the imported module is not there.

Can Somebody help me, telling me how to save the imported module as well? I've tried to change the FileFormatNum and the FileExtStr, but I keep having the same results.

Thanks in advance, I hope somebody is able to help me.

Victor

 

[Alisa]

Check your security settings. In 2007, all code is automatically disabled until you change the security settings to allow it.

 

[vgarzon]

Hey Alisa,

The security settings are set to allow this. I have the "Enable all macros" option chosen, and the "Trust Access to the VBA project object model" option checked, both on Excel 2007.

Should I set other options in Access or Excel? Remember that everything works fine when the file is generated (the button does what it should do), but when closing and opening again, I can't see the added module.

I think that the problem is on the saving part. It should be saved as .xlsm or a similar extension but I can't do it.

Thanks

 

[Alisa]

What happens if you put a breakpoint in your code before the call to save the workbook, and manually save it at that point?

 

[NigelShaw]

Hi

I'm not very familiar with this but as a general observation, is there a separate save option for the vbaproject?

You are able to save the objects as they are on the actual spreadsheet but the module is part of the vba interface so effectively a different program ( I think )

Nidge

 

[chergh]

Try replacing:

appExcel.VBE.ActiveVBProject.VBComponents.Import "C:\BLA\Change_Views_Script.bas"

with

appexcel.activeworkbook.VBProject.VBComponents.Import "C:\BLA\Change_Views_Script.bas"