Open CSV Files

[NatalieEC]

Hello,

I would like to create a code that allows you to select the directory, and import each csv file into a worksheet. I would like each csv file to be ten columns apart. I am not looking for someone to write the code for me because I want to learn and understand it. I have found some code that allows me to select the directory.

Please Login or Register  to view this content.

If I want to set a string variable to the directory, how do I do that? I found this chunk of code online and I don't completely understand it. Also, I recorded a macro of one csv file I imported to see what code was used. I put an X in the directory name and a Y in the file name. Would I be able to put the string variable where I put X?

Please Login or Register  to view this content.

Also, how would I going to the next file in the folder?

Thanks, Natalie

[LJMetzger]

Hi Natalie,

Take a look at the file in post #17 of the following thread: http://www.excelforum.com/excel-prog...a-a-macro.html

See post #19 in the above thread for some debugger tips.

Download the two .csv files from post #1 in the above thread, if you want to test out the above file.

It has code in Sub SetDataFolderName() to select a folder and put the result in a spreadsheet cell.

Sub UpdateDataCountByClient() processes all .csv files in that folder. You should be able to see how Microsoft function Dir() loops through the files until the list is exhausted.

My application processes the .csv files in a different manner than your use of QueryTables.Add. The use of QueryTables.Add is perfectly fine. In your question, X should be the combination of folder and file name, and Y should be the file name.

Lewis

[NatalieEC]

Hi Lewis,

Thanks for your suggestion. Unfortunately, when I download the code, I can't see the code.

I did follow (I think) your answer. Here is the code I have so far. It is inchoate at the moment. I have it so that you can select a directory, count how many files are in that directory and hopefully import them. I have it so that the file name can be entered (ie, X-i). I enter the file name without the i. I hope my idea sort of makes sense.

Please Login or Register  to view this content.

I get an error at the first few lines after with. It is a run time error, application defined or object defined error

[NatalieEC]

Hey, I actually fixed it! Thanks for your help.

Please Login or Register  to view this content.

[LJMetzger]

When you download the code, the file should be saved in your Downloads folder or whichever folder your computer/browser puts downloaded files.

First a couple of tips/suggestions:
To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx


Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

Your primary problem was with the following line:

Please Login or Register  to view this content.

It was in the wrong place (outside the loop) and omitted the following items in red:

Please Login or Register  to view this content.

You also had a spelling error (flder), but that didn't seem to affect the code.

My final version of your code (tested and working) is:

Please Login or Register  to view this content.

Please let me know if you have any more problems and/or questions.

Lewis

[NatalieEC]

Thanks so much for everything Lewis! My code is now working as I want it

[LJMetzger]

My pleasure. I see that you not only got it working, but you changed the starting range for each file. Nice job.

Lewis