Many files in one folder issues. Convert file extention, move it and process it.

[Sunnyboy]

Hello every one,

 

My macro needs to do 3 things.

 

1. I need to open an excel CSV file in a folder.

2. I need to change the extention CSV to TXT (tab seperated value in excel).

3. I need to move the new .TXT file to an other location.

4. I need to run an existing macro with this file.

 

Here are my issues with each steps.

 

1. My macro will be run on a scheduler only week days. However, the CSV files that I need to process are generated in this folder every days. That means when my macro will run on mondays i will have 3 files to process (fri, sat, sun). Here is the format for the files generated in the folder:

 

FILENAME_YYMMDD.CSV

 

In the Launch program command, I can't use the "*" to complete the file name I need to open. I need to write the complete path name and I can't use a variable with this command. So how can I do this? I need to process all 3 files, one after another (the oldest file 1st).

 

2. Is there a faster, easier and safe way to convert my Excel .CSV file into .TXT (tab separated value) instead of opening the file in Excel and Save as command?

 

3. I have to move my .TXT file, to an other folder. I guess I use the Move file command and here i can use "*" in the Program Path/Name field. I have no issue with this step.

 

4. When I run my other macro, with the new TXT file, I am ask for a file name. Can't use the "*" here again. So how would I do that? Should I rename my file to process with something standard such as: FILETOBEPROCESS.TXT and delete it at the end of each process job?

 

Thanks so much for your help. I've been looking over the forum for quite some time now to find topics that matches my needs.

 

Thanks again.

[Yehnfikm8Gq]

1. If the new files are the only ones in the folder you can Repeat with Folder to process each file (return the full file path in the variable). If there are other files not needing to be processed you could have some If conditions based on Creation Date (ie within 3 days). Alternatively keep a list of old files in a text file and find any new additions by comparing new filenames with list.

 

I don't know why you can't use a variable, Text Type into dialog if that is the problem. You can use variables in Launch Program. Put variable in quotes in case it has any spaces in the path.

 

If you have to process the older file first, use Creation Date which also happens to be in the filename (it's not easy to evaluate which is older especially across year and month boundaries). At the moment I can't think how you would tie that in with Repeat with Folder.

 

2. There's nothing particularly bad or unreliable about working on files opened in Excel. Allow enough time delays and "Wait for" commands for it to be reliable. Three files are not a big deal. You may be able to convert with a macro within Excel, avoiding ME completely. I can't help there.

 

3. If you are running the "other" macro at the same time, call the macro (Macro Run) in the Repeat with Folder loop. It will process the current file whose name will be known (as part of the Repeat with Folder/File renaming). If running the macro later, you may be able to process per 1. above.

[Yehnfikm8Gq]

I took an xls spreadsheet and converted to File1.csv using Save As within Excel. I also converted to a tab delimited file File1.txt using Save As within Excel. I then took the .csv file and used the following commands:

Variable Set String set %T1% to the contents of D:\Temp\File1.csv
Variable Set to ASCII Char 9 to %T9%
Variable Modify String: Replace "," in %T1% with "%T9%"
Variable Modify String: Save %T1% to "D:\Temp\File2.txt"

I compared the differences between File1.txt and File2.txt. There were some minor differences which were related to cells already containing commas (additional to commas separating variables). Where Excel put data with commas in quotes, converting the csv with the above commands kept the quotes but replaced those commas with tabs. That could be handled but I don't have any code to hand.

 

You could try the code on your files and see what you get. I used the utility Exam Diff to make the text file comparison.

[Sunnyboy]

Hello John!

 

Thank you so much for your help. I didn't know we could use variable in every command. With the information provided I was able to complete my macro job.

 

Thanks again and i's really a good thing that you guys (the pros) are there to give us a hand when were stuck in our macros.