Help needed to programmatically copy a module to a another workbook

[AliJWood]

Hi to all,

I wonder if you can help. I have a Macro (see below) which works effectively in copying an active sheet of a temporary file into a new work book (and enables me to format and do other things within it) before opening the new workbook. This VBA is contained in a "template" (module1) that is attached to a report extracting from a database.

I have added some control buttons to my template and assigned macros to these buttons (the macros are saved in Module 2 of the template)

I added the code "ActiveWorkbook.VBProject.VBComponents("module2").Copy" into the macro (shown in italics below) - However, ever since I have made this change, I am getting a Runtime 9 error and the active sheet is no longer copied into the output workbook (but BOTH module 1 and module 2 are!)

Just wondering if anyone can give me an idea of what I am doing wrong?

Sub Auto_Open()
On Error GoTo ErrorHandler


Application.Visible = False

ThePath = ThisWorkbook.Path
Workbooks.Open Filename:=ThePath + "\ReportData.txt"

' Copy the workbook, and close the source file (having marked it as saved)
Set Wbook = ActiveWorkbook
ActiveSheet.Copy
ActiveWorkbook.VBProject.VBComponents("module2").Copy this is the code I have added!
Wbook.Saved = True
Wbook.Close

'Set ReportSheet = ActiveSheet
' Mark the active workbook as saved
ActiveWorkbook.Saved = True

'Moves the sheets into a different order.
Workbooks("ExcelList.xls").Sheets(2).Move Before:=ActiveWorkbook.Sheets(1)
Workbooks("ExcelList.xls").Sheets(2).Move Before:=ActiveWorkbook.Sheets(1)
Workbooks("ExcelList.xls").Sheets(2).Move Before:=ActiveWorkbook.Sheets(1)
'Selects and copies all the data extracted from SIMS and pastes it onto the student data sheet
Sheets("ReportData").Select
Range(Sheets("ReportData").Range("A4"), Sheets("ReportData").Range("A1").SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("student Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues
'Makes all columns on student data sheet wide enough for any data used
Sheets("student Data").UsedRange.EntireRow.AutoFit
Sheets("student Data").UsedRange.EntireColumn.AutoFit
Range("A1").Select

Sheets("student data").Select
Dim r As Long
r = ActiveSheet.Range("A1").End(xlDown).Row
Range("l2:s2").Select
Selection.AutoFill Destination:=Range("l2:s" & r)


Sheets("Workings").Visible = False

Sheets("Analysis").Select
Range("A1").Select


Application.DisplayAlerts = False
Worksheets("ReportData").Delete
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.Visible = True

Workbooks("ExcelList.xls").Saved = True
Workbooks("ExcelList.xls").Close

Exit Sub

' Error-handling routine
ErrorHandler:
Application.Visible = True
MsgBox "Error " & Err.Number & " : " & Err.Description

End Sub

[CK76]

You can't directly copy Vbcomponents if I recall.

You should first export and then import. Have a read of Ron de Bruin's article on the subject.
https://www.rondebruin.nl/win/s9/win002.htm

[AliJWood]

Hi CK76

Thank you for your swift reply
I will have a look at the article. Bit mystified though, as the modules have copied into the output, but the subsequent line is not happening!

[Zer0Cool]

You can do it using import/export methods:

Something like:

Please Login or Register  to view this content.

You need to have enabled access via trust center to the VB object model for this to work, which is a big security risk (as it lets code make code). You may also need to enable the reference in the references to VBE or use late binding.

However its unclear what your goal is. Why are you trying to copy the module? Where is it currently and where does it need to be?

Usually when someone starts duplicating code there is a better way to handle it (template, personal workbook, addin/xlam, etc)

[AliJWood]

Hi Zer0Cool

Thank you for your reply. I am using a template, which works absolutely fine normally, however, I have added some Buttons into the template and assigned macros to them (they are just basic buttons that will filter information on click) - when I run the template in the normal way, the macros that have been assigned to the buttons in the template are not available in the workbook. That is why I was attempting to copy the code as well as the data into the output. Any ideas?

Ali

[Zer0Cool]

I am still not entirely clear on what you are doing overall. Try and explain your workflow to us, in terms of what you want to accomplish not in terms of what you have that doesnt work.

IE (hypothetical, not saying this is what you are doing):

• I have a template file (xltm) that I use to process files in a folder
• I need to copy x, y, z from the files in the folder which are temp files to a new file/the template file/etc
• I also need code in a module located in the temp files to be copied to a module in the output file because of x reason.

Something like the above lets us know what your goal is, so we may provide the best solution for what you want to do, not what you are doing.

Also, using precise terminology will help as well. You keep using the word "template". To me, and technically in Excel, a template is a special kind of file with its own extension (xltx, xltm, etc) that when "opened" creates a new file based off of itself each time you "open" it. In this manner the file that opens is a copy that has no impact on the original.

If instead you mean a file that is central to your workflow then it would be more clear to term it as something like "Master File". Explaining it as the file you use to process other files and/or store code to act upon other files.

Sample files would be immensely helpful. Code and a brief explanation are not as helpful to us as one may think. We cant see your data, structure, follow the code against your layout on sheet. We can also only go by your description of what you want to accomplish without a sample.

Presenting us with a "this doesnt work, how do I fix it" is kind of like handing a device to someone that doesnt work and asking them to fix it, without telling them what the device is meant to do.

[AliJWood]

[FONT=Arial]Hi Zer0Cool,

Thank you very much for your reply. My apologies for not being clear. I have spent most of today trying to figure this out and am still stumped.. I am attaching the file I am using so that you can see my problem.

Firstly. I am unable to use an Excel Template file (.xlt or .xltm) because I am restricted to the type of file I can use in the MIS system that I am extracting data from. That is my first problem. This is the history:


I extract information from an MIS system via an inbuilt reporting engine. This information is exported to a .txt file on the system, and I link the "template.xls" that I use, to the report definition so that, at run time, the excel "template (.xls)" is used to (a) open a new workbook (b) copy the data from the text file into another template (kind of intermediate) called "ExcelList.xls" and this ultimately ends up in a Workbook (Book1), it also applies formatting and vba that i have pre-populated in the template. I can (successfully) use an excel file (.xls) and use as a "template" (even though I know it is not a template in the true sense of the word!. Using this .xls file, I can insert VBA to perform most functions, as well as adding formulae within the "template" to perform calculations this is achieved with an "auto-open" macros that it in the original "template.xls" file. This I can achieve without problem.


However, for my most recent project, within the "template", I want to include Buttons, with a macro assigned to each of them. These are simple form control buttons and the macro is merely a filter of the data that has been extracted. The problem I have is that when the final output opens, the VBA is not copied at all, just the data. The "autopen" macro along with any other VBA is not present in "Book 1" which is the output file. This, in turn, means that the buttons (which are present in the output) do not have any macros assigned to them as they are not available in the workbook.


I have attempted to:
(a) programmatically insert a module - I get this to work and it appears in "Book1" - the ultimate output
(b) Programmatically insert a sub into the module - this did not work (it stayed in "Excellist.xls")

I also tried to use the Import/Export Code but this only gets as far as "Excellist" (the intermediate file)


I realise that the type of file I used initially (.xls) is not effective, so have changed the VBA etc, to use .xlm. Unfortunately, I cannot use .xlt or .xltm as the MIS will not see them! The most efficient file type I can use is .xlsm (attached).

I am attaching the "normal" .xls file that I would use (basic.xls) as a basis for my "templates" and also including my latest attempt after applying the above (ExcelAddressLinkCheckV2.xlsm).

Any help with this would be very much appreciated!

Ali