Save As Excel & PDF File in Seperate Folder

[leewoo5]

Hi,

I would like to alter the below code to include saving the file as an excel as well as pdf into the folder...
Additionally, I would like for there to be a hyphen and spaces between the cell values for file name if possible:

Sub SaveAsPDF()


Dim fName As String
With ActiveSheet
    fName = .Range("L4").Value & .Range("P1").Value & .Range("E6").Value
    .ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
            "I:\FST\Quality\Non-Conformance\Non-Conformance Reports\Open\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            
            
End With
End Sub

Many thanks,

 

[gallen]

If understood. changes in red

Sub SaveAsPDF()


Dim fName As String
[COLOR=#ff0000]Dim sLoc As String 'location


    sLoc = "I:\FST\Quality\Non-Conformance\Non-Conformance Reports\Open\"[/COLOR]


With ActiveSheet
    fName = .Range("L4").Value & .Range("P1").Value [COLOR=#ff0000]& "-" &[/COLOR] .Range("E6").Value
    
[COLOR=#ff0000]    'Save as xlsx
    ActiveWorkbook.SaveAs Filename:=sLoc & fName, FileFormat:=xlOpenXMLWorkbook[/COLOR]
    
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            sLoc & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            
            
End With
End Sub

 

[leewoo5]

Thanks for your help! Unfortunately there is a compatibility error when saving as an Excel file.
Could the macro run as saving the worksheet as pdf but the workbook as excel? I think this may be the issue?

Many thanks,

 

[gallen]

Sorry, of course it needs to be macro-enabled so the fileformat should be 'xlOpenXMLWorkbookMacroEnabled':

[COLOR=#333333]Sub SaveAsPDF()[/COLOR]

Dim fName As String
[COLOR=#ff0000]Dim sLoc As String 'location


    sLoc = "I:\FST\Quality\Non-Conformance\Non-Conformance Reports\Open\"[/COLOR]


With ActiveSheet
    fName = .Range("L4").Value [COLOR=#ff0000]& "-" &[/COLOR]  .Range("P1").Value [COLOR=#ff0000]& "-" &[/COLOR] .Range("E6").Value
    
[COLOR=#ff0000]    'Save as xlsx
    ActiveWorkbook.SaveAs Filename:=sLoc & fName, FileFormat:=[/COLOR][COLOR=#ff0000]xlOpenXMLWorkbookMacroEnabled[/COLOR]
    
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            sLoc & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            
            
End With [COLOR=#333333]End Sub[/COLOR]

I also missed a hyphon in the name, didn't realise there was 3 parts.
I've tested and it works on my machine.