ExportAsFixedFormat 1

[mar050703]

Hi Guys

Sorry to bother you all, I have a little problem with some code I am working on.

I have an .xlsm file which I ExportAsFixedFormat to pdf, using the following code (this is only test version):

Worksheets("Sheet2").Select
            ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, Filename:="C:\Test1.pdf"

However when I need to re-export the version it just overwrites the existing, but I would like to keep the original, therefore is there a way to check if the file exists first and if so to simply increase the number to the next number on, (therefore if I am up to Test3, then it would go to Test 4). I guess some kind of loop until.

Thanks for your input. I am trying to expand my use and knowledge of VBA, so please excuse my in-experience.

 

[strongm]

If you are happy to go with a

[tt]xxxxxx .xxx[/tt]

format (as used by the Windows shell when it does this sort of thing) instead of

[tt]xxxxxxn.xxx[/tt]

then there is a built-in API function that'll do it.

[blue]Option Explicit

Private Declare Function PathMakeUniqueName Lib "shell32.dll" (ByVal pszUniqueName As String, ByVal cchMax As Long, ByVal pszTemplate As String, ByVal pszLongPlate As String, ByVal pszDir As String) As Boolean
Const Max_Path As String = 260

[green]' strRootFilename can include path rather than usingthe seperate strPath parameter
' strRootFileName is used to generate sequential filename. strRootFileName itself does not need to contain include a sequence number[/green]
Public Function vbGetNextFileName(strRootFileName As String, Optional strPath As String = "") As String
    Dim strresult As String * Max_Path
    
    PathMakeUniqueName strresult, Max_Path, "", StrConv(strRootFileName, vbUnicode), StrConv(strPath, vbUnicode)
    vbGetNextFileName = StrConv(strresult, vbFromUnicode)
End Function

Private Sub Command2_Click()
    Dim FileName As String
    
    FileName = vbGetNextFileName("C:\test.txt")
    MsgBox result
    [green]' Uncomment lines below for simple demo of sequential capability
    'Open FileName For Output As #1
    'Close #1[/green]
End Sub[/blue]

 

[mar050703]

Thank you for your suggestion.

If I read your code correctly it appears to save the file as ".txt" and not ".pdf"
However I have slightly been able to develop my original code to

Sub SavePDF()

Static count As Integer
Dim filestr As String
filestr = "C:\Test"

Worksheets("Sheet2").Select

If count = 0 Then
    ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, filename:=filestr '& "v" & count
        ElseIf count > 0 And Dir(filestr & "v" & count) = "" Then
        ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, filename:=filestr & "v" & count
        End If
        
    count = count + 1
    
    
End Sub

Which is ok except that if I close the file (with or without saving any changes in the original .xlsm file it does not create a v1, it just over-writes the original "C;\test.pdf" file That is if I don't close the .xlsm file, it will increase the version to v1 or v2 etc.

I cant quite figure out why, can someone help?

Thanks

 

[strongm]

Mine was simply an example of how to reliably generate a sequential filename, it wasn't an exact solution (you may have noticed, for example, that it doesn't actually include any use of ActiveSheet.ExportAsFixedFormat). You can pass any filename template you like to the function. e.g

[tt]FileName = vbGetNextFileName("C:\test.pdf")[/tt]

would work fine.

 

[mar050703]

Sorry for appearing to not understand.

Using my second code is there a reason, for which I can not see, why the code is only increasing on version number when I don't close the file.

If that makes sense?

 

[Andrzejek]

As long as you have (Excel with the code) file open, your [tt]count [/tt] keeps increasing.
As soon as you close it and re-open, the [tt]count [/tt]goes back to 0 and you increase it again to 1, 2, 3, ...

You may want to check if there are any files in "C:\Test*.pdf" already and detect which one is the 'last' one.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mar050703]

Thanks Andy, that explains the problem,

Can you suggest a solution please?

I have tried

If Dir(filestr & "v" & count) = "" Then
    ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, filename:=filestr
    Else
    ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, filename:=filestr & "v" & count
count = count + 1

But I get the same result.

 

[strongm]

My code deals with that problem without any change

 

[Andrzejek]

You may try something like this:

[blue]Dim[/blue] count As Integer
Dim filestr As String[blue]
Dim strF as String[/blue]
filestr = "C:\Test"

Worksheets("Sheet2").Select

If Dir(filestr & "v" & count) = "" Then
    ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, filename:=filestr
Else[blue]
    strF = Dir(filestr & "v*")
    Do Until strF = "" 
	count = count + 1 
        strF = Dir
    loop[/blue]
    ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, filename:=filestr & "v" & count
End if

Code not tested.

 

[mar050703]

Thanks for the code.

Unfortunately, I am still only getting "test.pdf", therefore if the is a "Test.pdf" I am still unable to get "Testv1.pdf".

When testing, I am closing the file before attempting a "Testv1.pdf".

Any further ideas would be appreciated.

Thanks

 

[HughLerwill]

>then there is a built-in API function that'll do it.
Thanks for vbGetNextFileName ...

 

[Andrzejek]

OK, here is a little 'proof of concept'

Dim count As Integer
Dim filestr As String
Dim bln As Boolean

filestr = "C:\Test"

Worksheets("Sheet2").Select

Do While Not bln
    If Dir(UCase(filestr & "v" & count & ".pdf")) = "" Then
        ActiveSheet.ExportAsFixedFormat Type:=xltypepdf, filename:= filestr & "v" & count 
        bln = True
    End If
    count = count + 1
Loop

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

I'll have one more go ...

You could replace the entire If (or Do) statement(s) with

[blue]Worksheets("Sheet2").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=vbGetNextFileName("C:\Test.pdf")[/blue]

In other words, one minor change to the code you originally posted

 

[mar050703]

Thanks Andrzejek,

Your code works perfectly in my simple test version.

Unfortunately in my live version (with a little more coding etc) again appears to overwrite the file

My Live code is:

Dim Fnme As String
Dim Fpath As String
Dim filestr As String 
Dim count As Integer 
Dim bln As Boolean 

    'check I want to save as Draft
Response = MsgBox("Do you want to Save to Draft?", vbYesNo, Save)
Select Case Response
    Case vbYes

        Worksheets("Parameters").Select
        Fnme = ActiveSheet.[A2]
        Fpath = ActiveSheet.[E19]
        filestr = Fpath & "\" & Fnme 
        count = 1 
        
        Worksheets("Profit & Loss").Select
        Do While Not bln 
            If Dir(UCase(filestr & "Draft TEST" & "v" & count & ".pdf")) = "" Then  
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filestr & " Draft TEST" & "v" & count 
                bln = True 
            End If 
            count = count + 1
        Loop 
    Case vbNo
        MsgBox "This Document has NOT been saved", vbExclamation, "Warning"

End Select

The only change I made was to casue count to = 1, and so start as Draft TESTv1
please note Fpath is the looking at the cell where the file path is
and Fnme is the File name (so will be mmmyy (Oct15)).

Each new Financial Year the cells will work out the new path and folder (15-16, or 16-17 etc), and so automatically save to that folder.

Am I missing something, as I am unable to see it at present.

Thanks again

 

[Andrzejek]

I would put some break points in the code, step thru it checking the values of your variables.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mar050703]

Thank you for your suggestion.

The code is not bringing any errors, it just overwrites the file it creates, (I can see that from the time stamp in windows explorer.

It is as if the loop is not working, but am unable to figure out why.

The first instance is naming the file correctly in the correct place, ie H:\Test\15-16\DraftTestv1.pdf, but when I want v2, it just overwrites v1 again.

Thanks

 

[strongm]

Tum ti tum ...

 

[mar050703]

No worries, me being silly

 If Dir(UCase(filestr & "Draft TEST" & "v" & count & ".pdf")) = "" Then  
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filestr & " Draft TEST" & "v" & count

notice the "SPACE" before the word "DRAFT" on the 2nd line, and not on the 1st. Subtle.

Thank you all for your help. I can sleep easy now.