VBA: save as pdf with specific name into specific location

[k_amy21]

Hello,

I would like to save one excel file (containing 3 sheets) as a one single pdf file. That file should be saved in the location "P:\Corrosion and Materials BGN\Test traceability" and have a name that corresponds to the value/text inside the cells D6, E6 and F6 of sheet 1, ex. "D6 E6-F6".

Can you please be so kind and let me know how would a VBA code for this look like? I would be extremely grateful!!

Brgs
Amy

 

[Arjan77]

 Sub PrintPdf()
 
 
 strPath = "P:\Corrosion and Materials BGN\Test traceability"
    strFName = Range("D6").Text & " " & Range("E6").Text & " " & Range("F6").Text & ".PDF"
    
    For Each Worksheet In Worksheets
  Worksheet.Select
    Next
    
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        strPath & strFName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
End Sub

 

[Kamolga]

Maybe

Sub test()
     On Error Resume Next
     Sheets("sheet1").Select
     ChDir "P:\Corrosion and Materials BGN\Test traceability"
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
     Filename:="P:\Corrosion and Materials BGN\Test traceability\" & Range("D6") & " " & Range("E6") & "-" & Range("F6"), _
     Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
     
End Sub

 

[k_amy21]

Hey, thanks a lot Arjan77 & Kamolga for your answers.

Unfortunately, the codes didn't work well for me.
- Arjan77's code showed an error in the line "Worksheet.Select".
- Kamolga's code saved only the 1st sheet.

However, with help of both codes, I was able to figure something out and come up with this code:

Sub Save()

Dim fName As String
With Worksheets("Checklist")
fName = .Range("C7").Text & " " & .Range("C6").Text & "-" & .Range("c8").Value
End With
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\AMEKE\Documents" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

(meanwhile, I changed some inputs such as folder location where the file should be saved and the cells to which the file name should refer)

However, I still have 2 issues left:
1. The code works well. It saves the file to the right locations and assigns the appropriate name, BUT it adds in the file name word "Documents" in the front. How can I get rid of that word in the file name??
2. Eventually, I decided to save as a pdf only sheets #1 and #2. How do I input that in the code?

 

[Kamolga]

Sorry did not read the requirements properly...Indeed this code should save the workbook ...don't you have the word document in D6?

Sub test()
     On Error Resume Next
     Sheets("sheet1").Select
     ChDir "P:\Corrosion and Materials BGN\Test traceability"
     ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
     Filename:="P:\Corrosion and Materials BGN\Test traceability\" & Range("D6") & " " & Range("E6") & "-" & Range("F6"), _
     Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
      [COLOR=#333333]End Sub[/COLOR]

For sheet 1 & 2 only

Sheets(Array("Sheet1", "Sheet2")).Select
    Sheets("Sheet1").Activate
    ActiveSheet.ExportAsFixedFormat ...

SO I would try this

Sub test()
     On Error Resume Next
Sheets(Array("Sheet1", "Sheet2")).Select
    Sheets("Sheet1").Activate
    ActiveSheet.ExportAsFixedFormat [COLOR=#333333] Type:=xlTypePDF, _[/COLOR]     Filename:="P:\Corrosion and Materials BGN\Test traceability\" & Range("D6") & " " & Range("E6") & "-" & Range("F6"), _
     Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
     
End Sub

 

[Arjan77]

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

[COLOR=#333333]Dim fName As String[/COLOR]
[COLOR=#333333]With Worksheets("Checklist")[/COLOR]
[COLOR=#333333]fName = .Range("C7").Text & " " & .Range("C6").Text & "-" & .Range("c8").Value[/COLOR]
[COLOR=#333333]End With

[/COLOR][COLOR=#ff0000]for i = 1 to WorkSheets.count
worksheets(i).visible = false
next

Worksheets(1).visible = true
Worksheets(2).visible = true[/COLOR]

[COLOR=#333333]ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _[/COLOR]
[COLOR=#333333]"C:\Users\AMEKE\Documents[/COLOR][COLOR=#ff0000]\[/COLOR][COLOR=#333333]" & fName, Quality:=xlQualityStandard, _[/COLOR]
[COLOR=#333333]IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False[/COLOR]

[COLOR=#FF0000]for i = 1 to WorkSheets.count[/COLOR]
[COLOR=#FF0000]worksheets(i).visible = True[/COLOR]
[COLOR=#FF0000]next[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[k_amy21]

Kamolga, the code works great now. Now I know how to manipulate with the sheet no. that I want to print or not. Thank you so much!!

Arjan77, your code somehow erased all of my sheets.(?!?!?!) Good that I had an "experimental" version of this file and saved the original not to mess with it. I am not sure if my excel has a bug or something, 'cuz I had previously situations that the same code works fine in the same file but on another person's computer. Not sure what happened now...

I am still not able to remove the word "Document" from the file name. Any thoughts on this one? Please...

 

[Arjan77]

Your worksheets are hidden not removed, I didn't test the code so maybe the worksheets.count gives 0 in case of a hidden sheet. For your documents problem just add a \ after documents in "C:\Users\AMEKE\Documents\", this should help.

 

[Arjan77]

If you start from your first worksheet, this code works. The On error resume next guarantees that your sheets are unhidden in the end

Sub Save()On Error Resume Next


Dim fName As String
With Worksheets("Checklist")
fName = .Range("C7").Text & " " & .Range("C6").Text & "-" & .Range("c8").Value
End With


For i = 2 To Worksheets.Count
Worksheets(i).Visible = False
Next


Worksheets(1).Visible = True
Worksheets(2).Visible = True


ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\AMEKE\Documents\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False


For i = 1 To Worksheets.Count
Worksheets(i).Visible = True
Next
End Sub

 

[k_amy21]

Awesome!! This works great, Arjan77! I finally don't have "Document" in the file name. "/" helped in removing it. Only... I had to change the location to make it work, because it didn't want to work with location "C:\Users\AMEKE\Documents". Really weird...