Excel VBA: Import Data From text/.csv

What is the best code used to import data from a text/.csv file in Excel file without using: "Workbooks.Open" or "Query Tables". Also the number of rows in the text/.csv files varies largely and changes each day. Therefore, if you are using ADO or some other method, will you please include in your code a way to "Select * From" or "Copy All" without looping through each record?

I am working in a very large environment where "Query Tables" and "Workbooks.open" code works on some machines and not others. (They are all using Excel 2010 and/or 2016 and claim to all have the same setup with Windows 7 and/or Windows 10.)  I will take code that works on one machine and it won't work on another. The code is correct, the full path is defined with and/or without the extension; depending. (i.e., some machines require the extension in the path others will error 1004 if it is there and vice versa. There are also other random problems and the users won't send me a screenshot when it errors nor will they allow me to see/debug the errors on their machine.) Therefore, I would like to look at some solid alternatives as per above.
Who is Participating?
Ejgil HedegaardCommented:
Here is a method where the file is read by VBA, transferred to an array, and the array stored on the sheet.

The first line in the code defines the file.
Change that to the file specified by the user.
Ejgil HedegaardCommented:
Make a text import on a new sheet using the text import wizard.
Then start the macro recorder, and edit the just imported query.
Just accept.
Stop the recorder.
The recorded code should look something like this.
    With Selection.QueryTables(1)
        .Connection = "TEXT;" & CsvFileName
        .TextFilePlatform = 1252
        .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, 3, 3)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Open in new window

Change Selection to the sheet specification, so you don't have to select the sheet to update.
The sheet can also be hidden.
With a variable for the sheet like wsData, where wsData is assigned to the sheet with Set wsData= Worksheets("Data"), so the first line will be
With wsData.QueryTables(1)

The Connection in the recording will include the csv file name.
Using a variable CsvFileName having the complete path to the csv file, the file name can be changed by the code as shown.

Change the settings to not ask for filename on update.

Then you can update the query by running the code.

Formulas referring to the query will automatically adjust to the number of rows in the import.
ouestqueAuthor Commented:
Normally that would be great!!!

Nonetheless, I am automating a process where a new .csv file is downloaded from a specified location each time someone presses a button then imports the data from the newly downloaded .csv. (i.e., the location is specified by the user and can change every hour.)

Do you have any other ideas? that would work with my scenario?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ejgil HedegaardCommented:
I don't understand how you will import the file if the location and file name is not known.
You need to have some way to specify the file, either by selecting, or searching with some known identifiers for the file.
Code can be made for both scenarios, and when the file name and path is stored in the variable CsvFileName, above import will work.
ouestqueAuthor Commented:
Hey Ejgil.  The VBA code works in this way:

1) The location and .csv/text file name is specified by the user
2) The user presses a button
3) The .csv/text file is downloaded
4) Data is downloaded from the .csv/text file

Using a macro recorder to create a 'Query Table' is nice, but (As per my original post) I am looking for a way to do this without 'Query Tables'. Reason being, I am looking for a good full proof way to pull data from a .csv/text file that won't fail. In my environment I have seen them work on some machines and when I bring the exact same code to another machine they won't work.

Do you have any full proof alternatives?
ouestqueAuthor Commented:
Very Interesting! Thanks Ejgil!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.