Automatically Running Macros

[94mustang]

I am using Excel 2007 with Windows XP. Is it possible to run an Excel Macro even when Excel is not open? This issue is where I need help. I have a piece of Lab equipment that creates a CSV file and is saved approximately every 3 to 5 minutes. It is actually saved each time the operator completes his/her test so the time is very critical. The format of the CSV file is unacceptable to automate data collection into another software system because it has text in the first three rows. I am looking to see if an Excel Macro will look at this CSV file when it is saved and create another CSV file with the removal of the first three rows so this file can be used for importing into the other software system. I do have the book VBA and Macros for Microsoft Office Excel 2007 by Bill "MrExcel" Jelen and Tracy Syrstad. On page 340, it mentions the topic, "Read Entire CSV to memory and Parse" but not sure if this is the route to go. I have just started looking and wanted to turn to an expert to get some feedback on how this might be accomplished. Again, the CSV file will always be closed as well as the workbook with the Macro. Any advice and help would be greatly appreciated. Thanks.

 

[Smitty]

Welcome to the Board!

You can't run a macro when Excel is closed, but you can tell Windows to open the workbook. From there you can use the Workbook_Open event to fire your code, then close the workbook.

Goto Start-->All Programs-->Accessories-->System Tools-->Task Scheduler.

HTH,

 

[vaskov17]

you can do a script which deletes the first three lines of the csv file when excel is not running

 

[vaskov17]

put the following line of text into a text file and save it with CMD or BAT extension:

more +3 "c:\file1.csv" >> "c:\file2.csv"

c:\file1.csv is your input file
c:\file2.csv is your output without the first three lines

change the two to whatever you need, but make sure you have permission to create and delete files in those folders

 

[94mustang]

I tried to access the schedule tasks on the server and I do not have rights to schedule them. I get access denied. However, I did run the script using the CMD file type and the first time it worked beautifully. I had to click on the CMD file for the script to run. I changed some values in the original file but the second file did not update on the second try. I do have access to create and delete files in the directory. What needs to be done so the script runs the second, third, or nth time to update the file without user interface? Here is what is in the CMD file:

'\\Sw72infsql\spc plant files\Plant SP\Tape Lab\Tape Instron Data'
CMD.EXE was started with the above path as the current directory.
UNC paths are not supported. Defaulting to Windows directory.

M:\Windows>more +3 "\\Sw72infsql\spc plant files\Plant SP\Tape Lab\Tape Instron Data\1.is_tens_Results.csv" 1>>"\\Sw72infsql\spc plant files\Plant SP\Tape Lab\Tape Instron Data\TapeInstronResults.csv"

 

[vaskov17]

can you try mapping the "\\Sw72infsql\spc plant files\Plant SP\Tape Lab\Tape Instron Data\" folder and then do:

more +3 "Z:\1.is tens Results.csv" >> "Z:\TapeInstronResults.csv"

 

[94mustang]

I was able to map to the drive and was able to remove the lengthy path directory. It did perform updates. It is appending new data on the end of the new file. I have gone into the original file, made changes, saved, closed it and then immediately open the file that does not have the first three rows (text) and the changes are not there. How often or how quickly is the data from the original file suppose to transfer to the new file without the first three rows? Is there anything I am doing? Is there additional code that needs to be added. Here is an example of the original file:

A B
1 Text1 Text1A
2 Text2 Text2A
3 Text3 Text3A
4 Value1 Value2
5 Value3 Value4

The new file has the following:

A B Comments
1 Value1 Value2
2 Value3 Value4
3 Value1 Value2 New value from same file
4 Value3 Value4 New value from same file

This is what is in the CMD file:
more +3 "M:\1.is_tens_Results.csv" >> "M:\TapeInstronResults.csv"

 

[vaskov17]

the ">>" operator forces the more command to append to "TapeInstronResults.csv" so it never gets rid of previous data. Put the following in your cmd file:

del "M:\TapeInstronResults.csv"
more +3 "M:\1.is_tens_Results.csv" >> "M:\TapeInstronResults.csv"

this should first delete the old output file and then create a new one.

speed depends on the size of your file, but it shouldn't take more than a few seconds

 

[94mustang]

How does it run automatically? I have edited the CMD file as stated. I manually deleted the results file to completely remove it and then added the additional command in the CMD file and saved it. I changed values in the original file and saved changes. I waited about 20 seconds and then double-clicked on the CMD file but nothing happened. Your help has been very valuable and think we are almost there. What else could we try?

 

[vaskov17]

This doesn't run automatically, it just deletes the first three lines of your current file and creates a new one. Under what conditions would the script run?