CODEWITHSUNDEEP
vbaexcel
ChrisProsser
ChrisProsser

Reputation: 13088

How to execute a vba procedure in your current workbook against another workbook

I have a template workbook (Temp_TS) that I use to create new files (based on another template) and want to then run vba code contained in Temp_TS against the new file. As all of the VBA is contained in Temp_TS, how do I specify that this should be executed against the new file I have created?

An example of one of the procedures that I will run against the new file is shown below:

Sub DelSheets(Optional p_sheet_to_keep1 As String = "xxx", _
              Optional p_sheet_to_keep2 As String = "yyy", _
              Optional p_sheet_to_keep3 As String = "zzz")
On Error GoTo DelSheets_err
    ' e.g. call gen.DelSheets("Parameters", "Chris Prosser")
    Dim ws As Worksheet
    Application.DisplayAlerts = False

    For Each ws In Worksheets
        If LCase(ws.Name) <> LCase(p_sheet_to_keep1) And _
           LCase(ws.Name) <> LCase(p_sheet_to_keep2) And _
           LCase(ws.Name) <> LCase(p_sheet_to_keep3) Then
            ws.Delete
        End If
    Next
    Application.DisplayAlerts = True

DelSheets_exit:
    Exit Sub

DelSheets_err:
    Debug.Print "Error: ", Err.Number, vbCrLf, Err.Description
    Resume Next
End Sub

To execute this against the current workbook I would just do something like:

gen.DelSheets("Parameters", "Chris Prosser")

How would I specify that I want this to execute against the new workbook and not the current workbook?

EDIT

In case it helps anyone here is the method used to create the new sheet:

strCommand = "cp """ & oldFileName & """ """ & newFileName & """"
Shell strCommand, 1

This part works fine, the file is created but not opened yet.

Upvotes: 1

Views: 350

Answers (1)

user2140173
user2140173

Reputation:

Modify your DelSheet sub to accept a workbook as parameter (send it byRef)

Then add wb.Worksheets in your delSheet

Now you could call it from the Main sub like this

Sub Main()

    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\Users\...\Desktop\file1234.xlsm")

    DelSheets (wb)

    wb.Save
    wb.Saved = True
    wb.Close
    Set wb = Nothing

End Sub


Sub DelSheets(ByRef wb As Workbook, Optional p_sheet_to_keep1 As String = "xxx", _
              Optional p_sheet_to_keep2 As String = "yyy", _
              Optional p_sheet_to_keep3 As String = "zzz")
On Error GoTo DelSheets_err
    ' e.g. call gen.DelSheets("Parameters", "Chris Prosser")
    Dim ws As Worksheet
    Application.DisplayAlerts = False

    For Each ws In wb.Worksheets
        If LCase(ws.Name) <> LCase(p_sheet_to_keep1) And _
           LCase(ws.Name) <> LCase(p_sheet_to_keep2) And _
           LCase(ws.Name) <> LCase(p_sheet_to_keep3) Then
            ws.Delete
        End If
    Next
    Application.DisplayAlerts = True

DelSheets_exit:
    Exit Sub

DelSheets_err:
    Debug.Print "Error: ", Err.Number, vbCrLf, Err.Description
    Resume Next
End Sub

Upvotes: 2

Related Questions