Macro to open up Multiple File

[howard]

I have a macro to open up multiple csv files and to copy the data

 Sub Open_MultipleFiles()
Clear_Data
Dim fDialog As Object, varFile As Variant
Dim nb As Workbook, tw As Workbook, ts As Worksheet
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .CutCopyMode = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set fDialog = Application.FileDialog(3)
ChDir "C:\extract"
With fDialog
  .Filters.Clear
  .Filters.Add "Excel files", "*.csv*"
   .Show
   
   For Each varFile In .SelectedItems
      Set nb = Workbooks.Open(Filename:=varFile, local:=True)
        nb.Sheets(1).Range("A1:AZ500").Copy Destination:=ThisWorkbook.Sheets("Imported Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
        nb.Close False
   Next
End With
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .CutCopyMode = True
End With
With Sheets("Imported Data")
.Range("a1").EntireRow.Delete
 End With


End Sub



Sub Clear_Data()
Dim LR As Long
With Sheets("Imported Data")
 LR = .Cells(.Rows.Count, "B").End(xlUp).Row
  .Range("A1:AZ" & LR).ClearContents
    End With

End Sub

However, when Running the macro, I get a run time error "Object variable or with block variable not set"


Kindly test and amend my code

 

[dmt32]

Hi,
try these updates to your codes & see if will do what you want

Sub Open_MultipleFiles()
    Dim fDialog         As Object
    Dim varFile         As Variant
    Dim nb              As Workbook, tw As Workbook
    Dim wsImportData    As Worksheet

    Set tw = ThisWorkbook
    Set wsImportData = tw.Worksheets("Imported Data")
    
    Clear_Data wsImportData
    
    Set fDialog = Application.FileDialog(3)
    
    ChDir "C:\extract"
    
    With fDialog
        .Filters.Clear
        .Filters.Add "Excel files", "*.csv*"
        
        'user pressed OK.
        If .Show = -1 Then
            EventsEnable False
            For Each varFile In .SelectedItems
                Set nb = Workbooks.Open(Filename:=varFile, local:=True)
                nb.Sheets(1).Range("A1:AZ500").Copy Destination:= _
                                                    wsImportData.Range("A" & wsImportData.Rows.Count).End(xlUp).Offset(1)
                nb.Close False
                Set nb = Nothing
            Next
        Else
            'cancel pressed
            Exit Sub
        End If
    End With
    
    wsImportData.Range("a1").EntireRow.Delete
    
    EventsEnable True
    
End Sub

Sub EventsEnable(ByVal state As Boolean)
    With Application
        .ScreenUpdating = state: .CutCopyMode = state
        .Calculation = IIf(state, xlCalculationAutomatic, xlCalculationManual)
    End With
End Sub

Sub Clear_Data(ByVal sh As Object)
    Dim LR          As Long
    LR = sh.Cells(sh.Rows.Count, "B").End(xlUp).Row
    sh.Range("A1:AZ" & LR).ClearContents
End Sub

Dave

 

[howard]

Hi Dave

Thanks for the help. Your code works perfectly

 

[dmt32]

Hi Dave

Thanks for the help. Your code works perfectly

welcome - appreciate feedback

Dave