Solved

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
2
602 Views
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!

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

Open in new window

0
Comment
Question by:David Smithstein
2 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40596438
See the info here:
http://www.experts-exchange.com/Database/MS_Access/Q_28604716.html
...and here
http://www.experts-exchange.com/Database/MS_Access/Q_28604716.html#a40573620
...and here:
http://www.experts-exchange.com/Database/MS_Access/Q_28604716.html#a40577723

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

JeffCoachman
0
 

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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now