Excel VBA importing multiple excel files from a folder

[Tomvba]

Hello,


I'm trying to import a random ammount of excel files into a new excel file.

I used the vba code from this thread as a base: https://www.mrexcel.com/forum/excel...-vba-importing-multiple-csv-files-folder.html

Sub ImportCSVData()

Dim wb As Workbook
Dim wbCSV As Workbook
Dim myPath As String
Dim myFile As Variant
Dim fileType As String
Dim i As Integer


 With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Source Folder"
        .AllowMultiSelect = False
        .Show

        myPath = .SelectedItems(1) & "\"
    End With


  fileType = "*.xlsx*"


  myFile = Dir(myPath & fileType)


 Workbooks.Add

 ActiveWorkbook.SaveAs fileName:= _
        myPath & "Total Results.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Set wb = Workbooks.Open(myPath & "Total Results.xlsm")


  Do While myFile <> ""
    Worksheets.Add(Before:=Worksheets("Blad1")).Name = "bestand " & i + 1

    With ActiveSheet.QueryTables.Add(Connection:="TEXT" & myPath & myFile _
            , Destination:=ActiveSheet.Range("$A$1"))
            .Name = myFile
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
            1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False    'Error occurs here!
        End With
    i = i + 1
    myFile = Dir
    
  Loop

  MsgBox "Result Import Complete"

End Sub

This works great with csv-files. If i try adapting it to xlsx-files, i get an error in this line:

With ActiveSheet.QueryTables.Add(Connection:="TEXT" & myPath & myFile _

How can i make it work? I think it has something to do with the TEXT -part of the code.

Thank you for your help.

 

[Tomvba]

I'm trying to import a random ammount of excel files into a new excel file.[\quote]

I don't mean a random amount, but all xlsx files in the specific folder.

 

[John_w]

The QueryTables TEXT import method (Data tab > From Text) only works with text files. To import .xlsx files the easiest method is Workbooks.Open, to open the source workbook, then copy/import the required range. Other methods are Microsoft Query and Power Query, however they both import the data as a table.

For an example of importing with Workbooks.Open, see https://www.mrexcel.com/forum/excel...itional-variable-post5229226.html#post5229226