Results 1 to 11 of 11
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Convert all word docs to PDF

    Hi Guy's is there an easy way to loop through a folder and convert any docx files to PDF ?



    Kindest

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    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

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    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

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    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

  5. #5
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    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:
    Code:
    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
    Error Message
    Click image for larger version. 

Name:	Error Message.JPG 
Views:	19 
Size:	14.4 KB 
ID:	42350

    Debug:

    Click image for larger version. 

Name:	Debug.JPG 
Views:	19 
Size:	60.8 KB 
ID:	42351

  6. #6
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    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 ????

  7. #7
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Something went wrong. Do you have a filename like that?

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    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

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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

  10. #10
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    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

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, 2nd question first:
    Quote Originally Posted by DMT Dave View Post
    Also if there are no DOCX files in there, the code doesn't need to run ?
    This part of the code
    Code:
            i = InStrRev(oFile.Name, ".docx")   'only use Word doc files
    checks to see if a docx file exists.
    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:

    Quote Originally Posted by DMT Dave View Post
    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 ?
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. storing and accessing word docs in a table?
    By baronqueefington in forum Access
    Replies: 1
    Last Post: 07-04-2015, 07:27 AM
  2. Import Word Docs as Form Template
    By Spectre50 in forum Forms
    Replies: 0
    Last Post: 03-05-2012, 01:51 PM
  3. merging check box information into Word Docs
    By LeesKeys in forum Access
    Replies: 1
    Last Post: 10-19-2011, 03:37 PM
  4. Creating Links to Word Docs
    By kingharvest in forum Access
    Replies: 1
    Last Post: 05-19-2010, 10:28 PM
  5. word docs.
    By laqsb in forum Access
    Replies: 1
    Last Post: 02-26-2010, 08:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums