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.

 

[94mustang]

The script would need to run when the original file is saved. Operators will be overwriting the file each time. The script would need to recognize that the original file has been overwritten/saved and then run. You mentioned about the file size. It is small (1.12 KB). The range in the original file is from A1 to approx. E15 but the first three rows again is text about the sample.

 

[vaskov17]

How often would the operators change the original file? Also every time the script runs, what should happen to the previous output file?

 

[94mustang]

The original file will be updated approximately every 3 to 5 minutes. The operators are performing laboratory testing and the testing time varies so it is not an exact time each time. The previous output file needs to stay like it is until the operators perform his/her next save to the original file. Having the output file to stay until the next save will allow me to use this file to pull in the data to another software system.

 

[vaskov17]

I'll come up with something and post it here later today

 

[vaskov17]

Put the following code into a text file and change it's extension to VBS then double click it to run it.

To stop the script, use CTRL+SHIFT+ESC to access task manager and end the wscript.exe process.

The script will check the first file every 30 seconds and if it has been updated it should make a new file. In the same location a log.txt file should be created that shows when the script checks or updates the files.

Test it out with different in and out files and let me know if it works.

Dim objFSO, objOldFile, objNewFile, toSkip, i, strLine, strOut, strIn, bProcess, strLog, objLog, numTime

strIn="M:\1.is_tens_Results.csv"
strOut="M:\TapeInstronResults.csv"
strLog="M:\log.txt"
numTime=2

Set objFSO=CreateObject("Scripting.FileSystemObject")

'create the log.txt file if it doesn't exist
If Not objFSO.FileExists(strLog) Then
	objFSO.CreateTextFile strLog
End If

Set objLog=objFSO.OpenTextFile(strLog,8)

'number of lines to skip at the beginning
toSkip=3

Do
	bProcess=False
	'check if file was modified more than 2 minutes ago
	If (Now()- objFSO.GetFile(strIn).DateLastModified)*60*24>numTime Then
		If Not objFSO.FileExists(strOut) Then
			bProcess=True
		ElseIf objFSO.FileExists(strOut) Then
			If objFSO.GetFile(strOut).DateLastModified<objFSO.GetFile(strIn).DateLastModified Then
				bProcess=True
			Else
				bProcess=False
			End If
		End If
		
		If bProcess Then
			'open input file
			Set objOldFile=objFSO.OpenTextFile(strIn,1)
			
			'delete output file if it exists
			If objFSO.FileExists(strOut) Then
				objFSO.DeleteFile(strOut)
			End If
	
			'create new output file and open it
			objFSO.CreateTextFile strOut,True
			Set objNewFile=objFSO.OpenTextFile(strOut,8)
			
			i=0
			
			Do Until objOldFile.AtEndOfStream
				'line counter
				i=i+1
				
				'read line
				strLine=objOldFile.ReadLine
					
				If i>toSkip Then
					objNewFile.WriteLine strLine
				End If
			Loop
			objNewFile.Close
			objOldFile.Close
			objLog.WriteLine "Update: " & Now()
		Else
			objLog.WriteLine "Check: " & Now()
			Call Wait
		End If
	Else 
		Call Wait
	End If
Loop

Sub Wait()
	WScript.Sleep 30000 'wait for 30 seconds
End Sub

 

[94mustang]

The script would need to run when it recognizes the original file has been updated/saved. The result file will need to contain the same values as the original until there is another update/save to that file. I hope this info. helps.

 

[94mustang]

Ignore my last response. I will try this script and let you know. What do I need to change to check the original every 15 to 20 seconds?

 

[94mustang]

What language is this? Since I am so new to forums, how can I post an image like you did in your last response? This is what I get when running the script:

Windows Script Host Dialog box

Script: \\Sw72infsql\spc plant files\Plant SP\Tape Lab\Tape Instron Data\DataFromInstron.VBS
Line: 27
Char: 46
Error: Expected 'Then'
Code: 800A03F9
Source: Microsoft VBScript compilation error

 

[vaskov17]

1. Language is VBScript
2. to post code like I did, put the code you want between code tags - "CODE" and "/CODE" just substitute the quotes with square brackets [ and ]
3. Are you running the script from your computer or is it on a network drive? Try mapping the folder with the input and output files and running the script from your desktop or another folder on your PC.

 

[94mustang]

I was running the script on a server where the files will exist. I will try it on my computer. I will take the exact names of the files and transfer them to my computer and run the script and let you know what happens.

I very much appreciate your perseverance and determination in helping me resolve this issue.