Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Help with Save AS PDF

  1. #1
    New Member
    Join Date
    Aug 2013
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Question Help with Save AS PDF

    I have modified the following code to change the value of a specific field and auto increment an invoice number for all the files in a user selected folder. The code works great and saves the changes to the .xlsx file but I would like to save as PDF at the same time. I have tried examples from all over the web but I just can't figure it out. Any help would be appreciated.

    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    'Retrieve Target Folder Path From User
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
    .Title = "Select A Target Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & ""
    End With

    'In Case of Cancel
    myPath = myPath
    If myPath = "" Then GoTo ResetSettings

    'Target File Extension
    myExtension = "*.xlsx*"

    'Target Path with Ending Extention
    myFile = Dir(myPath & myExtension)

    'Loop through each Excel file in folder
    Do While myFile <> ""
    'Set variable equal to opened workbook
    Set wb = Workbooks.Open(Filename:=myPath & myFile)

    'Ensure Workbook has opened before moving on to next line of code

    wb.Worksheets(1).Range("AJ32").Value = 300

    wb.Worksheets(1).Range("AN6").Value = TextBox1.Value

    'Save and Close Workbook

    wb.Close SaveChanges:=True

    TextBox1.Value = TextBox1.Value + 1

    'Ensure Workbook has closed before moving on to next line of code

    'Get next file name
    myFile = Dir

    'Message Box when tasks are completed
    MsgBox "Task Complete!"

    'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

  2. #2
    New Member
    Join Date
    Oct 2017
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Re: Help with Save AS PDF

    From the looks of it, you'd want to do something like:
    ActiveSheet.ExportAsFixedFormat _

    You would want to do this after
    wb.Close SaveChanges:=True
    as doing so before might not save your changes to the aforementioned XLSX.

    I found my information here if you would like to check it out in more detail or if my information doesn't quite complete the fix.

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