Runtime Error '-2147024773 (8007007b)'

[Doerte]

Hello,
I get above mentioned error code when I try to run the following macro:

Sub PrintAndSavePdf()

    Dim strFileName As String
    Dim strPath As String
    Dim ws As Worksheet
    Dim strPathSplit As Variant
    Dim myTempPath As String
   

    For Each ws In ActiveWorkbook.Worksheets
   
        If ws.Name <> "Master" Then

            strFileName = ws.Range("I2") & ".pdf"
            strPath = ws.Range("I1")
       
            myTempPath = ""
           
             If Dir(strPath, vbDirectory) = "" Then
           
                strPathSplit = Split(strPath, "\\")
                If UBound(strPathSplit) > 0 Then
                    myTempPath = "\\"
                    strPathSplit = Split(strPathSplit(1), "\")
                End If
               
                myTempPath = myTempPath & strPathSplit(0) & "\"
               
                For i = 1 To UBound(strPathSplit)
                    myTempPath = myTempPath & strPathSplit(i) & "\"
                    If Dir(myTempPath, vbDirectory) = "" Then
                        MkDir (myTempPath)
                    End If
                Next i
               
            End If
           
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strPath & strFileName, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
        End If
   
    Next ws
End Sub

Someone gave me this code, but it does not work.

It is an excelmap containing app 70 different worksheets.
On each worksheet there is in cell I1 the path to a server address (IP/path) and in cell I2 the file name, which contains "year name.02.01 - name"
Each sheet has the be stored as pdf under the mentioned path (I1) with the mentioned name (I2)
If the path does not exist the macro should create this path and store the file as descibed.

The red marked section turns to yellow when I open the debug mode.

Can anyone pls help?

 

[6StringJazzer]

What are the values of strPath and strFileName when this error occurs?

 

[Doerte]

Hi Jeff,
thanks for getting back.

Examples are:
for path: \\192.168.50.3\Doerte\Products\Apple\
for file name: =YEAR(TODAY()) & " " & (F57) & " - ORGANIC CERTIFIED " & (A2)
(The file name in this case is 2021 OBF-CT-APPLE.JC.02.01. - ORGANIC CERTIFIED AND BABY FOOD APPROVED CONCENTRATED APPLE JUICE)

On every sheet is a different path and a different file name.

I dont know, where exactly the error occurs - the only message I get when I click the macro is the failure number of the runtime error.

Thank you!

 

[Doerte]

Does anybody has a hint, what might be wrong? Your help is highly appreciated - many thanks in advance!

 

[Alex Blakenburg]

What happens if you manually try to save as pdf and put the file path in your save as box ?
\\192.168.50.3\Doerte\Products\Apple\
I have had permissions issues using an IP address before which didn't seem to happen if I used a drive mapping.

 

[Doerte]

Hi Alex,
if I save manually in the mentioned directory it is working out.
I've also tried to change the path to the drive letter, but it results in the same runtime error.

 

[Alex Blakenburg]

The folders aren't by any chance on Onedrive or Sharepoint are they ?

 

[Alex Blakenburg]

If not can you try
1) a shorter file name
2) saving it to your c drive.
For 2 make sure the folder exists, the mkdir is only geared for a \\ file path.

Also do you know how to set a breakpoint and how to use the immediate window ?
I am in a different timezone (Australia)

 

[Doerte]

Hi Alex,
the storage is done on a local server - no share point, one drive or dropbox.
I have shortened the file names - now it lookes like 2021 OBF-CT-APPLE.JC.02.01 - the folder now is created on my local C drive - still not working and the same error.
But the macro should also create a folder, if it does not exist.
Do you have any idea?

Unfortunately I do not know anything about breakpoints or immediate windows.

Regards to Australia and thanks for your effort!

 

[Alex Blakenburg]

1) have you proved that the create a folder part works ?
It is specific to using a network path that needs the \\ so I haven't tested it.

2) Before the line
ws.ExportAsFixedFormat Type:=xlTypePDF,
can you add this line.

debug.print strPath & strFileName

It should print to the immediate window in the VBA screen.

If you can't see it press Ctrl+G when you are in the VBA window.

Copy paste the text it outputs in a reply here.