VBA to save file with version number

[kgkev]

I have a macro that is saving as PDF.

I use a line of code to generate the filepath

filepath = "\\serv\company\shipping\invoices\" & FileName & ".pdf"

However I would like to then check if this file already exists and rename it to (1) or (2) .. etc


Any suggestions

 

[doofusboy]

Just check for the existance of "\\serv\company\shipping\invoices\" & FileName & ".pdf" BEFORE saving and, if it exists, add a (1) or (2) to 'Filename'.

 

[kgkev]

I know how to open the file but not how to just check its there - Seems a bit long winded to attempt to open the file.

 

[RSXchin]

deleted by user

 

[VoG]

Maybe like this

i = 0
Do While Dir("\\serv\company\shipping\invoices\" & Filename & ".pdf") <> ""
    i = i + 1
    Filename = Filename & " (" & i & ")"
Loop

 

[klarowe]

A little off topic, but can you post or send the code you use to save the excel workbook as a PDF? Thanks

 

[kgkev]

pretty bad practice I'm afraid.

Until I get a few PC's upgraded we won't be running office 2007 / 2010 which has it built in,.

Currently use cutepdf as the pdf writer and use a sendkeys command to handle the cutepdf interface.

    Newhour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 5
    FileName = Sheets("Data").Range("A3").Value
    FileName = FileName & " - " & Sheets("Calcs").Range("B14").Value
    FileName = FileName & " - " & Sheets("Calcs").Range("B18").Value
    FileName = Replace$(FileName, Mid$("/", 1, 1), "-")
    
        
    n = 0
    Do While Dir("\\serv\company\shipping\invoices\" & FileName & ".pdf") <> ""
        n = n + 1
        FileName = FileName & " -" & n
    Loop
    filepath = "\\serv\company\shipping\invoices\" & FileName & ".pdf"
    Sheets("Invoice edit").PrintOut Copies:=1, ActivePrinter:= _
    "CutePDF Writer on CPW2:", Collate:=True
 
    waittime = TimeSerial(Newhour, newMinute, newSecond)
    Application.Wait waittime

    SendKeys filepath & "{ENTER}", False

The 5 second delay on the waittime isn't really necissary but we have some pretty slow PC's and this guarantees the dialog box is definatly up.



Can't claim any credit for this code - Found after a bit of digging on this forum.