macro with multiple csv

[Guest]

Created a macro to pull data in from a csv file to a spreadsheet. Works
fine. There are 27 other csv files i would like to pull the same data into
the same spreadsheet. The data from each csv would drop into its own column.
Can my macro be setup to simply address or activate all the csv's "in one
line" of text or must the macro contain duplicated instructions pertinent to
each sheet?
OK, i am not great at this.
Thanks for any help! jb

 

[Tim Williams]

You could modify your current code to take a parameter which is the name of the csv file. Call your code in a loop, passing each
file name in turn.

If you were to post your current code and also explain how the other files are identified I'm sure someone will offer a
suggestion...

 

[Guest]

Hi JB:

You need to have a loop to go through the csv files.

If the files are sequentially numbered like

shop001.csv
shop002.csv
shop027.csv

then you can loop through and put each shop in its own column like
shop001=col 2,
shop002 = col 3 ...

you cna have a table in your workbook that you read that details the file
and the columns to put them and then loop through the table

you could read all the files in one directory that meet specific criteria:

like: 20070222_shop???.cxv

You then need to modify the macro to work in the general case so that it can
be called from the loop as in


function dojob(szFileName as string, lcolumn_no as long, worksheet2paste as
worksheet) as boolean
' code for processsing the 1 csv file
' flex the column on the lcolumn given
' it is a function and you can return TRUE on success and FALSE failure
end function

You need some interation with the user to cinfirm the files as in get the
folder or confirm the files tro be done.

Without the code little difficult to give firm a example.

 

[Guest]

hi, you can use the following code to loop :

With Application.FileSearch
.NewSearch
.LookIn = "c:\a\"
.SearchSubFolders = True
.Filename = "*.*"
.Execute
FilesToProcess = .FoundFiles.Count
For i = 1 To .FoundFiles.Count
thisentry = .FoundFiles(i)
Workbooks.Open thisentry
activewkb_name = ActiveWorkbook.Name
ActiveWorkbook.Close
Next i
End With

 

[Guest]

Thanks to all for the great info. I shall dive into it and see how i fare
and then post again.
Thank you all. jb