Renaming .dat to .csv, run through macros and save in correct folder

[holly66]

Someone help! I have a massive project that I need to complete by using macros and I do not know how to complete it. I have a few of the codes needed completed. Here is what I need help with:

I have four folders
New Raw Data DC (T:\Program Circuit Board\P4911 Board Data\ NEW RAW DATA\DC)
New Raw Data Current (T:\Program Circuit Board\P4911 Board Data\ NEW RAW DATA\I)
Processed Raw Data (T:\Program Circuit Board\P4911 Board Data\ PROCESSED RAW DATA)
Complete (T:\Program Circuit Board\P4911 Board Data\ COMPLETE)

I have five.dat files that will be in the New Raw Data DC folder (all ending with A's) and five.dat files that will be in the New Raw Data Current folder (all ending with B's) The ten .dat files will be named xxxxx-A.dat, xxxxx-B.dat, yyyyy-A.dat and yyyyy-B.dat, etc. They correspond with one another. The file numbers will always be different. I need my macro to rename all .dat files as .csv files, open them as .csv files and delete the .dat files out of the New Raw Data folders, having them empty and ready to have new data received and saving the .csv files in the Processed Raw Data folder.

With my ten .csv files open, I need them to run through a template already created. This is the tricky part in my opinion. I need only two files (xxxxx-A and xxxxx-B) to go into the template at a time. After ran through template, I need the template to save in the Complete folder as the tag number (xxxxx).xls. This needs to happen five times, each one being saved as a .xls in the Complete folder. Each .xls file can be saved and close but the .csv still need to stay open.

I will then use my second macro to compile all .csv files open through a master summary template (one that which will keep growing and growing – no overwriting please). I need the .csvs to do this and somehow add to not overwrite (new .csvs to be added to next available column)

After the master summary page runs with the ten .csv, all the .csvs can close (being saved in processed raw data) and the master page can close (being saved in completed).

I know its a lot but I need help!!!! JJ thank youu!!!!

[JBeaucaire]

So Holly, what is your question? What have you done so far? We can help you along with snippets and all, but you want to build this yourself, right, so you fully understand it and can maintain/adjust it as needed, yes?

So, something simple to get you started. To rename xxxxx-A.dat file as xxxxx-A.csv moving it to another folder in the process is pretty easy:

Please Login or Register  to view this content.

Now, along with moving the file you could also open it, process it with your templates in whatever manner you have in mind, all before you set the next fName to start the loop again.

[holly66]

Thanks so much! This is exactly what I needed to begin.

What I am basically trying to do is have corresponding A and B files (two .csv) being copied into a template (with macro, already created) to process data individually and then again with allllll of the A and B files together to process data in a master analysis (with macro, already created). I am just having trouble moving and naming things to get this all to flow.....

From your code, I have all 10 (5 A files and 5 B files) in a folder together. How would I have a macro call out to open only two at a time, two that match with corresponding file numbers? For example, I have 1A.csv, 1B.csv, 2A, 2B, 3A, 3B, 4A, 4B, 5A and 5B all in one data folder now. What kind of callout would I have to do to only have corresponding 1A and 1B open together? Mind you these numbers will be changing....

[JBeaucaire]

I think the "A" and "B" thing will still be the trick for you in a secondary loop. This macro would go in the workbook with your "template".

Open the template workbook
Open the VBeditor
Insert an empty module (insert > module)
Paste in this code:

Please Login or Register  to view this content.

Edit the sheet names in the code (underlined) for accuracy
Edit the copy values code in each With wbA and the With wbB sections...
Give it a try...

[holly66]

It isn't doing anything. Maybe I needed to be a bit more clear...

Once opened, I would like 1-A.csv data range A1 through F685 copy and pasted into Cell A1 Sheet "Voltage" of Template and 1-B.csv data range A1 through F685 copy and pasted into Cell A1 Sheet "Current" of Template.....they are being put into two different places...

[JBeaucaire]

Don't put this macro in the Template workbook, put it in another workbook and run it from there.

Be sure to edit the macro paths if they are not correct.

Try running the macro using F8 in the VBeditor so that you can check each line of code actually does what you expect, that it doesn't skip the main DO/LOOP altogether.

Please Login or Register  to view this content.