PDA

View Full Version : Solved: Command Button (Save as PDF)



pbobadilla
10-01-2012, 08:29 AM
Hello,

I am trying to set-up a command button in a userform that will save a specific worksheet within the workbook as a pdf. I can only currently save it as the same file name (packing list) each time i create the pdf. How would I be able to save the file in the same path, but with the file name as the value in cell b1 of worksheet "Data Entry" ? Any help would be appreciated.

Here is the code I currently have:


Private Sub CommandButton1_Click()
Range("B1").Value = TextBox1
Range("C29").Value = TextBox2
Range("C30").Value = TextBox3
Range("D30").Value = TextBox4
Range("C31").Value = TextBox5
Range("E30").Value = TextBox6
Range("F31").Value = TextBox7
Range("F30").Value = TextBox8
Range("G30").Value = TextBox9

Sheets("PL-TYPE 1").Select
ChDir "P:\ShipRecv\Packing Lists"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"P:\ShipRecv\Packing Lists\packing list.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub

JKwan
10-01-2012, 12:56 PM
try this

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"P:\ShipRecv\Packing Lists\" & Worksheets("Data Entry").Range("B1") & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

pbobadilla
10-01-2012, 01:38 PM
try this

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"P:\ShipRecv\Packing Lists\" & Worksheets("Data Entry").Range("B1") & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True


Thanks for the help, but it looks like when I try this it gives me a subscript out of range error. :( What could be causing this?

JKwan
10-01-2012, 02:10 PM
I don't know, my is ok. Can you run this and see if you get:

Sub test()
MsgBox "P:\ShipRecv\Packing Lists\" & Worksheets("Data Entry").Range("B1") & ".pdf"
End Sub


Just stabbing in the dark, do you have a value in B1?

Aussiebear
10-01-2012, 10:08 PM
it gives me a subscript out of range error. What could be causing this:(?

Normally speaking it means it cannot find the sheet as being referenced in the code. Check to ensure the filename is correct

pbobadilla
10-02-2012, 06:45 AM
Jkwan and Aussiebear,

Yes, there is a value in B1. I believe I solved the issue. It looks like it was trying to add the value from cell b1 worksheet "Data Entry" when it was previously selecting worksheet "PL-TYPE 1." Instead, I asked it to add the same value, but this time it is listed in "PL-TYPE 1" in cell E21. This seems to work. Thanks for all of the help guys.