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?

 

[Alex Blakenburg]

Oops meant to send you this.

 

[Doerte]

Hi Alex,
only this is written in the immediate window:

.pdf

 

[Doerte]

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.

Hi Alex,
here are the answers:
1.) it worked once last year, but nothing has changed since then
2.) added the line

Immediate window just show:
.pdf

 

[Alex Blakenburg]

Can you add and additional line:-

debug.print ws.name, ws.Range("I2"), ws.Range("I1")

 

[Doerte]

Added the line right below Debug.Print strPath & strFileName but failure message remains the same

 

[Alex Blakenburg]

It is not fixing the problem just trying to identify it.
What 2 lines did you get in the immediate window ?
Expecting previous .pdf and on the next line a sheet name and the what is I2 & I1 on the sheet (possibly blank)

 

[Alex Blakenburg]

Being in Australia I will be login off shortly.
If it used to work then the sheet name in the debug.print statement is likely to point you at the source of the problem.
The only sheet being excluded by the code is the sheet called "Master".
If any other sheet (even a hidden sheet) does not have any values in I2 & I1, the code will error out.
It should produce pdf's sheets to the left of the one it errors out on. If the sheet it errors out on is the first (after master), then it will error out on the first pass.

 

[Doerte]

It says:

.pdf
.pdf
Review history
.pdf

 

[Doerte]

I discovered the path and file name of the sheet review history was in column J instead if I - it is corrected now, but the failure message remains the same

 

[Alex Blakenburg]

If you have corrected it and rerun it.
The did it create a pdf for the Review History file.
What sheet name appears in the immediate box now ?
(before you run it again click in the immediate box Ctrl+A and delete, to give you a fresh screen)