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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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?
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ouestqueAuthor Commented:
Very Interesting! Thanks Ejgil!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.