Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro with multiple csv
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro with multiple csv
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... -- Tim Williams Palo Alto, CA "jb" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro with multiple csv
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "jb" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro with multiple csv
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 "Martin Fishlock" wrote: 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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "jb" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro with multiple csv
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 "niket patel" wrote: 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 "Martin Fishlock" wrote: 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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "jb" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make a macro to clear multiple cells from multiple worksheets? | Excel Worksheet Functions | |||
Macro: Filter Multiple header then extract to Multiple Files | Excel Discussion (Misc queries) | |||
Modify macro code to export multiple cell contents to multiple Text Files | Excel Programming | |||
macro: copy multiple workbooks to multiple tabs in single book | Excel Programming | |||
Macro for multiple charting of multiple datasets | Excel Programming |