Use macro button to save xlm as PDF and xls in network folder

[scoudny]

Hello Druids of excel

I'm fairly experienced with excel but green as grass with VBA.
I work with multiple workbooks that when completed need to be saved with individual titles and also as pdfs in various folders on our companies network drives.
I have already scrounged a very good piece of code from one of the forums on here already to save the active excel worksheet as a pdf and and name it as the contents of a target cell but would you gods of the spreadsheet be able to help me adapt the code to;

• save the worksheet as a pdf. with the name as range("X7")
• save the pdf to a network folder with the address as range("X8")

and some of the workbooks will have to be saved in the same folder as the pdf so also;

• save the workbook as a xls. with the name as range("X7")
• save both the pdf and workbook to a network folder with the address as range("X8")

Cell X7 creates a document name from various details entered in the workbook and cell X8 are a choice of north or south network folders determined from the first two characters in cell X7.
Once the worksheet has been checked for errors I would like to be able to do all of this at the press of a button if possible.
The code I have at the moment just to create a named pdf document and auto-open to inspect is

Private Sub CommandButton1_Click()
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=Range("X7"), _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End Sub

Thank you for any help you can provide oh lords of the green X

 

[edmitchell]

Hi - the code does what I think you need, but saves the file as an Macro enabled excel file, but wasn't sure if you would prefer a 'standard' file format (without any of the VBA Code)? Also, the code always saves the file as both an 'xlsm' and 'pdf' - as I wasn't sure how you were purposing to determine if both formats were needed or just 'pdf'.

I've also added some comments into the VBA, should you need to adapt the code further.

hope it helps

Private Sub CommandButton1_Click()

'X8 to contain full path including last backslash, e.g. C:\TEMP\MrExcel\
'X7 to contains the Filename without extension
FN = Range("x8") & Range("X7")
FN_PDF = FN & ".pdf"
FN_XLSM = FN & ".xlsm"


'Save as a PDF
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=FN_PDF, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

Application.DisplayAlerts = False 'Set False, so not warned about over writing the files.

'Save as a macro enabled Excel file (e.g. *.xlsm)
    ActiveWorkbook.SaveAs Filename:=FN_XLSM, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Application.DisplayAlerts = True    'Set back to true if needed...


End Sub

 

[scoudny]

Thank you Ed you are a lifesaver, it worked perfectly.
The only reason I thought to save as an xls was to minimise file size as there's loads of formulae on there already but tbh there's not a lot of difference.
Thank you for the explanations as well, like I said I am as green as grass with VBA.