Select only multiple csv files and make tabbed xlsx file gets error 52

Question

I am trying to navigate to a folder with the function GetFilesUserForm then multi select csv files then with the Sub ImportCombineCSVsNavigate make a multi tabbed xlsx file

I think the function GetFilesUserForm is working but ImportCombineCSVsNavigate is expecting a folder not selected files I get error 52 or Bad file name or number not getting how to alter it to work with selected files

Thanks

Option Explicit
Public fPath As String


Sub ImportCombineCSVsNavigate()
'Summary:   Import all CSV files from a folder into separate sheets named    for the CSV filenames
Dim fCSV    As String
Dim wbCSV   As Workbook

'start the CSV file listing
fCSV = Dir(fPath & "*.csv")
Do While Len(fCSV) > 0
    'open a CSV file and move
    Set wbCSV = Workbooks.Open(fPath & fCSV)
    ActiveSheet.Move Before:=ThisWorkbook.Sheets("Helper")
    'ActiveSheet.Move After:=ThisWorkbook.Sheets(Sheets.Count)

    'ready next CSV
    fCSV = Dir
Loop


Set wbCSV = Nothing
End Sub



Function GetFilesUserForm() As String
Dim fd As FileDialog
Dim FileChosen As Integer
Dim filter As String, strPath As String


Set fd = Application.FileDialog(msoFileDialogFilePicker)
strPath = "C:Desktop"
With fd
    .AllowMultiSelect = True
    .Filters.Add "CSV Files", "*.*", 1
    .FilterIndex = 2
    .Title = "Choose CSV File"
    .InitialView = msoFileDialogViewDetails
    '.Show

 FileChosen = fd.Show
   If FileChosen <> -1 Then
      'didn't choose anything (clicked on CANCEL)
       MsgBox "You chose cancel"
       End
   Else

      'display name and path of file chose
        GetFilesUserForm = fd.SelectedItems(1)

   End If
 End With
End Function

stucharo · Accepted Answer · 2015-08-02T15:56:15.923

How do you call the ImportCombineCSVsNavigate() sub? At the moment it doesn't take any arguments. If you made the GetFilesUserForm()return a FileDialogueSelectedItems you could pass that into the ImportCombineCSVsNavigate() sub.

Sub Test()

    Application.ScreenUpdating = False

    Call ImportCombineCSVsNavigate(GetFilesUserForm())

    Application.ScreenUpdating = True

End Sub

Sub ImportCombineCSVsNavigate(files As FileDialogSelectedItems)

    'Summary:   Import all CSV files from a folder into separate sheets named    for the CSV filenames
    Dim wbCSV   As Workbook

    Dim i As Integer
    For i = 1 To files.Count
        Set wbCSV = Workbooks.Open(files(i))
        ActiveSheet.Move Before:=ThisWorkbook.Sheets("Helper")
    Next i

    Set wbCSV = Nothing

End Sub

Function GetFilesUserForm() As FileDialogSelectedItems

    Dim fd As FileDialog
    Dim FileChosen As Integer
    Dim filter As String, strPath As String


    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    strPath = "C:Desktop"
    With fd
        .AllowMultiSelect = True
        .Filters.Add "CSV Files", "*.csv", 1
        .FilterIndex = 0
        .Title = "Choose CSV File"
        .InitialView = msoFileDialogViewDetails
        '.Show
    End With

    FileChosen = fd.Show
    If FileChosen <> -1 Then
      'didn't choose anything (clicked on CANCEL)
       MsgBox "You chose cancel"
       End
    End If

    'return all the files together as FileDialogSelectedItems
    Set GetFilesUserForm = fd.SelectedItems

End Function

Select only multiple csv files and make tabbed xlsx file gets error 52

1 Answers1