Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: VBA required to convert WORD to PDF for folders and its subfolders

  1. #1
    Seeker amb2301's Avatar
    Join Date
    May 2020
    Posts
    15
    Articles
    0
    Excel Version
    excel 2013

    VBA required to convert WORD to PDF for folders and its subfolders



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Friends,
    i have a task to convert lot of word files to PDF files , i found some code which does the convertion correctly but its not happening for the Subfolders inside the folder
    coud someone helpme to fix it.

    HTML Code:
    Option Explicit
    
    Sub Word_To_PDF()
    
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    
    Dim fso As New FileSystemObject
    Dim fo As Folder
    Dim f As File
    
    Dim wb As Workbook
    Dim n As Integer
    
    Dim wordapp As New Word.Application
    Dim worddoc As Word.Document
    
    Set fo = fso.GetFolder(sh.Range("E13").Value)
    
    For Each f In fo.Files
    n = n + 1
    Application.StatusBar = "Processing..." & n & "/" & fo.Files.Count
    
    Set worddoc = wordapp.Documents.Open(f.Path)
    
    worddoc.ExportAsFixedFormat sh.Range("E14").Value & Application.PathSeparator & VBA.Replace(f.Name, ".docx", ".pdf"), wdExportFormatPDF
    worddoc.Close False
    Next
    
    Application.StatusBar = ""
    MsgBox "Process Completed"
    
    End Sub

  2. #2
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus

  3. #3
    Seeker amb2301's Avatar
    Join Date
    May 2020
    Posts
    15
    Articles
    0
    Excel Version
    excel 2013
    Thanks Norms, I will try with this & let you know the results

  4. #4
    Seeker amb2301's Avatar
    Join Date
    May 2020
    Posts
    15
    Articles
    0
    Excel Version
    excel 2013
    Hi Norms,
    i cant able to understand that script as i am new to VBA, could you please help me to complete my task, it will helpful for my learning..

    Thanks

  5. #5
    Seeker amb2301's Avatar
    Join Date
    May 2020
    Posts
    15
    Articles
    0
    Excel Version
    excel 2013
    could someone from forum please help me on this task

  6. #6
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    120
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Ok, try this

    Code:
    Option Explicit
    
    
    Sub Word_To_PDF()
    
    
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    
    
    Dim fso As New FileSystemObject
    Dim fo As Folder
    
    
    Set fo = fso.GetFolder(sh.Range("E13").Value)
    
    
    CreatePDF fo
    
    
    Application.StatusBar = ""
    MsgBox "Process Completed"
    
    
    Set fso = Nothing
    
    
    End Sub
    
    
    
    
    Sub CreatePDF(fo As Scripting.Folder)
    
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    
    
    Dim Subfol As Scripting.Folder
    Dim f As Scripting.File
    Dim n As Integer
    
    
    Dim wordapp As New Word.Application
    Dim worddoc As Word.Document
    
    
    For Each f In fo.Files
        n = n + 1
        Application.StatusBar = "Processing..." & n & "/" & fo.Files.Count & " in folder " & fo.Name
        
        If Right(f.Name, 4) = "docx" Then
            Set worddoc = wordapp.Documents.Open(f.Path)
            
            worddoc.ExportAsFixedFormat sh.Range("E14").Value & Application.PathSeparator & VBA.Replace(f.Name, ".docx", ".pdf"), wdExportFormatPDF
            worddoc.Close False
        End If
    Next
    
    
    For Each Subfol In fo.SubFolders
        CreatePDF Subfol
    Next
    
    
    End Sub

  7. #7
    Seeker amb2301's Avatar
    Join Date
    May 2020
    Posts
    15
    Articles
    0
    Excel Version
    excel 2013
    Quote Originally Posted by NormS View Post
    Ok, try this

    Code:
    Option Explicit
    
    
    Sub Word_To_PDF()
    
    
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    
    
    Dim fso As New FileSystemObject
    Dim fo As Folder
    
    
    Set fo = fso.GetFolder(sh.Range("E13").Value)
    
    
    CreatePDF fo
    
    
    Application.StatusBar = ""
    MsgBox "Process Completed"
    
    
    Set fso = Nothing
    
    
    End Sub
    
    
    
    
    Sub CreatePDF(fo As Scripting.Folder)
    
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    
    
    Dim Subfol As Scripting.Folder
    Dim f As Scripting.File
    Dim n As Integer
    
    
    Dim wordapp As New Word.Application
    Dim worddoc As Word.Document
    
    
    For Each f In fo.Files
        n = n + 1
        Application.StatusBar = "Processing..." & n & "/" & fo.Files.Count & " in folder " & fo.Name
        
        If Right(f.Name, 4) = "docx" Then
            Set worddoc = wordapp.Documents.Open(f.Path)
            
            worddoc.ExportAsFixedFormat sh.Range("E14").Value & Application.PathSeparator & VBA.Replace(f.Name, ".docx", ".pdf"), wdExportFormatPDF
            worddoc.Close False
        End If
    Next
    
    
    For Each Subfol In fo.SubFolders
        CreatePDF Subfol
    Next
    
    
    End Sub


    Hi Norms, Thanks you so much for your help it worked like a charm
    i m so much thankfull to you

  8. #8
    Acolyte macropod's Avatar
    Join Date
    Mar 2017
    Posts
    57
    Articles
    0
    Excel Version
    2010
    Cross-posted at: https://www.excelforum.com/excel-pro...ubfolders.html
    For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    Seeker amb2301's Avatar
    Join Date
    May 2020
    Posts
    15
    Articles
    0
    Excel Version
    excel 2013
    hi macropod,
    sorry for this happening (cross-posting) , i will make sure it will not happening again in the forum.

  10. #10
    Seeker amb2301's Avatar
    Join Date
    May 2020
    Posts
    15
    Articles
    0
    Excel Version
    excel 2013
    Hi NormS,
    i need a small modification in the code, with your latest script everything is working fine,
    Actually all the converted pdf files are saved in the same path (D:/TEST/testfiles) as per the range given in E14 cell,
    even the sub-folders converted files also getting saved into same path (D:/TEST/testfiles).

    Now instead of that, converted pdf files to be saved in the respective sub-folders itself....
    could you please help me..

    Thanks in Advance.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •