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!

Thanks!
David

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

KeepGoing:
 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
         
ExcelFileSave:
        FileType = 1
        
          Set OpenBook = Excel.ActiveWorkbook
       
            FName = Forms![frmMain]![ExportPath] & "\" & Forms![frmMain]![DocQMSNumber] & "_" & Forms![frmMain]![Revision] & Forms![frmMain]![ExcelExt]

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

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


OpenDocumentEnd:

  
End If


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

                
Exit_ReleaseDocumentsExport:
    Exit Function

Err_ReleaseDocumentsExport:
    
 If ExportStop = False Then
       
       Counter = Counter + 1
     
     If Counter < 3 Then
        If FileType = 1 Then
            GoTo WordFileSave
        Else
            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
  
 Else
  
        DoCmd.GoToRecord acDataForm, "frmMain", acNext
  
 End If
   
    Resume Exit_ReleaseDocumentsExport
End Function

Select allOpen in new window