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?
save a excel file as PDF and then print that file
-
kadyanp -
August 16, 2019 at 3:14 AM -
Thread is marked as Resolved.
-
-
-
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.
-
Code
Display More[FONT=Calibri][size=12]Sub SavePrint()[/SIZE][/FONT] [FONT=Calibri][size=12]'[/SIZE][/FONT] [FONT=Calibri][size=12]' SavePrint Macro[/SIZE][/FONT] [FONT=Calibri][size=12]'[/SIZE][/FONT] [FONT=Calibri][size=12]' Keyboard Shortcut: Ctrl+s[/SIZE][/FONT] [FONT=Calibri][size=12]'[/SIZE][/FONT] [FONT=Calibri][size=12] ChDir _[/SIZE][/FONT] [FONT=Calibri][size=12] "G:\Report\1. Monthly \4. EXP\2020\1. WR\Notes\FY2001\05. Aug 2019\3. WE 18 Aug 2019"[/SIZE][/FONT] [FONT=Calibri][size=12] ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _[/SIZE][/FONT] [FONT=Calibri][size=12] "G:\Report\1. Monthly \4. EXP\2020\1. WR\Notes\FY2001\05. Aug 2019\3. WE 18 Aug 2019\Report-WE 18 Aug.pdf" _[/SIZE][/FONT] [FONT=Calibri][size=12] , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _[/SIZE][/FONT] [FONT=Calibri][size=12] :=False, OpenAfterPublish:=True[/SIZE][/FONT] [FONT=Calibri][size=12]End Sub[/SIZE][/FONT]
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
Code
Display MoreOption 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
-
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.
CodeSub 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.
-
-
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 !!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!