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

Posted on 2015-02-07
Last Modified: 2016-02-10
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

Question by:David Smithstein
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 74

Accepted Solution

Jeffrey Coachman earned 500 total points
ID: 40596438
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


Author Closing Comment

by:David Smithstein
ID: 40600953
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question