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?

  • 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.


  • 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.

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


  • 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
    1. Sub Test()
    2. Dim fso As Object
    3. Set fso = CreateObject("Scripting.FileSystemObject")
    4. MsgBox fso.GetBaseName(ThisWorkbook.Name)
    5. End Sub


    Usually, one builds the path\filename.pdf using a cell, worksheet name, workbook name, or date or such. e.g.

    Code
    1. fn = "c:\invoices\" & Format(Date,"yyyymmdd") & " Invoice #" & [A2] & ".pdf"
  • Hello, i could not find the thread i needed and this one is close to what i need.


    I have a Word document and what i want is a macro or a button to create new file...
    1) i want to clear all the contents from the lines
    2) i want to update the numbers of the file and update the date
    3) i want to save the file and create a new file with new name (Daily Log_081-05-09-2019) and when create a new one i want to be new file ( Daily Log_082-06-09-2019) so 81 goes to 82 and 05 goes to 06.
    4) is this possible?


    attached you have a picture with what i want to be erased and what to be updated !!