run time error 53, file not found

M

marcenet03

New Member
Joined
Apr 12, 2019
Messages
22
Hello, I have the following macro and used to run perfectly but now isn't working. As it shows run time error 53, file not found. Can someone please help me with what I'm missing?

VBA Code: 
Sub printxxx()


' Print_quote XXX Macro

    ActiveSheet.PageSetup.Orientation = xlLandscape
    Worksheets("Quote").PageSetup.PrintArea = "$H$6:$Z$133"
     strFile = ThisWorkbook.Path & "\" & strFile
    
    

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote ", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True


ActiveSheet.PageSetup.Orientation = xlPortrait
  
End Sub
 
Sort by date Sort by votes
rlv01 said:
Does this fail, and if so, on which line of code and for what values of ExportFile and ExportFolder? Does either msgbox appear?
VBA Code: 
Sub printxxx
    Dim ExportFolder As String, ExportFile As String
    Dim strFile As String

    ' Print_quote XXX Macro

    If ThisWorkbook.Path = "" Then
        MsgBox "This workbook (" & ThisWorkbook.Name & ") has not been saved yet and thus has no path.", vbExclamation
        Exit Sub
    End If

    ActiveSheet.PageSetup.Orientation = xlLandscape
    Worksheets("Quote").PageSetup.PrintArea = "$H$6:$Z$133"
    strFile = ThisWorkbook.Path & "\" & strFile

    ExportFile = ThisWorkbook.Path & "\" & CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote "
    Debug.Print ExportFile

    ExportFolder = Left(ExportFile, InStrRev(ExportFile, "\"))
    Debug.Print ExportFolder

    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(ExportFolder) Then
            MsgBox "Folder " & vbCr & vbCr & ExportFolder & vbCr & vbCr & " not found.", vbExclamation
            Exit Sub
        End If
    End With

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ExportFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True


    ActiveSheet.PageSetup.Orientation = xlPortrait
End Sub
Click to expand...
hi, thanks, it doesn't work. Shows the same error. and debug in
ExportFile= ThisWorkbook.Path & "\" & CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote "
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
So if the ExportFile statement is where it's failing, then let's break it down some more and see where the error is being generated. What does the 'Test Results' msgbox say?
VBA Code: 
Sub Test1()
    Dim ExportFolder As String, ExportFile As String
    Dim strFile As String
    Dim S As String, I As Long
    Dim FSO As Object, FileObj As File
    Dim SA(1 To 50) As String


    On Error Resume Next
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileObj = FSO.GetFile(ThisWorkbook.FullName)
    On Error GoTo 0

    If FSO Is Nothing Then
        MsgBox "Cannot Create FileSystemObject", vbExclamation
        Exit Sub
    End If

    If FileObj Is Nothing Then
        MsgBox "Cannot Create File Object for '" & ThisWorkbook.FullName & "'", vbExclamation
        Exit Sub
    End If


    On Error GoTo ErrHandler
    For I = 1 To 8
        Select Case I
        Case 1
            SA(I) = ThisWorkbook.FullName
        Case 2
            SA(I) = FileObj.Path
        Case 3
            SA(I) = FileObj.ParentFolder.Name
        Case 4
            SA(I) = ThisWorkbook.Path & "\"
        Case 5
            SA(I) = FSO.GetFile(ThisWorkbook.FullName).ParentFolder.Name
        Case 6
            SA(I) = " XXXQuote "
        Case 7
            SA(I) = ThisWorkbook.Path & "\" & FSO.GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote "
        Case 8
            SA(I) = ThisWorkbook.Path & "\" & CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote "
        End Select
    Next I
    On Error GoTo 0

    For I = 1 To 8
        S = S & I & ": " & SA(I) & vbCr
    Next I

    MsgBox S, vbInformation, "Test Results"


    ExportFile = ThisWorkbook.Path & "\" & CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote "
    Debug.Print ExportFile

    ExportFolder = Left(ExportFile, InStrRev(ExportFile, "\"))
    Debug.Print ExportFolder

    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(ExportFolder) Then
            MsgBox "Folder " & vbCr & vbCr & ExportFolder & vbCr & vbCr & " not found.", vbExclamation
            Exit Sub
        End If
    End With

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ExportFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True


    ActiveSheet.PageSetup.Orientation = xlPortrait
    Exit Sub
ErrHandler:
    SA(I) = "Error, #: " & Err.Number
    Resume Next
End Sub
 
Upvote 0
rlv01 said:
So if the ExportFile statement is where it's failing, then let's break it down some more and see where the error is being generated. What does the 'Test Results' msgbox say?
VBA Code: 
Sub Test1()
    Dim ExportFolder As String, ExportFile As String
    Dim strFile As String
    Dim S As String, I As Long
    Dim FSO As Object, FileObj As File
    Dim SA(1 To 50) As String


    On Error Resume Next
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileObj = FSO.GetFile(ThisWorkbook.FullName)
    On Error GoTo 0

    If FSO Is Nothing Then
        MsgBox "Cannot Create FileSystemObject", vbExclamation
        Exit Sub
    End If

    If FileObj Is Nothing Then
        MsgBox "Cannot Create File Object for '" & ThisWorkbook.FullName & "'", vbExclamation
        Exit Sub
    End If


    On Error GoTo ErrHandler
    For I = 1 To 8
        Select Case I
        Case 1
            SA(I) = ThisWorkbook.FullName
        Case 2
            SA(I) = FileObj.Path
        Case 3
            SA(I) = FileObj.ParentFolder.Name
        Case 4
            SA(I) = ThisWorkbook.Path & "\"
        Case 5
            SA(I) = FSO.GetFile(ThisWorkbook.FullName).ParentFolder.Name
        Case 6
            SA(I) = " XXXQuote "
        Case 7
            SA(I) = ThisWorkbook.Path & "\" & FSO.GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote "
        Case 8
            SA(I) = ThisWorkbook.Path & "\" & CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote "
        End Select
    Next I
    On Error GoTo 0

    For I = 1 To 8
        S = S & I & ": " & SA(I) & vbCr
    Next I

    MsgBox S, vbInformation, "Test Results"


    ExportFile = ThisWorkbook.Path & "\" & CreateObject("Scripting.FileSystemObject").GetFile(ThisWorkbook.FullName).ParentFolder.Name & " XXXQuote "
    Debug.Print ExportFile

    ExportFolder = Left(ExportFile, InStrRev(ExportFile, "\"))
    Debug.Print ExportFolder

    With CreateObject("Scripting.FileSystemObject")
        If Not .FolderExists(ExportFolder) Then
            MsgBox "Folder " & vbCr & vbCr & ExportFolder & vbCr & vbCr & " not found.", vbExclamation
            Exit Sub
        End If
    End With

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ExportFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True


    ActiveSheet.PageSetup.Orientation = xlPortrait
    Exit Sub
ErrHandler:
    SA(I) = "Error, #: " & Err.Number
    Resume Next
End Sub
Click to expand...
Thank you so much. I got user defined type not defined. in
VBA Code: 
  Dim FSO As Object, FileObj As File
 
Upvote 0
Change
VBA Code: 
  Dim FSO As Object, FileObj As File
to
VBA Code: 
  Dim FSO As Object, FileObj As Object
and re-run.
 
Upvote 0
rlv01 said:
Change
VBA Code: 
  Dim FSO As Object, FileObj As File
to
VBA Code: 
  Dim FSO As Object, FileObj As Object
and re-run.
Click to expand...
it says cannot create file object for https://......
in debug
VBA Code: 
 MsgBox "Cannot Create File Object for '" & ThisWorkbook.FullName & "'", vbExclamation
 
Upvote 0
Could the information here about file naming restrictions (particularly length) in sharepoint be relevant to your situation?
 
Upvote 0
marcenet03 said:
it says cannot create file object for https://......
Click to expand...

That is a very unsatisfactory response. If you come to MrExcel to ask others to expend effort on your behalf, you should be willing to make an effort in return. You should have posted the actual message in full instead of attempting to paraphrase or shorten. FWIW, I think Peter_SSs is on to something. It is something about the workbook name and/or path you have chosen that is at the root of your problem.
 
Upvote 0
rlv01 said:
That is a very unsatisfactory response. If you come to MrExcel to ask others to expend effort on your behalf, you should be willing to make an effort in return. You should have posted the actual message in full instead of attempting to paraphrase or shorten. FWIW, I think Peter_SSs is on to something. It is something about the workbook name and/or path you have chosen that is at the root of your problem.
Click to expand...
apologies, i thought I've uploaded the screen print.
 

Attachments

  • Capture.JPG
    Capture.JPG
    17.5 KB · Views: 5
Upvote 0
marcenet03 said:
apologies, i thought I've uploaded the screen print.
Click to expand...
So, did you look at the link I provided?

I cannot vouch for its accuracy as I do not use sharepoint but, if correct, it looks like you are well in breach of the stated character limit.
 
Upvote 0
Peter_SSs said:
So, did you look at the link I provided?

I cannot vouch for its accuracy as I do not use sharepoint but, if correct, it looks like you are well in breach of the stated character limit.
Click to expand...
Hi Peter, yes I've looked at it. But still, it doesn't make sense when it used to work perfectly. I have changed the macro to this:
VBA Code: 
Sub printxxx()


' Print_quote XXX Macro

MsgBox ActiveSheet.Parent.FullName
    ActiveSheet.PageSetup.Orientation = xlLandscape
    Worksheets("Quote").PageSetup.PrintArea = "$H$6:$Z$133"
     strFile = ThisWorkbook.Path & "\" & strFile
    
    

  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & ParentFolderName & " XXXQuote ", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True


ActiveSheet.PageSetup.Orientation = xlPortrait
  
End Sub

Now the problem that I have is that it opens the pdf online but doesn't save it on one drive.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA - PDF two / three sheets
Replies
7
Views
318
Olov
O
vipulgos
  • Question
Merging the Exported pdf files with same starting names
Replies
1
Views
370
John_w
J
S
  • Question
VBA Code - Message Box "If file exists"
Replies
9
Views
462
Spaztic
S
A
  • Solved
Creating PDF with vba code
Replies
2
Views
220
asyamonique
A
K
  • Question
Excel Macro to Save to .pdf not working
Replies
9
Views
301
kpost
K

Forum statistics

Threads
1,215,056
Messages
6,122,907
Members
449,096
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top