Macro to open file and copy data

[howard]

I have code below to open a workbook in C:\extract and to copy the data

The code works well, but need it amended so to limit the number of files to only contain New Vehicles.csv for eg BR1 New Vehicles Sales Report.csv so to make it easier for the user to select the correct file



It would be appreciated if someone could kindly amend my code

 Sub Open_NV_Workbook()
ChDir ("C:\extract")

     With Sheets("New Vehicles")
     .Range("A1:AM1500").ClearContents
     End With
     
    Dim nb As Workbook, ts As Worksheet, A As Variant
    Dim rngDestination As Range
    
    Set ts = ActiveSheet
    With Sheets("New Vehicles")
    .Select
    End With
    
    On Error Resume Next
    Set rngDestination = Application.Range("'new vehicles'!A1")
    
    On Error GoTo 0
   If rngDestination Is Nothing Then Exit Sub  'User canceled
    MsgBox ("Select New Vehicle Report")
    A = Application.GetOpenFilename
    If A = False Or IsEmpty(A) Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set nb = Workbooks.Open(Filename:=A, local:=True)
    ThisWorkbook.Activate
    
    nb.Sheets(1).Range("A:AM").Copy
    rngDestination.PasteSpecial Paste:=xlPasteValues
    rngDestination.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
  
    nb.Close savechanges:=False 'Close the source workbook
     
    
    
End Sub

 

[Domenic]

You can use the FileDialog object, here's an example...

    Dim filename As Variant
    With Application.FileDialog(msoFileDialogFilePicker)
        .ButtonName = "Select"
        With .Filters
            .Clear
            .Add "CSV Files", "*.csv"
        End With
        .FilterIndex = 1
        .InitialFileName = "*New Vehicles*.csv"
        .Title = "Select CSV file"
        If .Show = 0 Then Exit Sub
        filename = .SelectedItems(1)
    End With
    
    Debug.Print filename

Hope this helps!

 

[howard]

Thanks for the help Domenic

I have included your code, but the range select for the source data in not being opied and pasted on sheet "New Vehicles"

Kindly amend code accordingly

 Sub Open_NV_Workbook()
ChDir ("C:\extract")

     With Sheets("New Vehicles")
     .Range("A1:AM1500").ClearContents
     End With

    Dim nb As Workbook, ts As Worksheet, A As Variant
    Dim rngDestination As Range
  Dim filename As Variant
    Set ts = ActiveSheet
    With Sheets("New Vehicles")
    .Select
    End With
    
    On Error Resume Next
    Set rngDestination = Application.Range("'new vehicles'!A1")
    
    On Error GoTo 0
   If rngDestination Is Nothing Then Exit Sub  'User canceled
    MsgBox ("Select New Vehicle Report")
 
    With Application.FileDialog(msoFileDialogFilePicker)
        .ButtonName = "Select"
        With .Filters
            .Clear
            .Add "CSV Files", "*.csv"
        End With
        .FilterIndex = 1
        .InitialFileName = "*New Vehicles*.csv"
        .Title = "Select CSV file"
        If .Show = 0 Then Exit Sub
        filename = .SelectedItems(1)
    End With
    Debug.Print filename
    
    Application.ScreenUpdating = False
   'A = Application.GetOpenFilename
   'If A = False Or IsEmpty(A) Then Exit Sub
   'Set nb = Workbooks.Open(filename:=A, local:=True)
    ThisWorkbook.Activate
    
    Sheets(1).Range("A:AM").Copy
    rngDestination.PasteSpecial Paste:=xlPasteValues
    rngDestination.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
  
        
    
End Sub

 

[howard]

I managed to amend your code and it now works 100%

 Sub Open_NV_Workbook()

    ChDir ("C:\extract")

    With Sheets("New Vehicles")
        .Range("A1:AM1500").ClearContents
    End With

    Dim nb As Workbook, ts As Worksheet, A As Variant
    Dim rngDestination As Range
    Dim filename As Variant
    Set ts = ActiveSheet
    With Sheets("New Vehicles")
        .Select
    End With
    
    On Error Resume Next
    Set rngDestination = Application.Range("'new vehicles'!A1")
    
    On Error GoTo 0
    If rngDestination Is Nothing Then Exit Sub  'User canceled
    MsgBox ("Select New Vehicle Report")
 
    With Application.FileDialog(msoFileDialogFilePicker)
        .ButtonName = "Select"
        With .Filters
            .Clear
            .Add "CSV Files", "*.csv"
        End With
        .FilterIndex = 1
        .InitialFileName = "*New Vehicles*.csv"
        .Title = "Select CSV file"
        If .Show = 0 Then Exit Sub
        filename = .SelectedItems(1)
    End With
    Debug.Print filename
    
    Application.ScreenUpdating = False
    Dim srcWorkbook As Workbook
    Set srcWorkbook = Workbooks.Open(filename:=filename, local:=True)
    ThisWorkbook.Activate
    
    srcWorkbook.Sheets(1).Range("A:AM").Copy
    
    rngDestination.PasteSpecial Paste:=xlPasteValues
    rngDestination.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    
    srcWorkbook.Close SaveChanges:=False

End Sub /code]