Grab flatfile data to xlsm & make More Copies
Hello All,
I am trying to figure out the best way via VBA to do this:
Regarding code: FSO Method is greatly preferred over f = dir ()
I have a main.xlsm file which will read a folder and
1. Go through each .csv file in that folder.
2. open up a csv file to get the csv data and copy it over to cell C8 of sheet1 of main.xlsm file
3. Then saveAS (in the same folder as the xlsm file) the xlsm file with the name of the csv file that it copied data from
4. Closes the csv file and moves on to the next csv in the target folder
So for example: If there were three csv files in the folder : csv1.csv,csv2.csv,csv3.csv
Thanks
main.xlsm
csv3.csv
csv2.csv
csv1.csv
I am trying to figure out the best way via VBA to do this:
Regarding code: FSO Method is greatly preferred over f = dir ()
I have a main.xlsm file which will read a folder and
1. Go through each .csv file in that folder.
2. open up a csv file to get the csv data and copy it over to cell C8 of sheet1 of main.xlsm file
3. Then saveAS (in the same folder as the xlsm file) the xlsm file with the name of the csv file that it copied data from
4. Closes the csv file and moves on to the next csv in the target folder
So for example: If there were three csv files in the folder : csv1.csv,csv2.csv,csv3.csv
Thanks
main.xlsm
csv3.csv
csv2.csv
csv1.csv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect Brian,
you are always there to help :)
you are always there to help :)
ASKER
thank you
Thanks, Rayne - always glad to help!
(Out of curiosity, why the lack of love for Dir()?!)
(Out of curiosity, why the lack of love for Dir()?!)
ASKER
Hello Brian,
Sure I will be glad to share the thought :)
In this question:
https://www.experts-exchange.com/questions/27985710/Grab-data-from-files-and-put-to-sheet-with-names.html
I tried the expert's F = DIR() idea but when I tested it, I found it kept looping over the same exact file in the directory THAN moving onto next file...Not sure why it did that same file infinite loop on my computer. So instead of spending more time on why its not working, I gave up and used the FSO and it worked great.
Rayne
Sure I will be glad to share the thought :)
In this question:
https://www.experts-exchange.com/questions/27985710/Grab-data-from-files-and-put-to-sheet-with-names.html
I tried the expert's F = DIR() idea but when I tested it, I found it kept looping over the same exact file in the directory THAN moving onto next file...Not sure why it did that same file infinite loop on my computer. So instead of spending more time on why its not working, I gave up and used the FSO and it worked great.
Rayne
Thanks, Rayne. I'll have a look, but it'll be a couple of days.
Rayne,
I've looked at that macro and I can't see any problems with it. (I even replaced the updates with Debug.Print and got exactly the results I expected.)
The key ideas are...
(1) Run Dir() once with parameters. You need to check for a blank result - that means the file (or files) don't exist.
(2) Repeatedly run Dir() without parameters. Again, you need to check for a blank result - that means there are no more files and so you need to break out of the loop.
There are few ways that you could accidentally cause a loop, but, seeing that the there is no "Option Explicit" , I think it very possible that "Fil" was misspelled - "FiI" or "Fi1", perhaps.
Another possibility would be to include a file name in the "looping" Dir().
Finally, any chance that the wrong (or no) variable was used for controlling the loop?
Regards,
Brian.
I've looked at that macro and I can't see any problems with it. (I even replaced the updates with Debug.Print and got exactly the results I expected.)
The key ideas are...
(1) Run Dir() once with parameters. You need to check for a blank result - that means the file (or files) don't exist.
(2) Repeatedly run Dir() without parameters. Again, you need to check for a blank result - that means there are no more files and so you need to break out of the loop.
There are few ways that you could accidentally cause a loop, but, seeing that the there is no "Option Explicit" , I think it very possible that "Fil" was misspelled - "FiI" or "Fi1", perhaps.
Another possibility would be to include a file name in the "looping" Dir().
Finally, any chance that the wrong (or no) variable was used for controlling the loop?
Regards,
Brian.
ASKER
Hello Brian,
Thank you for detailed analysis. Maybe one of the other went wrong :(
Sure I will keep that in mind next time. Maybe ask a new question next time with especially DIR() stuff and notify you
Thank you :) again for your effort to help me
Thank you for detailed analysis. Maybe one of the other went wrong :(
Sure I will keep that in mind next time. Maybe ask a new question next time with especially DIR() stuff and notify you
Thank you :) again for your effort to help me
Glad to help, Rayne!
(Rather than pushing the expert to use Dir(), you'll probably get a better answer by letting him/her choose!)
(Rather than pushing the expert to use Dir(), you'll probably get a better answer by letting him/her choose!)
ASKER