How can I import excel sheets in the way I am importing csv ones using VBA?

Question

I currently have this code for importing multiple .csv files, where if the file name matches an existing sheet in my excel file, then the content will be automatically pasted into it. And if not in creates a tab with the exact same name.

I was wondering if by any chance could I replace the QueryTables command used to import the data from a .csv file and use something similar for .xlsx files.

Thank you

PS: it would be to modify mainly te importCSV part. For now I won’t be needing anymore csv formats.

It was just to explain the logic of the importing method. I need something to allow me to import data coming from multiple xlsx into the corresponding Tab in my master excel. Thankss!

Sub CopyCSVfiles()
    Dim naming As Object
    Dim xlsheet As Worksheet
    Dim qt As QueryTable
    Dim txtfilesToOpen As Variant, txtfile As Variant

    Application.ScreenUpdating = False
    Set Name = CreateObject("Scripting.FileSystemObject")

    txtfilesToOpen = Application.GetOpenFilename _
                 (FileFilter:="Text Files (*.csv), *.csv", _
                  MultiSelect:=True, Title:="Text Files to Open")

    For Each txtfile In txtfilesToOpen
        ' Here we find if there is an already existing worksheet
        For Each xlsheet In ThisWorkbook.Worksheets
            If xlsheet.Name = Replace(naming.GetFileName(txtfile), ".csv", "") Then
                xlsheet.Activate
                GoTo ImportCSV
            End If
        Next xlsheet

        ' CREATES A NEW WORKSHEET IF NOT FOUND
        Set xlsheet = ThisWorkbook.Worksheets.Add( _
                             After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        xlsheet.Name = Replace(naming.GetFileName(txtfile), ".csv", "")
        xlsheet.Activate
        GoTo ImportCSV

ImportCSV:
        ' DELETE EXISTING DATA
        ActiveSheet.Range("A:Z").EntireColumn.Delete xlShiftToLeft

        ' IMPORT DATA FROM TEXT FILE
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & txtfile, _
          Destination:=ActiveSheet.Cells(1, 1))
            .TextFileParseType = xlDelimited
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "|"

            .Refresh BackgroundQuery:=False
        End With

        For Each qt In ActiveSheet.QueryTables
            qt.Delete
        Next qt
    Next txtfile

    Application.ScreenUpdating = True
    MsgBox "Successfully imported text files!", vbInformation, "SUCCESSFUL IMPORT"

    Set naming = Nothing
End Sub

not based on your existing code. the data structure for a csv is very different to an xlsx. CVS is effectively a simple data structure hence why your query tables has the options to define the delimiter and headings info. This would not be possible with an xlsx which has multiple tabs, and can have data starting in different cells — v-c0de, Nov 03 '20 at 12:42
have a look at this and you may need to create two subs (one for CSVs and another for xml formats) : https://stackoverflow.com/questions/7876178/importing-excel-spreadsheet-data-into-another-excel-spreadsheet-containing-vba — v-c0de, Nov 03 '20 at 12:43

W. García · Answer 1 · 2020-11-06T02:31:58.757

For copy the information from a CSV file and put it in an Excel Worksheet, directly from VBA, you can take advantage of this project. If you don't know how to start with the utility, read the installation instructions.

Replace the following code:

        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & txtfile, _
          Destination:=ActiveSheet.Cells(1, 1))
            .TextFileParseType = xlDelimited
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "|"

            .Refresh BackgroundQuery:=False
        End With

        For Each qt In ActiveSheet.QueryTables
            qt.Delete
        Next qt

with a call to this procedure:

Sub ImportCSVRecords(filePathAndName As String, OutputSheet As String, OutputRange As String)
    Dim CSVix As CSVinterface
    
    Set CSVix = New CSVinterface 'Create new instance
    Call CSVix.OpenConnection(filePathAndName) 'Open a physical connection to the CSV file
    Call CSVix.ImportFromCSV 'Import data
    Call CSVix.DumpToSheet(WBookName:=ThisWorkbook.Name, SheetName:=OutputSheet, rngName:=OutputRange) 'Dumps the data to the current Workbook's OutputSheet starting at named OutputRange.
    Set CSVix = Nothing 'Terminate the current instance
End Sub

How can I import excel sheets in the way I am importing csv ones using VBA?

1 Answers1