Can not convert xls file to PDF via VBA

Suriel Liao 0 Reputation points
2023-11-25T16:32:27.33+00:00

I created a marco for converting multiple xls file to pdf. It had worked for over 2 years but it can not work from this week... When I run it shown error " documents not saved". Is there any one can help me ?

Sub EXCELtoPDF()


    Dim MyPath, MyName, PDFName As String
    Dim sFileName As String
    
    
    Dim isPrintHideSheet

    MyPath = ThisWorkbook.Path & "\"

    MyName = Dir(MyPath & "*.xls") 'Get file name
    
     isPrintHideSheet = 0 'No need to print hidden sheet

    Application.ScreenUpdating = False

    Do While MyName <> ""

        If MyName <> ThisWorkbook.Name Then

            Set wb = GetObject(MyPath & MyName) 
            
        
            If isPrintHideSheet >= 1 Then

               For i = 1 To wb.Worksheets.Count

                   wb.Worksheets(i).Visible = 1 

               Next
               
            End If
            

            
            PDFName = Left(MyName, Application.WorksheetFunction.Find(".", MyName) - 1)
            
            sFileName = MyPath & PDFName & ".pdf"
            
            Debug.Print sFileName


            wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            
            Debug.Print PDFName

            wb.Close False

            Set wb = Nothing

        End If

        MyName = Dir

    Loop

    Application.ScreenUpdating = True

    MsgBox "Completed"

End Sub
Microsoft 365 and Office | Development | Other
Microsoft 365 and Office | Excel | For business | Windows
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.