Help required for Excel VBA n00b

July 30, 2009 6:42 PM

ExcelMacroFilter: Writing a macro that imports a series of .csv files into their own worksheets in the same workbook in Excel 2007 would save me ridiculous amounts of time. I have written a macro to import the files and rename the worksheet accordingly, but I'm new to VBA and can't for the life of me work out how to get Excel to automatically repeat the import from File_1.csv to File_x.csv. Help please?

The research that I am doing at the moment means that I end up with lots of .csv data files at the end of a day, and it takes forever for me to move the data into Excel, so automating this would be great.

The files are all saved in the same folder (for arguments sake FOLDER). and all have a set naming convention (eg. File_1.csv, File_2.csv etc). I feel like I there should be something that says "From i = 1 to x" at the beginning of the code and then a "Next i" at the end, but it is definitely not right. What am I missing here?

posted by cholly to Computers & Internet (4 answers total)

Are you using the "Dir" function to retrieve the csv files? The end case of Dir is "" (nothing), so your loop could look something like this:

filename = Dir(FOLDER)
Do While Not filename = ""
  do stuff
  filename = Dir()
Loop

posted by SuperSquirrel at 6:58 PM on July 30, 2009

2nd dir

(though I'd use:

filename = Dir(FOLDER, "*.csv")

...or it'll try to open non-csv files in that folder too). Also IIRC you don't need the brackets on the second dir. By itself it will just grab the next file in the folder / mask.
posted by pompomtom at 7:07 PM on July 30, 2009

What SuperSquirrel and pompomtom said.

But if you really want to be explicit, something like:

for n = 1 to x
filename = "file_" + n
// open file and process here
next n
posted by orthogonality at 9:10 PM on July 30, 2009

Not sure if my answer is relevant, since it was asked yesterday (that's like 3 months in askmefi years). Assuming you have the import macro ready, plug it to something like below. HTH.

Sub listfiles()
myfolder = "C:\Mycsvfiles\" 'insert your file directory here
With Application.FileSearch
.NewSearch
.LookIn = myfolder
.Filename = "*.csv" 'or *.txt file, whatever text file you need
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count
'call your import here
Next i
End With
End Sub
posted by aseno at 3:16 PM on July 31, 2009