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
Medium Priority
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 2000 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

743 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