Filename strip date and timestamp only from some files
Hello Experts,
I have 20 some reports printed to PDF from an access database. Depending on the report and the PDFCreator printer used (there are 2 printers with different naming profiles), some of the filenames end with a date and timestamp like shown below. All have different 'front end' names. This is by design as half the reports are designed to be run by individual users in different Teams to shared network directories and we need this info to keep versions straight for those reports.
reportname1_20111021104242
report2 nodate.pdf
reportname3 diff length name__20111021104244.pdf
reportname4 different length name__20111021114400.pdf
report5 printfast__20111021114400.
rep6 nodate.pdf
report-7 nodate.pdf
However, I do some 'batch' report processing and need to strip off just the underscore and date/timestamp extension for only the reports in the directory that have it (i.e reports 1, 3, 4 & 5) in example above so I have a group of files in a directory that are always have the same filenames no matter when I run them. About half the files have date/timestamps the other half don't, some reports that print real quickly in sequence will have same date/timestamp (but different report names).
The other option seems to be to re-create duplicates of all the reports and point the duplicate to the PDFCreator printer that doesn't add a date/timestamp. Then create separate forms/buttons, etc. etc. Long term this does not appear to be the best solution.
I am thinking that a VBA or VBS routine just to strip the extra info off if it is there would be easiest but don't know how to code this. I can imbed the VBA in Access as part of the 'batch' reporting other or call a VBS script within as well, but am open to other ideas if you can tell me how to incorporate into a call from Access.
Please ask questions if unclear and THANKS in advance!
I have 20 some reports printed to PDF from an access database. Depending on the report and the PDFCreator printer used (there are 2 printers with different naming profiles), some of the filenames end with a date and timestamp like shown below. All have different 'front end' names. This is by design as half the reports are designed to be run by individual users in different Teams to shared network directories and we need this info to keep versions straight for those reports.
reportname1_20111021104242
report2 nodate.pdf
reportname3 diff length name__20111021104244.pdf
reportname4 different length name__20111021114400.pdf
report5 printfast__20111021114400.
rep6 nodate.pdf
report-7 nodate.pdf
However, I do some 'batch' report processing and need to strip off just the underscore and date/timestamp extension for only the reports in the directory that have it (i.e reports 1, 3, 4 & 5) in example above so I have a group of files in a directory that are always have the same filenames no matter when I run them. About half the files have date/timestamps the other half don't, some reports that print real quickly in sequence will have same date/timestamp (but different report names).
The other option seems to be to re-create duplicates of all the reports and point the duplicate to the PDFCreator printer that doesn't add a date/timestamp. Then create separate forms/buttons, etc. etc. Long term this does not appear to be the best solution.
I am thinking that a VBA or VBS routine just to strip the extra info off if it is there would be easiest but don't know how to code this. I can imbed the VBA in Access as part of the 'batch' reporting other or call a VBS script within as well, but am open to other ideas if you can tell me how to incorporate into a call from Access.
Please ask questions if unclear and THANKS in advance!
ASKER
yeah sorry its access 2003, forgot to inlcude that info so there is no native pdf capability. thaks tho'
txt = Dir("C:\MyFolder\*.pdf")
While Len(txt)
i = Instr(txt, "_")
If i Then
dat = Mid(txt, i + 1, Len(txt) - 3 - i) 'raw date/time string
Datim = Mid(dat, 5, 2) & "/" & Mid(dat, 7, 2) & "/" & Left(dat,4) & " " & Mid(dat, 9, 2) & ":" & Mid(dat, 11, 2) & ":" & Right(dat, 2) 'date/time formatted as mm/dd/yyyy hh:mm:ss
Rem *** Do your processing here ***
End If
txt = Dir
Wend
While Len(txt)
i = Instr(txt, "_")
If i Then
dat = Mid(txt, i + 1, Len(txt) - 3 - i) 'raw date/time string
Datim = Mid(dat, 5, 2) & "/" & Mid(dat, 7, 2) & "/" & Left(dat,4) & " " & Mid(dat, 9, 2) & ":" & Mid(dat, 11, 2) & ":" & Right(dat, 2) 'date/time formatted as mm/dd/yyyy hh:mm:ss
Rem *** Do your processing here ***
End If
txt = Dir
Wend
Oh, never mind. You said to strip off the date/time. My bad. Use this:
txt = Dir("C:\MyFolder\*.pdf")
While Len(txt)
i = Instr(txt, "_")
If i Then
NewName = Left(txt, i - 1) & Right(txt, 4)
Name "C:\MyFolder\" & txt As NewName
End If
txt = Dir
Wend
txt = Dir("C:\MyFolder\*.pdf")
While Len(txt)
i = Instr(txt, "_")
If i Then
NewName = Left(txt, i - 1) & Right(txt, 4)
Name "C:\MyFolder\" & txt As NewName
End If
txt = Dir
Wend
ASKER
VB Classic Guy,
I'm game but need some help, is this a VB Script (the Dir command makes me think not)? or do i need to do something else? sorry to be dense
I'm game but need some help, is this a VB Script (the Dir command makes me think not)? or do i need to do something else? sorry to be dense
Have you tried: http://www.lebans.com/reporttopdf.htm
Although Steve has stopped developing for Access, he has done some really outstanding stuff for the Access community. If you haven't already done so, you should scroll through the entire site. There is some really neat stuff there.
Although Steve has stopped developing for Access, he has done some really outstanding stuff for the Access community. If you haven't already done so, you should scroll through the entire site. There is some really neat stuff there.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's VB6, but should run as a script with little or no modification.
ASKER
ok thanks. made a .vbs file off first post and got error below which is why i asked:
line: 1
char: 1
Error: Type mismatch: 'Dir'
BUT saw your second post and it runs to line 6 where I get :
Line:6
Char: 79
Error: expected end of statement
Can't figure out if comma, = sign are missing, don't know this syntax???
line 6 below
what am i doing wrong???
line: 1
char: 1
Error: Type mismatch: 'Dir'
BUT saw your second post and it runs to line 6 where I get :
Line:6
Char: 79
Error: expected end of statement
Can't figure out if comma, = sign are missing, don't know this syntax???
line 6 below
what am i doing wrong???
Name "\\MAINtwo003\Data\NYC\Budget\FY 2012\Monthly Report\Batch\" & txt As NewNameASKER
Waited a little bit to try and give VBClassic guy a chance to answer my open question. Would have considered a split points award if he did to be fair.
ALS this worked like a charm. Thank you, problem solved!
Thanks all who particpated and Fyed yes I've seen the Lebans site...found it after he retired and seriously considered going with his pdf solution before. Went he way we did for a bunch of reasons. Thanks!
ALS this worked like a charm. Thank you, problem solved!
Thanks all who particpated and Fyed yes I've seen the Lebans site...found it after he retired and seriously considered going with his pdf solution before. Went he way we did for a bunch of reasons. Thanks!
Oh yeah. For VBS, you might have to use:
Set fso = CreateObject("Scripting.Fi
fso.MoveFile "C:\MyFolder\" & txt , "C:\MyFolder\" & "C:\MyFolder\" & NewName
Set fso = Nothing 'clean up after ourselves
(place the "Set fso..blah blah" statement at the top of the routine.
Set fso = CreateObject("Scripting.Fi
fso.MoveFile "C:\MyFolder\" & txt , "C:\MyFolder\" & "C:\MyFolder\" & NewName
Set fso = Nothing 'clean up after ourselves
(place the "Set fso..blah blah" statement at the top of the routine.
Opps. Day late and a dollar short. That's how the cookie crumbles...
Good solution, als315. Congrats!
Good solution, als315. Congrats!
instead of opening the report in viewNormal mode, which prints it you just use the OutputTo method to save the report:
docmd.OutputTo acOutputReport, "reportname", acformatpdf, filename
the acformatPDF option may not be available with versions of Access prior to 2007, and I think may only be available in 2007 if you download the "SaveAsPDF" Access add-in. But there are other options for saving a report to a file, rather than "printing" the report to a file.