Macro Printing error

[OKCIrish]

I have a workbook that has a print report button on it, and when that button is pushed a macro runs and it consolidates the tabs into set print areas and titles and gives me a built PDF report with cover pages and graphs and the consolidated information that is in the tabs, it also saves that PDF on the desktop. However anytime this title page has a / on in the billing information area, it throws the macro and gives me this error,

ActiveWorkbook.ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _
Sheets("General").Range("C3").Value & " - " & Sheets("General").Range("C4").Value & " Drill Pipe Inspection Report " & " (" & Format(Date, "mm-dd-yyyy") & ")" & ".pdf", OpenAfterPublish:=True

Is there a way to fix this error or is there a way where I can make it to where the columns reject the input if a / is detected?

DRILCO DP Report V4.4.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Billing Information					Inspection Performed (dropdown list)								Specification Used (dropdown list)
2	Date:		January 2, 2022
3	Customer:
4	Rig:		H/P 22
5	Well/Location:
6	Ordered By:
7														Tube Specs
8	OCSG:													Size
9	PO #:													Grade
10	Quote #:													Weight
11														New Wall
12									Reports Available	Include Report? (dropdown list)				Min Wall
13	JOB#:								Overview	Yes				Tool Joint Specs
14	Inspected at:								Drill Pipe	Yes				New OD
15									Images	Yes				Min OD
16	Personnel Info								Appendix	Yes				Max Pin ID
17	Lead Inspector								Summary	Yes				Min TS Box
18	Monitor								DS1 Reference	Yes				Min TS Pin
19	Inspector Helper(s)													Recommend HB
20														Range
21														Connection
22														Box Length Min
23														Box Length Max
24														Pin Length Min
25														Pin Length Max
26
General

Cells with Data Validation
Cell	Allow	Criteria
J13:J19	List	=Lists!$G$3:$G$4
N2:R5	List	=Lists!$H$1:$H$14
F2:L10	List	=Lists!$A$9:$A$19
O8	List	=Lists!$H$23:$H$30
O9	List	=Lists!$H$17:$H$20
O20	List	=Lists!$H$33:$H$34

 

[HaHoBe]

Hi OKCIrish,

what about if C3 is empty like in your sample?

Dim strC3 As String
Dim strC4 As String

With Sheets("General")
  strC3 = Replace(.Range("C3").Value, "/", "_")
  strC4 = Replace(.Range("C4").Value, "/", "_")
End With
ActiveWorkbook.ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _
    strC3 & " - " & strC4 & " Drill Pipe Inspection Report " & " (" & Format(Date, "mm-dd-yyyy") & ")" & ".pdf", OpenAfterPublish:=True

Ciao,
Holger

 

[OKCIrish]

Hi OKCIrish,

what about if C3 is empty like in your sample?

Dim strC3 As String
Dim strC4 As String

With Sheets("General")
  strC3 = Replace(.Range("C3").Value, "/", "_")
  strC4 = Replace(.Range("C4").Value, "/", "_")
End With
ActiveWorkbook.ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _
    strC3 & " - " & strC4 & " Drill Pipe Inspection Report " & " (" & Format(Date, "mm-dd-yyyy") & ")" & ".pdf", OpenAfterPublish:=True

Ciao,
Holger

That fixed the error. Thank you.

 

[OKCIrish]

I have encountered another issue. I got a new computer and had to re-download all of the applications and PDF readers. Now when I hit the print page macro, I get this error and it tells me this is the line I need to debug/fix.

ActiveWorkbook.ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _
    strC3 & " - " & strC4 & " Drill Pipe Inspection Report " & " (" & Format(Date, "mm-dd-yyyy") & ")" & ".pdf", OpenAfterPublish:=True

Is the whole code for context.

Dim strC3 As String
Dim strC4 As String

With Sheets("General")
  strC3 = Replace(.Range("C3").Value, "/", "_")
  strC4 = Replace(.Range("C4").Value, "/", "_")
End With

ActiveWorkbook.ExportAsFixedFormat 0, Environ("USERPROFILE") & "\Desktop\" & _
    strC3 & " - " & strC4 & " Drill Pipe Inspection Report " & " (" & Format(Date, "mm-dd-yyyy") & ")" & ".pdf", OpenAfterPublish:=True

 

[HaHoBe]

Hi,

please check the path. Do you use OneDrive? Using the code provided the path is

C:\Users\holger\Desktop\

on my system.

When using

Sub DesktopWSH()
  Dim objWSH As Object
  Dim strDesktopPath As String
  Set objWSH = CreateObject("wscript.Shell")
  strDesktopPath = objWSH.SpecialFolders.Item("Desktop")
  Debug.Print strDesktopPath & Application.PathSeparator
End Sub

it's

C:\Users\holger\OneDrive\Desktop\

Ciao,
Holger

 

[OKCIrish]

Hi,

please check the path. Do you use OneDrive? Using the code provided the path is

C:\Users\holger\Desktop\

on my system.

When using

Sub DesktopWSH()
  Dim objWSH As Object
  Dim strDesktopPath As String
  Set objWSH = CreateObject("wscript.Shell")
  strDesktopPath = objWSH.SpecialFolders.Item("Desktop")
  Debug.Print strDesktopPath & Application.PathSeparator
End Sub

it's

C:\Users\holger\OneDrive\Desktop\

Ciao,
Holger

Yes, my OneDrive just got synchronized to my new PC. So I need to modify the code in order for it to save properly? Will this effect other users that use this macro function who do not have OneDrive set up?

 

[HaHoBe]

Hi OKCIrish,

I would recommend to use an altered macro which should avoid to take any user to the IDE due to an error:

Sub MrE_1228513_1701E14_Update()
' https://www.mrexcel.com/board/threads/macro-printing-error.1228513/
Dim strC3 As String
Dim strC4 As String
Dim strDTEnv As String
Dim strWSHEnv As String
Dim strPathToUse As String
Dim objWSH As Object

'checking for existence of folder
strDTEnv = Environ("USERPROFILE") & "\Desktop"
If Dir(strDTEnv, vbDirectory) = "" Then
  Set objWSH = CreateObject("wscript.Shell")
  strWSHEnv = objWSH.SpecialFolders.Item("Desktop") & Application.PathSeparator
  Set obhwsh = Nothing
  If Dir(strDTEnv, vbDirectory) = "" Then
    MsgBox "Could neither find " & strDTEnv & vbCrLf & _
        "nor " & strWSHEnv & ", please check!", vbInformation, "End here"
    Exit Sub
  End If
End If

'make up the path for the export
If Len(strDTEnv) > 0 Then
  strPathToUse = strDTEnv
Else
  strPathToUse = strDTEnv
End If
strPathToUse = strPathToUse & Application.PathSeparator

With Sheets("General")
  strC3 = Replace(.Range("C3").Value, "/", "_")
  strC4 = Replace(.Range("C4").Value, "/", "_")
End With

'in case of error proceed at the given line instead of breaking code
On Error GoTo err_here
ActiveWorkbook.ExportAsFixedFormat 0, strPathToUse & strC3 & " - " & strC4 & " Drill Pipe Inspection Report " & _
    " (" & Format(Date, "mm-dd-yyyy") & ")" & ".pdf", OpenAfterPublish:=True

Exit Sub

err_here:
  MsgBox "Error occurred in 'MrE_1228513_1701E14' creating PDF on " & Now & vbCrLf & _
      "Error number: " & Err.Number & vbCrLf & _
      "Error description: " & Err.Description, vbInformation, "Could not create PDF"

End Sub

Ciao,
Holger