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
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,715 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
0 comments No comments
{count} votes