runtime error '-2147024773 (8007007b)'

Doerte

New Member
Joined
Aug 1, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi guys!
Last year the macro was running well - the properties were not changed and this year it is not working anymore.
The code is as follows:

VBA Code:
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
            
          [B]  ws.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=strPath & strFileName, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False[/B]
        End If
    
    Next ws
End Sub
Sub PrintAndSavePdf()

The fat marked turn yellow, when I ran the macro.

I have checked the following:
cells are correctly refered : I1 path and I2 file name
both are within the numbers my OS allows
no unallowed signs
path is ending with \

Just to give examples for I1 and I2:
I1 "\\192.168.50.3\VB\Product Specifications\Ap\Sal\SAI FSA\Conc\"
I2 "2022 SAI-CT-APPLE-JC.03.01"

I really hope someone can help.

Many thanks!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Ok, found the problem - on one page I2 was empty....
 
Upvote 0
Solution

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

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