Announcement

Collapse
No announcement yet.

save a excel file as PDF and then print that file

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • save a excel file as PDF and then print that file



    I have recorded a macro to save a excel file as PDF and then print that file. I saved this file in folder A which is a last week report. Now next week i am copying this file and pasting in folder B to create a last week report. When i am using the macro, its saving the file as PDF but in folder A not in folder B. Could anyone please help me on this one?

  • #2
    Welcome to the forum! To help, we need to see code or the file. Click the # icon on reply toolbar to insert tags to paste between.

    If you copied and pasted, you should know which folder you pasted to. If you mean that you ran the recorded macro again, I guess that you did not modify the recorded macro.

    Comment


    • #3
      Code:
      Sub SavePrint()
      
      '
      
      ' SavePrint Macro
      
      '
      
      ' Keyboard Shortcut: Ctrl+s
      
      '
      
          ChDir _
      
              "G:\Report\1. Monthly \4. EXP\2020\1.  WR\Notes\FY2001\05. Aug 2019\3. WE 18 Aug 2019"
      
          ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
      
              "G:\Report\1. Monthly \4. EXP\2020\1.  WR\Notes\FY2001\05. Aug 2019\3. WE 18 Aug 2019\Report-WE 18 Aug.pdf" _
      
              , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
      
              :=False, OpenAfterPublish:=True
      
      End Sub
      Above is the code, The file name is Report-WE 18 Aug and if I change the file name next week ex. Report-WE 24 Aug and save in WE 24 Aug 2019. Macro works but file gets saved on previous folder not the current one.

      Comment


      • #4
        Maybe you can use the code to get the new file name and the folder to save it in

        Code:
        Option Explicit
        
        Sub SavePrint()
            Dim sFolder As String, sFilName As String
            ''/// get the file name to save as
        
            sFilName = Application.InputBox("Please enter the name to save as", "Saveto PDF")
            If sFilName = False Then
                MsgBox "You must enter a file name", vbCritical, "Input required"
                Exit Sub
            End If
        
            ' '/// Open the select folder dialog
            With Application.FileDialog(msoFileDialogFolderPicker)
                If .Show = -1 Then    ' if OK is pressed
                    sFolder = .SelectedItems(1)
                End If
            End With
        
            If sFolder <> "" Then    ' if a file was chosen
                ChDir sFolder
        
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                                                sFolder & Application.PathSeparator & sFilName & ".pdf" _
                                                , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                                                                                                          :=False, OpenAfterPublish:=True
            End If
        End Sub
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5


          If you need to change a base filename and maybe the folder, Roy's code will suffice.

          If you mean that you saved the xlsm file and want to use that as the base filename, you can do it this way.
          Code:
          Sub Test()
              Dim fso As Object
              Set fso = CreateObject("Scripting.FileSystemObject")
              MsgBox fso.GetBaseName(ThisWorkbook.Name)
          End Sub
          Usually, one builds the path\filename.pdf using a cell, worksheet name, workbook name, or date or such. e.g.
          Code:
          fn = "c:\invoices\" & Format(Date,"yyyymmdd") & " Invoice #" & [A2] & ".pdf"

          Comment

          Working...
          X