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

amb2301

New member
Joined
May 18, 2020
Messages
15
Reaction score
0
Points
0
Excel Version(s)
excel 2013
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:
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
 
Thanks Norms, I will try with this & let you know the results :)
 
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
 
could someone from forum please help me on this task
 
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
 
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
 
hi macropod,
sorry for this happening (cross-posting) , i will make sure it will not happening again in the forum.
 
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.
 
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.


Hi NormS,
I Tried modified this line, it worked as expected :)
HTML:
  worddoc.ExportAsFixedFormat fo.Path & Application.PathSeparator & VBA.Replace(f.Name, ".docx", ".pdf"), wdExportFormatPDF
 
Now instead of that, converted pdf files to be saved in the respective sub-folders itself....
The code I posted at ExcelForum already does that... I see no evidence you've even bothered to try that code.
 
Could you cross-post your Word VBA solution here, Paul, and save me the trouble of creating an account on Excelforum?
 
The code I posted at ExcelForum already does that... I see no evidence you've even bothered to try that code.

i m extremely sorry macropod, i just noticed your code there..
Actually i seen the Admin message(warning message) in excel forum for the cross-post, so i replied admin & missed to see your reply to me...
Now i just replied to that thread.

i m sorry for troubling you macropod

Hi NormS,
here is the link & thank you too for your codes, you guys are great
https://www.excelforum.com/excel-pr...f-for-folders-and-subfolders.html#post5342800

HTML:
Option Explicit 
'Re-used & shared variables
Dim FSO As Object, oFolder As Object, oSubFolder As Object, oFiles As Object, oItem As Object
  
Sub CreateDocPDFs()
Application.ScreenUpdating = False
Application.DisplayAlerts = wdAlertsNone
WordBasic.DisableAutoMacros True
Dim StrFolder As String
' Browse for the starting folder
StrFolder = GetTopFolder
If StrFolder = "" Then
  MsgBox "No document folder selected", vbExclamation
  Exit Sub
End If
' Search the top-level folder
Call ProcessFolder(StrFolder & "\")
' Search the subfolders for more files
Call SearchSubFolders(StrFolder)
' Return control of status bar to Word
Application.StatusBar = ""
WordBasic.DisableAutoMacros False
Application.DisplayAlerts = wdAlertsAll
Application.ScreenUpdating = True
MsgBox "Done!", vbExclamation
End Sub
  
Function GetTopFolder() As String
GetTopFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetTopFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
  
Sub SearchSubFolders(strStartPath As String)
If FSO Is Nothing Then
  Set FSO = CreateObject("scripting.filesystemobject")
End If
Set oFolder = FSO.GetFolder(strStartPath)
Set oSubFolder = oFolder.subfolders
For Each oFolder In oSubFolder
  Set oFiles = oFolder.Files
  ' Search the current folder
  Call ProcessFolder(oFolder.Path & "\")
  ' Call ourself to see if there are subfolders below
  SearchSubFolders oFolder.Path
Next
End Sub
  
Sub ProcessFolder(StrFolder As String)
Dim strFile As String, wdDoc As Document
strFile = Dir(StrFolder & "*.doc")
' Process the files in the folder
While strFile <> ""
  ' Update the status bar is just to let us know where we are
  Application.StatusBar = StrFolder & strFile
  Set wdDoc = Documents.Open(StrFolder & strFile, AddToRecentFiles:=False, ReadOnly:=False, Format:=wdOpenFormatAuto, Visible:=False)
  With wdDoc
    .SaveAs2 FileName:=Split(.FullName, ".doc")(0) & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    .Close SaveChanges:=False
  End With
  strFile = Dir()
Wend
Set wdDoc = Nothing End Sub
 
Last edited:
Could you cross-post your Word VBA solution here, Paul, and save me the trouble of creating an account on Excelforum?
There is no need to create an account there just to copy code from the body of a post...
 
Back
Top