Hi Guy's is there an easy way to loop through a folder and convert any docx files to PDF ?
Kindest
Hi Guy's is there an easy way to loop through a folder and convert any docx files to PDF ?
Kindest
Perhaps the following link will help:
https://www.extendoffice.com/documen...rt-to-pdf.html
If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
Bob Fitzpatrick
this will loop thru the source folder of doc files then convert them to PDF in the target folder param.
usage:
CvtAllWordFiles2Pdf [doc source folder], [pdf target folder]
Note: you must add Microsoft Word object library into REFERENCES.
Code:Public Sub CvtAllWordFiles2Pdf(ByVal pvSrcDir, ByVal pvTargDir) Dim fso, oFolder, oFile Dim sTargFile Dim i As Integer Dim vBaseNam 'word code Dim wrd As Word.Application Set wrd = CreateObject("word.application") wrd.Visible = True 'file code Set fso = CreateObject("Scripting.FileSystemObject") Set oFolder = fso.GetFolder(pvSrcDir) 'loop For Each oFile In oFolder.Files i = InStrRev(oFile.Name, ".doc") 'only use Word doc files If i > 0 Then vBaseNam = Left(oFile.Name, i - 1) sTargFile = pvTargDir & vBaseNam & ".pdf" Debug.Print sTargFile With wrd .Documents.Open CStr(oFile) .ActiveDocument.ExportAsFixedFormat OutputFileName:=sTargFile, ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False .ActiveDocument.Close False End With End If Next wrd.Application.Quit Set fso = Nothing Set oFolder = Nothing Set wrd = Nothing End Sub
Hi ranman, thank you, can i do this from a private sub as the source folder is from a combo box, also do i need to add the src and trag folder paths ?
the src path is the same as the targ path but the very last line of the path determines which folder, i have tried the following but it doesn't convert plus i want to kill the word doc after conversion ??? help
Also they are docx files and the references are checked in the library
So, if there is 22 pdf in the folder and 2 word docs
the 2 word docs are called PO-123456 and PO-654321.docx
can i convert these from a button (private sub) ? so there is now 24 pdf files and no docx files ? eventually i want to find a method of changing these before update because after update, it adds the record to the system adding the file name
Code:Private Sub cvtFiles(ByVal pvSrcDir, ByVal pvTargDir)Dim fso, oFolder, oFile Dim sTargFile Dim i As Integer Dim vBaseNam pvSrcDir = "T:\Folder1\Folder2\" & Me.cboFiles & "\" pvTragdir = pvSrcDir 'word code Dim wrd As Word.Application Set wrd = CreateObject("word.application") wrd.Visible = True 'file code Set fso = CreateObject("Scripting.FileSystemObject") Set oFolder = fso.GetFolder(pvSrcDir) 'loop For Each oFile In oFolder.files i = InStrRev(oFile.Name, ".docx") 'only use Word doc files If i > 0 Then vBaseNam = Left(oFile.Name, i - 1) sTargFile = pvTargDir & vBaseNam & ".pdf" Debug.Print sTargFile With wrd .Documents.Open CStr(oFile) .ActiveDocument.ExportAsFixedFormat OutputFileName:=sTargFile, ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False .ActiveDocument.Close False End With End If Next wrd.Application.Quit Set fso = Nothing Set oFolder = Nothing Set wrd = Nothing End Sub
Hi ranman, i am so close with this, can you kindly help?
I have made a small adaption to your suggestion, the 2 documents i have tested does convert, the issue is it leaves Word open on a blank file and code stalls as below
please note, i couldn't fit the snip of the code fully but the very end of the code sets wrd = Nothing etc same as your suggested code
My Code:
Error MessageCode:Private Sub Command225_Click()Dim srcPath As String, orgName As String, NewName As String Dim fso, oFile, oFolder Dim i As Integer Dim vBaseNam Dim wrd As Word.Application srcPath = "C:\Users\My Name\Desktop\MyDocs\" Set wrd = CreateObject("word.application") wrd.Visible = True Set fso = CreateObject("Scripting.FileSystemObject") Set oFolder = fso.GetFolder(srcPath) For Each oFile In oFolder.Files i = InStrRev(oFile.Name, ".docx") 'only use Word doc files If i > 0 Then orgName = Left(oFile.Name, i - 1) NewName = srcPath & orgName & ".pdf" Debug.Print NewName With wrd .Documents.Open CStr(oFile) .ActiveDocument.ExportAsFixedFormat OutputFileName:=strPath & NewName, ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False .ActiveDocument.Close False End With End If Next wrd.Application.Quit MsgBox (i & " " & "Converted") Set fso = Nothing Set oFolder = Nothing Set wrd = Nothing End Sub
Debug:
When i debug and hold the mouse over the oFile on Documents.open CStr(oFile)
The file name is $123456, is this an extension issue to post 5 ????
Something went wrong. Do you have a filename like that?
No The file name is SL-123456.docx
It copies fime to SL-123456.pdf but debugs at Documents.OpenCstr(oFile)
When the mouse is hovered over oFile, whilst in yellow debug, it says the file name is $123456.docx
I found a couple of errors in the code..... Here is the modified code. I did not encounter any errors..... (yet)
Code:' *************************************************** ' Set a reference to Microsoft Word XX.0 Object Library ' *************************************************** Private Sub Command225_Click() Dim srcPath As String Dim dstPath As String Dim orgName As String Dim NewName As String Dim fso As Variant Dim oFile As Variant Dim oFolder As Variant Dim i As Integer Dim kounter As Integer 'counter ' Dim vBaseNam '<<--variable not used Dim wrd As Word.Application 'srcPath = "C:\Users\My Name\Desktop\MyDocs\" srcPath = "D:\Forum\TestDocs\" 'path to DOCX files dstPath = "D:\Forum\TestPDF\" 'path for PDF files Set wrd = CreateObject("word.application") wrd.Visible = True Set fso = CreateObject("Scripting.FileSystemObject") Set oFolder = fso.GetFolder(srcPath) kounter = 0 For Each oFile In oFolder.Files i = InStrRev(oFile.Name, ".docx") 'only use Word doc files If i > 0 Then orgName = Left(oFile.Name, i - 1) NewName = dstPath & orgName & ".pdf" Debug.Print NewName With wrd .Documents.Open CStr(oFile) .ActiveDocument.ExportAsFixedFormat OutputFileName:=NewName, ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False .ActiveDocument.Close wdDoNotSaveChanges End With kounter = kounter + 1 End If Next wrd.Application.Quit MsgBox (kounter & " DOCX files Converted") Set fso = Nothing Set oFolder = Nothing Set wrd = Nothing End Sub
Thank you very much indeed, this works perfect, how do I check if the same file name has been converted by a user so the code doesn't convert the file if someone else has already converted ?
Also if there are no DOCX files in there, the code doesn't need to run ?
Kindest
OK, 2nd question first:
This part of the codechecks to see if a docx file exists.Code:i = InStrRev(oFile.Name, ".docx") 'only use Word doc files
So if there are 50 files in the folder, the code has to look at each of the 50 file names to see if it is a Word file. If no Word files are found, the code just ends.
'-----------------------------------------------------------------------------------------------------
Now the 1st question:
There is a method in the File Scripting Object "FileExists" that can be used to check in a PDF file exists in a folder (whatever the path is).
Here is the modified code:
Code:' *************************************************** ' Set a reference to Microsoft Word XX.0 Object Library ' *************************************************** Private Sub Command225_Click() Dim srcPath As String Dim dstPath As String Dim orgName As String Dim NewName As String Dim fso As Variant Dim oFile As Variant Dim oFolder As Variant Dim i As Integer Dim kounter As Integer 'counter Dim wrd As Object 'As Word.Application Dim FileExists As Boolean srcPath = "D:\Forum\TestDocs\" 'path to DOCX files dstPath = "D:\Forum\TestPDF\" 'path for PDF files 'srcPath = "C:\Users\My Name\Desktop\MyDocs\" Set wrd = CreateObject("word.application") wrd.Visible = True Set fso = CreateObject("Scripting.FileSystemObject") Set oFolder = fso.GetFolder(srcPath) For Each oFile In oFolder.Files i = InStrRev(oFile.Name, ".docx") 'only use Word doc files If i > 0 Then orgName = Left(oFile.Name, i - 1) NewName = dstPath & orgName & ".pdf" FileExists = fso.FileExists(NewName) If Not FileExists Then ' Debug.Print NewName With wrd .Documents.Open CStr(oFile) .ActiveDocument.ExportAsFixedFormat OutputFileName:=NewName, ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False .ActiveDocument.Close wdDoNotSaveChanges End With kounter = kounter + 1 End If End If Next wrd.Application.Quit MsgBox (kounter & " DOCX files Converted") Set fso = Nothing Set oFolder = Nothing Set wrd = Nothing End Sub