Announcement

Collapse
No announcement yet.

Select and apply macro to multiple workbooks in turn

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Select and apply macro to multiple workbooks in turn



    Hi, this is my first post so please bear with me if I have not done it correctly!
    The below code allows the user to browse for a file and then applies the relevant macro from modules depending on the file format. What I want to do is to enable selection of multiple workbooks and then have the macro applied to each in turn. Never tried doing this before so not sure how to go about it, any help would be appreciated.

    'Showing Excel Open Dialog Form
    vfile = Application.GetOpenFilename("Excel Files (*.csv*), *.csv", 1, "Select Excel File", "Open", False)
    If TypeName(vfile) = "Boolean" Then 'If Cancel then exit
    Exit Sub
    End If


    'Open the selected file
    Workbooks.Open vfile, Local:=True


    'Check to see if correct converter is being used

    '1. Check to see if data is from CT24 tracker
    Application.Goto Reference:="R1C2"
    If ActiveCell.Value = 0 Then ' the CT24 file has all data in Column A
    Call Module1.ct24
    Application.ScreenUpdating = True
    Unload UserForm1
    Application.Goto Reference:="R1C1"
    Exit Sub
    End If

    '2. Datong Rapids or GC-101

    Application.Goto Reference:="R1C7"
    z = ActiveCell.Value
    If Mid(z, 1, 4) = "AUTO" Then
    Call Module2.GC101
    Application.ScreenUpdating = True
    Unload UserForm1
    Application.Goto Reference:="R1C1"
    Exit Sub
    Else
    Call Module3.Datong
    Application.ScreenUpdating = True
    Unload UserForm1
    Application.Goto Reference:="R1C1"
    Exit Sub
    End If

    End Sub


    Private Sub OptionSet1_Click()

    End Sub

    Private Sub UserForm_Click()

    End Sub

  • #2
    Welcome to the forum!

    When pasting code, please paste between code tags. Click the # icon on reply toolbar to insert the tags.

    I guess you want a multi-file pick dialog? So if you were to pick multiple CSV files in a dialog, This would change to maybe:
    Code:
    Dim wb as Workbook
    Set wb = Workbooks.Open(vfile, Local:=True)

    Since the wb is open and not a macro,
    Code:
    Call Module2.GC101
    should probably be:
    Code:
    ThisWorkbook.Module2.GC101
    I am not sure about:
    Code:
    Application.Goto Reference:="R1C2"
    If ActiveCell.Value = 0 Then ' the CT24 file has all data in Column A
    If ActiveCell.Address is B1, I don't know why the comment about CT24 and column A is about.
    If B1 then:
    Code:
    If [B1]= 0 Then ' the CT24 file has all data in Column A
    I did not add the wb prefix to that above as it is the ActiveWorkbook after Workbooks.Open.

    I don't see a wb.Close so that would probably be needed too.

    Here is an example for a multi-select dialog:
    Code:
    Sub Test_FileOpen()
      Dim a, e
      
      a = FileOpen("C:\Users\lenovo1\Dropbox\Excel\CSV")
      If Not IsArray(a) Then Exit Sub
      
      For Each e In a
        'MsgBox e
        'OR do whatever...
      Next e
      
      MsgBox Join(a, vbLf)
    End Sub
    
    Function FileOpen(initialFilename$, Optional Title$ = "File Open", _
      Optional FilterDescription$ = "CSV", _
      Optional FilterExtensions$ = "*.csv")
      Dim x, i As Long
      With Application.FileDialog(msoFileDialogOpen)
        .ButtonName = "&Open"
        .initialFilename = initialFilename
        'Change the contents of the Files of Type list.
            'Empty the list by clearing the FileDialogFilters collection.
            .Filters.Clear
            'Add a filter that includes all files.
        '.Filters.Add "All files", "*.*" 'Usually first...
        '.Filters.Add "Excel (*.xls)", "*.xls", 1
        '.Filters.Add "Excel", "*.xls; *.xlsx; *.xlsm", 1
        .Filters.Add FilterDescription, FilterExtensions, 1
        .Title = Title
        .AllowMultiSelect = True
        If .Show = -1 Then
          ReDim x(1 To .SelectedItems.Count)
          For i = 1 To .SelectedItems.Count
            x(i) = .SelectedItems(i)
          Next i
          FileOpen = x
        End If
      End With
    End Function
    Last edited by Kenneth Hobson; January 12th, 2019, 12:02.

    Comment


    • #3
      Thanks Kenneth. The overall workbook contains multiple modules each of which relates to the file format output by a different device. The bit of code that was unclear to you is to identify the device type from its particular file format so the correct macro can be called. In this instance the ct24 is the only one which has no data in column 2, so this check identifies any data with an empty column 2 as ct24 and calls the appropriate macro. If that makes sense?

      Comment


      • #4
        Incorporate the Test sub and you should be set.

        I don't see all of your Sub but you can include it there or make your Sub accept an input parameter as a workbook or workbook name. Then you would just call it in the Test sub's For Next loop and pass, the workbok(s) name.

        Comment


        • #5


          Kenneth thanks again I will try it as soon as I get a chance and let you know the result. Very pleased that you replied as some of your past posts have proved invaluable to me as a learner, and were the reason I chose to join this forum.

          Comment

          Working...
          X