It just occurred to me that you said this...
"What I mean by "schedule task" is using a batch
type file that I can put into "scheduled tasks" in
windows to run the script every morning at a
pre-determined time."
Does that mean you are **not** looking for an Excel macro to run but, rather, a VBScript program instead? If so, the code I posted may or may not help you. I've never programmed in VBScript so I don't know how it differs from VB (compiled) or VBA (for example, is the FileDateTime function available there). I'll give you the code anyway just in case it will work, but I do not know for sure it it will run in the VBScript environment or not.
As written, the code will search through the directory set up in the Path constant (change my "c:\temp\" to whatever directory has your files in them and note the requirement for the trailing backslash) and process the csv file with the latest modified date. If you have other csv files in that directory, then you will have to modify this line...
Filename = Dir(Path & "*.csv")
by adding any fixed filename text to the asterisk. For example, if your reports have names like "Monthly Report for 10-16-2008.csv", then you would change the above line to this...
Filename = Dir(Path & "Monthly Report for *.csv")
in order to filter down to the filenames you are interested in. If any errors occur, the code ends without doing anything. Okay, here is the code...
Sub ReplacePDFwithTIF()
Dim FileNum As Long
Dim FileDate As Date
Dim Filename As String
Dim TotalFile As String
Dim PathAndFileName As String
Const Path As String = "c:\temp\" ' <<= note trailing backslash
On Error GoTo CloseSubroutine
Filename = Dir(Path & "*.csv")
PathAndFileName = Filename
FileDate = FileDateTime(Path & Filename)
Do While Len(Filename) > 0
If FileDateTime(Path & Filename) > FileDate Then
PathAndFileName = Path & Filename
End If
Filename = Dir
Loop
If Len(PathAndFileName) = 0 Then Exit Sub
FileNum = FreeFile
Open PathAndFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
TotalFile = Replace(TotalFile, "pdf", "tif", , , vbTextCompare)
FileNum = FreeFile
Open PathAndFileName For Output As #FileNum
Print #FileNum, TotalFile
Close #FileNum
CloseSubroutine:
End Sub
--
Rick (MVP - Excel)
1) yes only one file / day that will be deleted when the import is done
2) they will change with a date.csv
3) the files will always be dumped to the same directory
Thanks,
James B.
Okay, but in order to modify the code for what you are indicating, I need some more information. Is there only one file or more than one? Either way, are the filenames fixed or will they change (with the date, for example)? If more than one file, and if the names change, then are the file always going to be located in a specific directory?
--
Rick (MVP - Excel)
Rick,
Thanks for the information. What I mean by "schedule task" is using a batch type file that I can put into "scheduled tasks" in windows to run the script every morning at a pre-determined time. I would like for this to be as automated as possible.
Thanks,
James B.
I'm not sure what you mean by "schedule a task" since you have indicated you want to apply the requested functionality to "exported reports" whose names will vary over time, I am guessing, which would make scheduling somewhat problematic. Anyway, let's start here and we can modify it to meet your actual conditions if necessary. The following macro will prompt the user for a single file and then automatically replace all instances of pdf with tif within that file...
Sub ReplacePDFwithTIF()
Dim FileNum As Long
Dim TotalFile As String
Dim PathAndFileName As String
PathAndFileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
FileNum = FreeFile
Open PathAndFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
TotalFile = Replace(TotalFile, "pdf", "tif", , , vbTextCompare)
FileNum = FreeFile
Open PathAndFileName For Output As #FileNum
Print #FileNum, TotalFile
Close #FileNum
End Sub
Note that there is a practical limit on the size of the file... it should work efficiently enough with files up to, say, 15 to 20 Meg and slow down noticeably thereafter.
--
Rick (MVP - Excel)
I am looking for a way to schedule a task that will open a csv file and replace any instance of "pdf" with "tif". We are converting exported reports from a system as .pdf and converting them to .tif to be imported into a second system. The issue arrises that the csv file refrers to the pdf documents. I need to change all of the references from pdf to tif in the csv file and save the changes made. Any help or direction would be appreciated.
Thanks,
James B.
