• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Sheet to Existing List of Excel Files and Add Module

Fadil

Member
Hi,

I have a rather complicated task (at least for me it is complicated) and I am reaching out for your help, please.

I have a list of folders and in each folder I have number of excel files which are all of the same template. These files are my Cost Reports.
I need to add a new pre-formatted sheet into each of the existing files (a loop code).
Also, into this new sheet I want to add, for this sheet to get me the data I needed, I was suppose to add a Module to this workbook.

So, what I am in desperate need of is a code that will copy this pre-formatted sheet and paste it on the list of files I have, each file one by one (loop code) and also a code that will also add this new Module I created into this workbook so the new sheet will work properfly.
It is a function I had to create to convert numbers into text.

Your help is greatly appreciated

Regards,
Fadil
 
Hi Fadil,

While creating a looping code to copy a worksheet is fairly straightforward, editing the VB on each workbook is much more challenging. The reason being, is that you hit a security check.

A malicious macro could be written to duplicate itself by copying it's code to other workbooks. To prevent hackers from abusing this then, VB macros are prevented from editing/creating code by default. That said, there are ways to remove the security checks so that the code can do what you want, but it can be tricky and is certainly on the more advanced side of things.

Perhaps we can avoid having to import the macro? You mention that the new module contains a function...like a UDF? If you state the functions purpose, perhaps we can come up with a non-VB solution?
 
Hi Luke,

Thank you for your immediate response. The function purpose I mentioned is only needed to convert the text into numbers. The new sheet I am trying to add to my current books is an invoice type. So, once I add this sheet, I will link it with other sheets where it pulls data to auto-populate the missing fields and at the bottom I want the value to be spelled out with words. I found a VBA code that does this (i.e. =spellnumber function) and that's the only reason why I want to add this function to all books just so it will spell out the numbers in words.

Regards,
Fadil
 
Hi,

I will try to modify this and apply it to meet my needs. I'm just afraid I may go somewhere wrong with the VBA module copying it into the workbooks.

About copying the sheet into the workbooks, is it possible you can help me build something like the attached where I have a source of the document from where to copy to and destination folder where to copy on to, and also I need to be able to tell the code where to place the sheet into, before or after which sheet within that workbook. All workbooks are same and have the same structure.

Regards,
Fadil
 

Attachments

  • COPY DATA - TEST.xlsm
    18.1 KB · Views: 2
Sure, we can try. Here's my attempt at integrating the module import with your existing code.
Code:
Sub CopyItems()
Dim wbOld    As Workbook
Dim wbNew    As Workbook
Dim wbThis  As Workbook
Dim i        As Long
Dim modPath As String

'Where will the module temporarily be stored as?
modPath = "c:\MrXL1.bas"

' Delete any old stray copies of the module1
 On Error Resume Next
 Kill (modPath)
 On Error GoTo 0
 ' Export Module 1
 ActiveWorkbook.VBProject.VBComponents("module1").Export (modPath)

'set last row
lRow = Cells(Rows.Count, "B").End(xlUp).Row

'get the active sheetname of the book
Set wbThis = ActiveWorkbook

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False
Application.ScreenUpdating = False
For i = 2 To lRow
   Set wbOld = Workbooks.Open(Cells(i, 2).Value)
   wbThis.Activate
   Set wbNew = Workbooks.Open(Cells(i, 3).Value)

   With wbNew.Worksheets("Summ")
       .Range("D15:D19").Value = wbOld.Worksheets("Summ").Range("D15:D19").Value
   
       .Range("J10:J13").Value = wbOld.Worksheets("Summ").Range("J10:J13").Value
       .Range("J16:J20").Value = wbOld.Worksheets("Summ").Range("J16:J20").Value
       .Range("L21").Value = wbOld.Worksheets("Summ").Range("L21").Value
   
       .Range("D50:D53").Value = wbOld.Worksheets("Summ").Range("D50:D53").Value
       .Range("D56").Value = wbOld.Worksheets("Summ").Range("D56").Value
       .Range("F56").Value = wbOld.Worksheets("Summ").Range("F56").Value
           
       .Range("I60").Value = wbOld.Worksheets("Summ").Range("I60").Value
       .Range("J67").Value = wbOld.Worksheets("Summ").Range("J67").Value
       .Range("L67").Value = wbOld.Worksheets("Summ").Range("L67").Value
  End With

   'activate the source book
   wbNew.Activate
   'Import module into wbNew
   Application.VBE.ActiveVBProject.VBComponents.Import (modPath)
           
   wbNew.Save
   wbNew.Close
   wbOld.Close False
       
   'clear memory
   Set wbOld = Nothing
   Set wbNew = Nothing
   
    Cells(i, 1).Value = "Done!"
   
Next i
Application.ScreenUpdating = True
'Remove temp file
Kill (modPath)
End Sub
 
Hi Luke,


I really thank you for your help on this matter as I see you put lot of work on modifying the code above. As you can see I am beginner on this and I'm trying to understand the code how it works and what it does, and with F8 function I get to see what it does at points and make changes.


The excel sheet I posted on my last note is just an example a friend helped me put together to change things from one workbook to another (so I use this sample every time I need to make changes on other cells with minor changes of the code). However, I wanted to use that concept to add my new sheet into the existing workbooks where I can just specify the source and destination in columns B and C and have the code do the following:

For example in the B column named Old Project Source, I will specify where the sheet is located that I am trying to bring in: Sheet name "Invoice" in a particular workbook which will be specified in column B.

In C column I will add path where the "Invoice" sheet needs to be copied into the new destination.

The current code in my sample is not required now since I already did that exercise, but am not being able to add the Sheet "Invoice". If you can help me do this, to add the code here that will do the above I explained, I will really appreciate it.

Also, about the Module I am trying to bring, the destination workbooks already have Module 1 name and that Module does something else, so I guess in this case I just change the code to a module name that I don't use and that's the module will be updated with the new function I want to bring, correct?

Regards,

Fadil
 

Attachments

  • COPY DATA - TEST.xlsm
    18.1 KB · Views: 2
Hi Fadil,

It would help if you had some examples in Sheet one so I know what the macro will see.

The module that you are importing...is it coming from a single source, or are you needing to export/import different modules?
Correct, you would need to change the CodeName (not the file Save As name) before exporting, so that when you import you don't have a name conflict.

E.g. on how to change code name
upload_2015-3-3_9-29-14.png
 
Hi Luke,

I am typing from iPad now and am not on a PC until tomorrow to be able to send a sample. Basically the module that I want to import will come from a single source. Also the sheet named "Invoice" will come from single source so I just want the whole tab of the "Invoice" sheet to be copied into the destination workbooks as a new tab. It is pre-formated invoice form I created that I need to add to all other workbooks in the destination folders.

Regards,
Fadil
 
How about this then? Assumes that active workbook is the one with worksheet and module you want to export. Then it goes from there.
Code:
Sub CopyItems()
'ASSUMPTION
'The active workbook is the one with module/sheet to export
Dim wbNew    As Workbook
Dim wsOld As Worksheet
Dim i        As Long
Dim lRow As Long
Dim modPath As String

'Where will the module temporarily be stored as?
modPath = "c:\MrXL1.bas"

' Delete any old stray copies of the module1
On Error Resume Next
Kill (modPath)
On Error GoTo 0
' Export Module 1
ActiveWorkbook.VBProject.VBComponents("module1").Export (modPath)

Set wsOld = ActiveWorkbook.Worksheets("Invoice")
lRow = Cells(Rows.Count, "C").End(xlUp).Row

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False
Application.ScreenUpdating = False
For i = 2 To lRow
    Set wbNew = Workbooks.Open(Cells(i, "C").Value)
    'Copy the module
    wbNew.VBProject.VBComponents.Import (modPath)
    'Copy the worksheet
    wsOld.Copy after:=wbNew.Worksheets(wbNew.Worksheets.Count)
   
    'Close and save
    wbNew.Close True
    Cells(i, "A").Value = "Done!"
Next i
Set wbNew = Nothing
Set wsOld = Nothing
   
Application.ScreenUpdating = True
'Remove temp file
Kill (modPath)
End Sub
 
Hi Luke,

Thank you for your time. I am trying to add the module but I am getting stuck at a point where I highlighted it in Bold Letters:
I am attaching the file where I am adding your code and I took five sample files to try this to

Sub CopyItems()
Dim wbNew As Workbook
Dim wbOld As Worksheet
Dim i As Long
Dim lRow As Long
Dim modPath As String

'Where will the module temporarily be stored as?
modPath = "C:\Users\credjef\Documents\Regional Reporting\Invoice Sample\SpellNumber.bas"

' Delete any old stray copies of the module1
On Error Resume Next
Kill (modPath)
On Error GoTo 0
' Export Module 1
ActiveWorkbook.VBProject.VBComponents("SpellNumber").Export (modPath)

Set wsOld = ActiveWorkbook.Worksheets("Invoice")
lRow = Cells(Rows.Count, "C").End(xlUp).Row

'clear any thing on clipboard to maximize available memory
Application.CutCopyMode = False
Application.ScreenUpdating = False
For i = 2 To lRow
Set wbNew = Workbooks.Open(Cells(i, "C").Value)
'Copy the module
wbNew.VBProject.VBComponents.Import (modPath)
'Copy the worksheet
wsOld.Copy after:=wbNew.Worksheets(wbNew.Worksheets.Count)

'Close and save
wbNew.Close True
Cells(i, "A").Value = "Done!"
Next i
Set wbNew = Nothing
Set wsOld = Nothing

Application.ScreenUpdating = True
'Remove temp file
Kill (modPath)
End Sub
 

Attachments

  • COPY DATA - TEST.xlsm
    19.2 KB · Views: 1
Also,
Another issue I am encountering is when trying to execute the code just to copy the "Invoice" sheet, it goes to Debug at this point:
Set wbNew = Workbooks.Open(Cells(i, "C").Value)
It is not continuing further. The Path for the new workbooks is on C Column.
 
The second error is my fauly, I forgot to tie the Cells objects back to the correct parent object.

I tried to clean things up a bit. I'm assuming that the worksheet called out in cell B2 is the one w/ Invoice sheet AND the module to be exported? I changed the code to reflect that.
 

Attachments

  • COPY DATA - TEST LM.xlsm
    20.3 KB · Views: 1
Hi Luke,

The macro is copying the "Invoice" sheet perfectly but it does not work for the module, it is not copying the module. Perhaps I should do another exercise just for the module?
I have attached the workbook where I have added the SpellNumber function.

Regards,
Fadil
 

Attachments

  • COPY DATA - TEST LM.xlsm
    20.3 KB · Views: 3
What error are you getting when it tries to do the export? If it says something like "VB model is not trusted", that means that you never setup the security setting yet. (Excel Options - Trust Center - Trust Center Settings - Macro Settings - Trust Access to VBA project object model

Side note: I'd recommend turning this option off after running your macro, as a security precaution.
 
OMG, it worked perfectly, that was the deal, trust center settings for the VBA code to import.

Thank you very much for all your effort on this project. I will update my files now.

Please accept a symbolic donation as a thank you note, donation made on your name using this site.

Best regards,
Fadil
 
Sweet, glad we were able to get it working for you. And thank you for your generosity. :) :awesome:
 
Hi Luke,

Just a quick question. Will you be able to help me with deleting a module on this project we were working on few weeks back. I had to import the sheet once again so the module was imported again. So, now I have module named SpellNumber and another one SpellNumber1
I want to delete SpellNumber1 module from all workbooks, will you be able to help me with a code to do that

Regards,
Fadil
 
Hi Luke,
I tried the code to remove Module but it is stopping at this point

Dim VBProj As VBIDE.VBProject


The whole code is below: I am trying to delete modul "SpellNumber1"

Sub Button6_Click()
Dim strPath As String
Dim book1 As Workbook
Dim book2 As Workbook
Dim lRow1 As Long
Dim DLr As Long
Dim SName As String
Dim lRow2 As Long
Dim PID As String
Dim PName As String
Dim PInfo As String
Dim shName As String
Dim i As Long
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

Set book1 = ThisWorkbook
lRow1 = Cells(Rows.Count, 2).End(xlUp).Row
shName = Range("E6").Value

book1.Sheets("ControlPannel").Activate

For i = 13 To lRow1
strPath = Trim(Cells(i, 2).Value)

Workbooks.Open strPath, 0, 0

Set book2 = ActiveWorkbook
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("SpellNumber1")
VBProj.VBComponents.Remove VBComp

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Sheets("MENU").Activate
book2.Close (True)
Cells(i, 1).Value = "Done!"

Next i

Application.Calculation = xlCalculationAutomatic
Set book1 = Nothing
Set book2 = Nothing

End Sub
 
I think you skipped the section labelled:
Objects In The VBA Extensibility Model

Where he talks about adding a reference to your project.
 
Hi Luke,

I followed the procedure, I ticket in the library to activate the item listed but still it is not working to be able to proceed with the deletion of the Module

Thank you,
Fadil
 
Back
Top