Problem Saving to File Name
Results 1 to 2 of 2

Thread: Problem Saving to File Name
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Problem Saving to File Name

    Hi All,

    I wrote a macro that will loop through all open workbooks and save each and print each to pdf. It saves fine and it prints to PDF fine but it makes me enter the file name of the pdf manually. I would like it to use the filename for each workbook as the filename for each pdf file printed. Any thoughts would be welcomed.

    Code:
    Sub SB()
    Dim singlebook As Workbook
    Dim filename As String
    Dim xlWrkSht As Worksheet
    Dim fil As Scripting.File
    
    For Each singlebook In Workbooks
    singlebook.Activate
    'filename = ActiveWorkbook.Name
    singlebook.SaveAs
    With ActiveWorkbook
    
    ActiveWorkbook.PrintOut ActivePrinter:="microsoft print to pdf", prtofilename:=True
    
    End With
    Next singlebook
    
    
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,287
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Problem Saving to File Name

    The singlebook.SaveAs simply saves the workbook as an Excel file. You need to export it to pdf. try something like this after you substitute the correct file extension for your excel workbooks for the one in red font (.xlsm)
    Code:
    For Each singlebook In Workbooks
        With singlebook
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            Replace(.Name, ".xlsm", ".pdf"), _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
        End With
    Next singlebook
    Last edited by JoeMo; Jun 23rd, 2019 at 03:53 PM.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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