Using VBA to cycle through a list of embedded documents and save them to file if Word, Excel or .pdf

TASK:  I need to cycle through a list of documents which are embedded or linked in an OLE field and depending on whether the document is MS Word, MS Excel, or a .pdf use the appropriate referened .dlls to save a copy to a file location, then move onto the next document in the list and save it to file, etc, etc.

The code below is a function I am calling that opens the document in an OLE Field on a small form, and attempts to save it, depending on what type of file it is.

One of the issues i'm having is that even with an application.quit command  Excel.exe  stays running as a process and my code to check for the Excel document (Isrunning) returns a value of true as a result, so I can't  use it to figure out which type of file was just opened, and therefore which API to reference for the save command.

The other issue I have is i'm not sure I have the right Adobe references.  The client will have Adobe Ready 11 installed, as do I, and the available references looks like Adobe Acrobat 10.0 Type Library is what I need?  When I isolate the code and condition for a .pdf document,  MS Access throws the error "Object variable or With block variable not set."

Sort of a brute force strategy I though might work is to simply send it through each segment of code and see if one of them works, if not, log the document as skipped and move on.   Which brings me here, as I'm sure there's a more elegant solution to check for a file type between these three options and save the document that is open.

I can get the Word and Excel documents to save, but there seems to be an error when trying to switch from one file type to another and MS Access throws the error "Object variable or With block variable not set."  If the client has a mixture of file types, which most do, as soon as the process encounters a change in file type, the process fails to continue.

Any assistance with strategy, or syntax for specific components of the overall task would be much appreciated!


Public Function ReleaseDocumentsExport()
On Error GoTo Err_ReleaseDocumentsExport

    Dim wordDoc As Word.Document
    Dim wordAppl As Word.Application
    Dim mydoc As String
    Dim myAppl As String
    Dim myApplEx As String
    Dim myAppPDF As String
    Dim AcroApp As Acrobat.AcroApp
    Dim PDFDocument As Acrobat.AcroPDDoc
    Dim mystring As String
    Dim OpenBook As Excel.Workbook
    Dim FName As String
    Dim FileType As Integer
    Dim Counter As Integer
    myAppl = "Word.Application"
    myApplEx = "Excel.Application"
GenericCounter = GenericCounter + 1
Counter = 0

 ExportStop = False

If IsNull(Forms![frmMainDoc]![HistDocument]) = False Then

Forms![frmMainDoc]![HistDocument2] = Forms![frmMainDoc]![HistDocument]
    '-----------Opens the document --------------------------
    With Forms.Item("frmMainDoc").Controls.Item("HistDocument2")
        .Action = acOLEActivate
        .Verb = acOLEVerbOpen
    End With

    If IsRunning(AcroApp) = True Then
        If PDFDocument.Save(PDSaveFull, Forms![frmMain]![ExportPath] & "\" & Forms![frmMain]![DocQMSNumber] & "_" & Forms![frmMain]![Revision] & ".pdf") = False Then
          message ("Could not save .pdf file")
        End If
    End If

        If IsRunning(myApplEx) Then
        FileType = 1
          Set OpenBook = Excel.ActiveWorkbook
            FName = Forms![frmMain]![ExportPath] & "\" & Forms![frmMain]![DocQMSNumber] & "_" & Forms![frmMain]![Revision] & Forms![frmMain]![ExcelExt]

            OpenBook.SaveAs filename:=FName
            Wait (1)
              ExportStop = True  'No Error
       End If
    '  MsgBox "now check if Word is running"
    If IsRunning(myAppl) Then

        FileType = 2
        Word.Selection.Document.SaveAs (Forms![frmMain]![ExportPath] & "\" & Forms![frmMain]![DocQMSNumber] & "_" & Forms![frmMain]![Revision] & Forms![frmMain]![DocExt])
         ExportStop = True  'No Error
      Wait (1)
    End If


End If

    If Forms![frmMain]![UseDocNumberInPath] = 1 Then 'yes
        DoCmd.GoToRecord acDataForm, "frmMain", acNext
    End If

    Exit Function

 If ExportStop = False Then
       Counter = Counter + 1
     If Counter < 3 Then
        If FileType = 1 Then
            GoTo WordFileSave
            GoTo ExcelFileSave
        End If
     End If
        DoCmd.OpenQuery "Qry_SkippedDocExport"   'Creates a log of the document skipped so the user can go back and open/save manually.
        DoCmd.GoToRecord acDataForm, "frmMain", acNext
        DoCmd.GoToRecord acDataForm, "frmMain", acNext
 End If
    Resume Exit_ReleaseDocumentsExport
End Function

Open in new window

David SmithsteinCEOAsked:
Who is Participating?
Jeffrey CoachmanMIS LiasonCommented:
See the info here:
...and here
...and here:

This may not be as easy as you had hoped.

A while back the OLE2Disk utility worked fine for me for images.
But I am not sure how it will work now with the new Office formats (xlsx, docx, ...etc)
...and I was never abbe to get it to work with PDF's.

At the very extreme end you could try SENDKEYS after opening the file to automatically save it,..   But sendkeys is notoriously dangerous and hard to predict and control.
You would use sendkeys to simulate a "File save As" command. I believe it is something like this, ...but check the VBA help files to be sure.
    SendKeys "%FS"
...Also note that SendKeys ha a "Wait" argument that also may need to be used.
Finally, the way the processor issues commands, you may have to reverse the order in which you issue these commands.

Hope this helps

David SmithsteinCEOAuthor Commented:
Thanks Jeff,  those links were helpful, although so far I can simply identify that a .pdf is open, and shut down the process so I can keep cycling  through the list of documents.   I can run through about 60 or so spreadsheets and then Access will crash, but it was good enough to be more useful than doing it manually.   I changed my form to allow control for which file type is being extracted, as making a pass to pull out Excel docs and then doing one to pull out Word docs seems to be a more reliable strategy than trying to juggle between the two programically.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.