Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I recently downloaded MS Office Add-in Save as PDF. I would like to Save-As a PDF within a macro. Here's the macro I have recorded:

    Sub Save_to_PDF()
    '
    ' Save_to_PDF Macro
    '

    '
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "DriveLetter:\Folder\SubFolder\filename_2010-09-16.pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
    End Sub

    The Add-in uses the Excel filename but during the conversion it changes the extension from .xlsm to .pdf.
    How do I change the macro to save the filename with the new filename (tomorrow's file will be renamed filename_2010-09-17.xlsm)?

    Also, If I have to re-run a day ( perhaps to update some previously unrecorded entry) how can the macro be modified to overwrite without prompting?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I've not used it, but if I interpret what you want correctly it ought to be a bit like this

    Code:
    Sub Save_to_PDF()
    '
    ' Save_to_PDF Macro
    '
    Dim strFileName as String, strPath as String, strPDFName as String
    
    strFileName=ActiveWorkBook.Name
    strPath="DriveLetter:\Folder\SubFolder\"    'Put Your Path Here or Use       strPath=ActiveWorkBook.Path & "\"
    strPDFName=Left(strFileName,Len(strFileName)-4) & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat _
       Type:=xlTypePDF, _
       Filename:= strPath & strPDFName, _
       Quality:= xlQualityStandard, _
       IncludeDocProperties:=True, _
       IgnorePrintAreas:=False, _
       OpenAfterPublish:=False
    
    MsgBox "Saved To PDF"
    
    End Sub
    Andrew

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Aaarrrggghhh! This is why I'll always be a humble lounger! Read post, created code, came back to post and found someone quicker and smarter had got there before me - as always. Still, it's good to know that there are eminent loungers out there who are also quick off the mark when I post my queries!

    Might as well post my code anyway - it was slightly different, based on the possiblility that (a) you wanted to put the .pdf file in the same folder as the .xlsm file, and (b) you might occasionally want to start with a file with a 3-character extension:

    Dim strXLName As String, strPDFName As String, intPos As Integer

    strXLName = ActiveWorkbook.FullName
    intPos = InStr(1, strXLName, ".")
    strPDFName = Left(strXLName, intPos) & "pdf"

    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strPDFName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False


    The ExportAsFixedFormat seems to overwrite any existing file without prompting, which easily answers your second question - if you wanted it to prompt, I guess you'd have to check for the old file's existence and decide what to do with it before exporting the current file.

    Cheers
    Alison

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Many thanks to you both. Each solution works just as I need.

    Andrew, in the part where the filename is assigned to the string strPDFName
    (strPDFName=Left(strFileName,Len(strFileName)-4) & ".pdf")
    the dot in ".pdf" creates an extra dot in the filename. Despite that the pdf opened just fine. I removed the extra dot and it runs great.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    No worries!
    Code was still good, and it allows for xls and xlsx etc!
    Although you might be better using InstrRev to search for the . from the Right side
    It still returns the position counting from the left, but starts search on the right,
    because now file names can have . in the file name
    e.g. MyFile.1.1.1.xls

    So here, Instr looking for a . would return the value 7
    But InstrRev would return 13

    To check if file exists

    Easiest is probably to use Dir Function

    IF Dir(Path & File) = "" then 'It Does Not exist

    The Update below uses this with a Message Box Function to give the user a choice of buttons to press

    Code:
    Sub Save_to_PDF()
    '
    ' Save_to_PDF Macro
    '
    Dim strFileName As String, strPath As String, strPDFName As String
    Dim intReply As Integer
    
    strFileName = ActiveWorkbook.Name
    strPath = ActiveWorkbook.Path & "\"
    strPDFName = Left(strFileName, Len(strFileName) - 5) & ".pdf"
    
    'Check for File
    If Dir(strPath & strPDFName) <> "" Then
      intReply = MsgBox("A File named  " & strPDFName & vbLf & "Already exists in Folder" & vbLf & strPath _
               & "Do you want to Replace it?", vbQuestion + vbYesNo + vbDefaultButton1, "File Exists")
      If intReply = vbYes Then
         ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=strPath & strPDFName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    
            MsgBox "Saved To PDF " & strPDFName
      End If
    End If
    
    End Sub
    Arcturus16a.

    The reason for the extra . was I suspect because you are using an .xlsx not an .xls so it needs to crop the 5 characters (see above)
    That is why alifrog solution (see above as well) is good because it looks for the .
    Andrew

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Wow! That is slick. Thanks again Andrew.

    The message boxes are a nice touch and will be a great benefit to other users when I pass the workbook on.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Andrew - Thanks for the heads up on InstrRev, which I have completely missed in the time I've been using VBA! Had I thought of the fact that there might be a . in the middle of the file name (I must say, I preferred it when . could only be used before the extension), I would have done a very boring loop to find the final . but InstrRev is much neater.

    See, I always learn something in this forum!

    Cheers

    Alison

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    One incidental thing: if you have SP2 installed, the export to PDF functionality is built-in. If you don't have SP2, go and get it now!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •