Copy VBA with a batch file

LCTrucido

Board Regular
Joined
Apr 29, 2011
Messages
88
Here's the scenario:

I will be creating an excel report from another program on a weekly basis. I have a couple of macros that need to be added to each excel report before they can be used.

If I save the macros in a text file, or as part of a .bat, is there a way for the .bat file to add the macros to each new report?

I will only have one report at a time, and can put it in the same directory as the batch file before running it (the batch file).

I realize that I can simply copy the macros manually but there will be other people who have to do this as well and I need to make it dummy-proof.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Update:

I was able to use the info in this thread which works, thank you Nirvana...

This is one of the macros that is being imported:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCell As Range

'The variable KeyCell contains the cell that will
'cause an alert when it is changed.
Set KeyCell = Range("K1")

If Not Application.Intersect(KeyCell, Range(Target.Address)) _
Is Nothing Then

'When the variable KeyCell changes, run the following macro
FindDuplicate

End If
End Sub

The problem is that this code only works if it's located in the sheet, not a module. Is there a way to get it to work from the imported module?
 
Upvote 0
Sorry for the multiple posts; the more I research the issue the more apparent it is that I'm asking the wrong questions.

It would appear that saving and importing my macros as a .bas is not going to work.

Is there a way to import a macro to a new sheet in such a way so as to allow the use of a private worksheet change event in the imported macro?

I'm using 2003, btw.
 
Upvote 0
Hello

I do not have the same information as you , meaning that you have not been fully clear above, but what I do think is that you are overcomplicating matters.

If I have this Change event in a sheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox Target(1).Value
End Sub

and in a different file, in a general module, this code:

Code:
ActiveSheet.Copy

When I launch that code with the first sheet activated, it creates a new file with the Change event code present. I would follow a similar strategy if I were you and if I understand your requirements.
 
Upvote 0
Hello

I do not have the same information as you , meaning that you have not been fully clear above, but what I do think is that you are overcomplicating matters.

I know, and I apologize. As I've said in the past, VBA is brand new to me and what I have so far is largely the result of piecing together solutions from other threads and knowledge bases. I'll try again, and yes, this is related to the issue you helped me with yesterday...

1. I will be exporting an inventory report from another program into excel format on a regular basis. The exported data will always be in the same format with the same headers, but the content will change.

2. I have two macros that need to be added to each new report in order for a hand scanner to work with the report. If both of them are located in the Sheet1 object they work properly.

3. I found a way in the thread linked to above to copy a module from one workbook to another using the following code:

Code:
Sub ImportModules()
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objfolder = objFSO.getfolder("C:\Module Project\ExcelFiles")
    For Each f In objfolder.Files
        Workbooks.Open f.Path, , False
        ActiveWorkbook.VBProject.VBComponents.Import "C:\Module Project\script.bas"
        ActiveWorkbook.Save
        ActiveWorkbook.Close
    Next
End Sub
This solution works, but because my macro 1 is imported into a module instead of the object Sheet1, the change event won't work when the KeyCell is changed.

4. What I'm trying to do then, is find a way to copy both of my macros into Sheet1 instead of a separate module.

I hope this makes sense, my macros are below.

Macro 1 checks a target cell for a change value. When it sees a change, it calls macro 2.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCell As Range

    'The variable KeyCell contains the cell that will
    'cause an alert when it is changed.
    Set KeyCell = Range("K1")

    If Not Application.Intersect(KeyCell, Range(Target.Address)) _
        Is Nothing Then

        'When the variable KeyCell changes, run the following macro
        FindDuplicate
       
    End If
End Sub
Macro 2 looks for the data in K1. If it finds it, it highlights the row containing the data. If it doesn't find it, it displays a message box to that effect.
Code:
Sub FindDuplicate()

    Dim ICCR As String
    Dim FoundCell As Range
    
    'The variable ICCR contains the value that will be searched for
    ICCR = Range("K1").Value
  
    'Searches for the contents of the variable ICCR
    On Error Resume Next
    
    Set FoundCell = Range("K:K").Find(What:=ICCR, _
                  After:=Range("K1"), _
                  LookIn:=xlValues, _
                  LookAt:=xlWhole, _
                  SearchOrder:=xlByRows, _
                  SearchDirection:=xlNext, _
                  MatchCase:=False, _
                  SearchFormat:=False)
  
    'If the ICCR is not found, display a message box to the user
    If FoundCell.Address(0, 0) = "K1" Then
        ActiveSheet.Range("K1").Select
        MsgBox "ICCR " & Range("K1") & " Not Found"
        Exit Sub
        
    'If the ICCR is found, highlight the corresponding row
    Else
        Rows(FoundCell.Row).Select
    End If
  
End Sub
Hopefully I explained a little better this time around.
 
Upvote 0
Hi All,

1. I will be exporting an inventory report from another program into excel format on a regular basis. The exported data will always be in the same format with the same headers, but the content will change.


Not sure if of any help, but maybe just for us with thick skulls (namely me), just to be certain of what we're faced with: In the first bit, you mention exporting, but from another program. Are you in, or that is operating from the other program when the data is plopped into a (presumably) created Excel Worksheet? OR... are we using Excel to import the data initially?
 
Upvote 0
Hi All,




Not sure if of any help, but maybe just for us with thick skulls (namely me), just to be certain of what we're faced with: In the first bit, you mention exporting, but from another program. Are you in, or that is operating from the other program when the data is plopped into a (presumably) created Excel Worksheet? OR... are we using Excel to import the data initially?

The other program creates a report which is then saved as an excel file. It's that excel file that I then need to copy the macros to.

Basically, I have a master excel file that contains the macros. I run the report, save as excel, then copy the macros from the master file to the new one.
 
Upvote 0
Sorry, another quick question I should have thought of:

When the new wb is created by the other program, is the created wb:
  • a single-sheet wb?
  • that single sheet always has the default name of Sheet1 on the tab?
  • If you look in VBIDE (the code window) and look in the project window, the sheet is referred to as Sheet1 (Sheet1) ?
 
Upvote 0
For the sake of this exercise let's say yes to all three. I can change my existing code to match the parameters later if they change.
 
Upvote 0
Okay, since we can't really import a module effectively. As Sheet1 already has a module, we can add the code to it. (Slow typist here and I have to go, so partial code for brevity's sakes.)

In a Standard Module:

Rich (BB code):
Option Explicit
    
Sub ImportModules()
Dim objFSO          As Object ' FileSystemObject
Dim objFolder       As Object ' Folder
Dim f               As Object '  File
Dim wb              As Workbook
Dim o_CodeModule    As Object ' CodeModule
Dim o_vbComponent   As Object ' VBComponent
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    '//TESTING, change path back                                                            //
    'Set objFolder = objFSO.GetFolder("C:\Module Project\ExcelFiles")
    Set objFolder = objFSO.GetFolder(ThisWorkbook.Path & "\")
    
    For Each f In objFolder.Files
        If Not f.Path = ThisWorkbook.FullName Then  'Just because I tested in the same folder
        
            Set wb = Workbooks.Open(f.Path, , False)
            With wb.VBProject
                
                '// Just in case the CodeName 'Sheet1' does not exist.                      //
                On Error Resume Next
                Set o_vbComponent = .VBComponents("Sheet1")
                On Error GoTo 0
                
                If Not o_vbComponent Is Nothing Then
                
                    Set o_CodeModule = o_vbComponent.CodeModule
                    
                    o_CodeModule.AddFromString _
                        Space(2) & vbCrLf & _
                        "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf & _
                        "Dim KeyCell As Range" & vbCrLf & vbCrLf & _
                        Space(4) & "'The variable KeyCell contains the cell that will " & _
                        "cause an alert when it is changed." & vbCrLf & _
                        Space(4) & "Set KeyCell = Range(""K1"")" & vbCrLf & vbCrLf & _
                        Space(4) & "If Not Application.Intersect(KeyCell, Range(Target.Address)) " & _
                        "Is Nothing Then" & vbCrLf & _
                        Space(8) & "FindDuplicate" & vbCrLf & _
                        Space(4) & "End If" & vbCrLf & _
                        "End Sub" & vbCrLf & vbCrLf & _
                        Space(2) & _
                        "Private Sub FindDuplicate()" & vbCrLf & _
                        Space(4) & "MsgBox ""...ETC/Remainder of code..."", 0, """"" & vbCrLf & _
                        "End Sub"
                    
                    wb.Close True
                Else
                    '// NOT tested...   //
                End If
            End With
        End If
    Next
End Sub

For simplicity's sake, as inferred, I would just add FindDuplicate to the sheet's module as a Private sub.

As to FindDuplicate(), might I suggest ditching On Error Resume Next in this case. You have it a ways up in the code and ignore errors for the remainder. IMO, we want to ignore errors for only a line or two or whatever is the minimum we can, where we are sure of what error may occur and have handled. In this case, we could just check to see if .Find returned anything.

Rich (BB code):
    If Not FoundCell Is Nothing Then
        If FoundCell.Address(0, 0) = "K1" Then
            ActiveSheet.Range("K1").Select
            MsgBox "ICCR " & Range("K1") & " Not Found"
            Exit Sub
            
        'If the ICCR is found, highlight the corresponding row
        Else
            Rows(FoundCell.Row).Select
        End If
    Else
        'whatever
    End If

Hope that helps,

Mark
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top