• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

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.pdf
report2 nodate.pdf
reportname3 diff length name__20111021104244.pdf
reportname4 different length name__20111021114400.pdf
report5 printfast__20111021114400.pdf
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!
0
matttclark
Asked:
matttclark
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Dale FyeCommented:
instead of printing the pdf to a file, have you considered using the OutputTo method.

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.
0
 
matttclarkAuthor Commented:
yeah sorry its access 2003, forgot to inlcude that info so there is no native pdf capability. thaks tho'
0
 
VBClassicGuyCommented:
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
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
VBClassicGuyCommented:
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
0
 
matttclarkAuthor Commented:
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
0
 
Dale FyeCommented:
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.
0
 
als315Commented:
You can call thi sub for renaming files in your folder:
 
Sub Files_Rename()
Dim A, B As String
Dim Path As String
Dim Cmd As String
Dim RetVAl
Path = "c:\tmp\"
A = Dir("c:\tmp\*.pdf")
While A <> ""
    If InStr(1, A, "_") > 0 Then
      B = Mid(A, 1, InStr(1, A, "_") - 1) & Mid(A, InStrRev(A, "."))
      Name Path & A As Path & B
    End If
    A = Dir()
Wend
End Sub

Open in new window

0
 
VBClassicGuyCommented:
It's VB6, but should run as a script with little or no modification.
0
 
matttclarkAuthor Commented:
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???


Name "\\MAINtwo003\Data\NYC\Budget\FY 2012\Monthly Report\Batch\" & txt As NewName

Open in new window

0
 
matttclarkAuthor Commented:
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!
0
 
VBClassicGuyCommented:
Oh yeah. For VBS, you might have to use:

Set fso = CreateObject("Scripting.FileSystemObject")

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.
0
 
VBClassicGuyCommented:
Opps. Day late and a dollar short. That's how the cookie crumbles...

Good solution, als315. Congrats!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now