Help with VBA

[inspectorNDT]

I am really new to VBA's. I am trying to make a "create PDF" button to make a PDF out of the active tab. The file name would be using cells on the active tab then the name of the tab and then another cell.

I would also like it to prompt the user if a file of the same name exist and I would like it to auto save to the active users desktop.

thank you in advance for your help

 

[Michael M]

Upload the file to Dropbox and then post a link back here !

 

[inspectorNDT]

I downloaded the file and copied that code to the spreadsheet I want to use it on and i get {If Dir(stFileName) <> "" Then} will be highlighted and a error code of {error 52 bad file name or number}.

this is the entire code

cell O2 has the date formated as 12-May-19
cell
Option Explicit


Sub ExportToPDFs()
Dim nm As String
Dim ws As Worksheet
Dim stFileName As String
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String

saveInFolder = Environ("USERPROFILE") & "\Desktop"
If Right(saveInFolder, 1) <> "" Then
saveInFolder = saveInFolder & ""
End If

nm = ActiveSheet.Name
celOne = Range("A1").Value
celTwo = Range("C2").Value
celThree = Range("O2").Value

stFileName = saveInFolder & celOne & celTwo & nm & celThree & ".pdf"


If Dir(stFileName) <> "" Then
MsgBox "Specified File Exists", vbInformation, "File Error"
Exit Sub
Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End If


End Sub

.
Download working example : https://www.amazon.com/clouddrive/share/O6uLWDQPZgcVqQi5Hc6JwMlqkPBdNmtqT6GpT4LZZnT

 

[inspectorNDT]

information the sheet is my companies not mine. Don't think i can do that.

Upload the file to Dropbox and then post a link back here !

 

[inspectorNDT]

Cell d2 client name
cell c5 numbers
cell o2 date 12-May-19

I downloaded the file and copied that code to the spreadsheet I want to use it on and i get {If Dir(stFileName) <> "" Then} will be highlighted and a error code of {error 52 bad file name or number}.

this is the entire code

cell O2 has the date formated as 12-May-19
cell
Option Explicit


Sub ExportToPDFs()
Dim nm As String
Dim ws As Worksheet
Dim stFileName As String
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String

saveInFolder = Environ("USERPROFILE") & "\Desktop"
If Right(saveInFolder, 1) <> "" Then
saveInFolder = saveInFolder & ""
End If

nm = ActiveSheet.Name
celOne = Range("A1").Value
celTwo = Range("C2").Value
celThree = Range("O2").Value

stFileName = saveInFolder & celOne & celTwo & nm & celThree & ".pdf"


If Dir(stFileName) <> "" Then
MsgBox "Specified File Exists", vbInformation, "File Error"
Exit Sub
Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End If


End Sub

 

[Logit]

.
These lines should not be in your code :

cell O2 has the date formated as 12-May-19
cell

Even if you remove those two lines, the macro should not error. I don't have an answer for why it is. Sorry.

If you run just the workbook I provided for download, without changing anything, does it work there ?

 

[Michael M]

Well, it appears you are changing the code provided by @Logit and / or myself.
AND you cell references are now different for celOne, two and three
The code in your latest post is missing a backslash on this line

saveInFolder = Environ("USERPROFILE") & "\Desktop"

it should be AND was

saveInFolder = Environ("USERPROFILE") & "\Desktop\"

The error code you are receiving suggest the file location OR file name is incorrect or INVALID

Sub ExportToPDFs()
Dim nm As String
Dim ws As Worksheet
Dim stFileName As String
Dim saveInFolder As String
Dim celOne As String, celTwo As String, celThree As String

saveInFolder = Environ("USERPROFILE") & "\Desktop\"
If Right(saveInFolder, 1) <> "" Then
saveInFolder = saveInFolder & ""
End If

nm = ActiveSheet.Name
celOne = Range("A1").Value
celTwo = Range("C2").Value
celThree = Range("O2").Value

stFileName = saveInFolder & celOne & celTwo & nm & celThree & ".pdf"


If Dir(stFileName) <> "" Then
MsgBox "Specified File Exists", vbInformation, "File Error"
Exit Sub
Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveInFolder & celOne & celTwo & nm & celThree & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End If


End Sub

 

[Logit]

.
My code had these lines after the ENVIRON statement :

If Right(saveInFolder, 1) <> "\" Then
        saveInFolder = saveInFolder & "\"
End If

 

[Michael M]

@Logit...I know !! We both provided the backslash in different ways, but the OP is modifying the code !!....

 

[inspectorNDT]

Sorry those aren't in the code

.
These lines should not be in your code :

cell O2 has the date formated as 12-May-19
cell

Even if you remove those two lines, the macro should not error. I don't have an answer for why it is. Sorry.

If you run just the workbook I provided for download, without changing anything, does it work there ?

 

[Michael M]

@inspectorNDT
If you save the file as an .xlsx extension, the code will be removed.
Instead save the file as an .xlsm (macro enabled) extension.
That might help solve the problem.