Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    440 automation error

    This is a continuation of: https://www.ozgrid.com/forum/forum/h...o-save-changes

    The error seems to be on this line:

    Code:
    xl.Run "ThisWorkbook.BetterExcelDataToWord"
    However. If I run that same code from inside excel (not from access) there are no issues.

    How can I find whats causing the problem here?



    Andy.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is the exact error message? Post the Access procedure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    440 automation error is the exact error message.

    Full vba shown below.

    Code:
    Private Sub Command7_Click()
    
        On Error GoTo Error_Handle
    Dim FSO
    Dim sFile As String
    
    Dim sDFolder As String
    Dim sNewFile  As String
    Dim answer As Integer
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "update_RAMS_issue", acViewNormal, acEdit
    
    If Nz(Me.lstFileList, "") = "" Then
         MsgBox "pick a template."
         Me.Refresh
         Me.Requery
         
         Exit Sub
    End If
    If IsNull([txtProject]) Or [txtProject] = "" Then
         MsgBox "Please write a project."
         Me.Refresh
         Me.Requery
         
         Exit Sub
    End If
    
    
    Dim xl As Object
     
    'Step 1:  Start Excel, then open the target workbook.
       Set xl = CreateObject("Excel.Application")
       
       xl.Workbooks.Open ("\\SERVER\general\Documents\!Management\VBA_Templates_do_not_modify\HAZARDS\HAZARDS.xlsm")
         xl.activeworkbook.refreshall
    
    
    'File Name to Copy
    sFile = Me.lstFileList
    
    ' New File Name
    sNewFile = "RAM_" & Me.Site_Name & "_" & Me.Site_ID & "_" & Me.RAMS_ISSUE & ".docx"
    
    'destination folder path
    sDFolder = "\\SERVER\general\RAMS\RAM_RAMS\" & sNewFile
    
    Application.Echo False
    
    '-------msgbox for rams-----
    
    answer = MsgBox("Make RAMS for " & Me.Site_Name & "?", vbYesNo + vbQuestion, "Empty Sheet")
    If answer = vbYes Then
    
    Else
        Exit Sub
    End If
        
    '-------msgbox for rams end-----
    
    'Create Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'Checking If File Is Located in the Source Folder
    If Not FSO.FileExists(sFile) Then
        MsgBox "Specified File Not Found", vbInformation, "Not Found"
        
    'Copying If the Same File is Not Located in the Destination Folder
    ElseIf Not FSO.FileExists(sDFolder) Then
        FSO.CopyFile (sFile), sDFolder, True
     
    Else
        MsgBox "Specified File Already Exists In The Destination Folder", vbExclamation, "File Already Exists"
        Exit Sub
    End If
    
    '-----------------CREATE FILE END-----------------
    '-----------------AMMEND FILE-----------------
    
    
        '-----------------Set up word document to use recordset ------------------
        Dim wrdApp As Object
        Dim wrdDoc As Object
        Dim filepath As String
        Dim docFile As String
        
     ''''''''''''''   Dim rst As DAO.Recordset
        
       
        '-----------------Set up word document to use recordset end------------------
    
    
    
        '------------------ define query -----------------------------
        Dim db As Database
        Dim sSql As String
    
    
        Set db = CurrentDb
    
    
        sSql = "SELECT SiteT.Site_ID, SiteT.Site_Name, SiteT.Asset_Type, SiteT.Address_1, SiteT.Address_2, SiteT.Address_3, SiteT.Postcode, ClientT.Company_Name, ClientT.Company_ID, HospitalT.Hospital_Name, HospitalT.Hospital_Postcode, HospitalT.Hospital_Address, HospitalT.Hospital_Telephone, SiteT.lat, SiteT.long, SiteT.Rams_Issue "
        sSql = sSql & "FROM HospitalT INNER JOIN (ClientT INNER JOIN SiteT ON ClientT.Company_ID = SiteT.Site_Owner) ON HospitalT.Hospital_ID = SiteT.Hospital_ID "
        sSql = sSql & "WHERE [SiteT].[Site_ID] = " & Me.Site_ID & " "
        sSql = sSql & "ORDER BY SiteT.Site_Name;"
    
     Dim rst As DAO.Recordset
        Set rst = db.OpenRecordset(sSql)
    
    
    
        filepath = sDFolder
    
    
    
       Set wrdApp = CreateObject("Word.Application")
       wrdApp.Visible = False
    
    
        Set wrdDoc = wrdApp.Documents.Open(filepath)
    
    
        With wrdDoc
    
            wrdApp.ActiveDocument.Bookmarks("Date_Compiled").Select
            wrdApp.Selection.Text = Date
    
            wrdApp.ActiveDocument.Bookmarks("Doc_No").Select
            wrdApp.Selection.Text = rst("Site_ID") & "_" & rst("Rams_Issue")
            
            wrdApp.ActiveDocument.Bookmarks("hospital_details").Select
            wrdApp.Selection.Text = rst("Hospital_Name") & vbCrLf & rst("Hospital_Address") & vbCrLf & rst("Hospital_Postcode") & vbCrLf & rst("Hospital_Telephone")
    
            wrdApp.ActiveDocument.Bookmarks("site_details").Select
            wrdApp.Selection.Text = rst("Site_Name") & vbCrLf & rst("Address_1") & vbCrLf & rst("Address_2") & vbCrLf & rst("Address_3") & vbCrLf & rst("Postcode")
          
            wrdApp.ActiveDocument.Bookmarks("Site_Name1").Select
            wrdApp.Selection.Text = rst("Site_Name")
    
            wrdApp.ActiveDocument.Bookmarks("Site_Name_Postcode").Select
            wrdApp.Selection.Text = rst("Site_Name") & vbCrLf & rst("Postcode")
    
            wrdApp.ActiveDocument.Bookmarks("User").Select
            wrdApp.Selection.Text = "test"
    
            wrdApp.ActiveDocument.Bookmarks("User_Long").Select
            wrdApp.Selection.Text = "test"
    
            wrdApp.ActiveDocument.Bookmarks("User_Long_title").Select
            wrdApp.Selection.Text = " - Systems Manager"
            
            wrdApp.ActiveDocument.Bookmarks("Date_Compiled1").Select
            wrdApp.Selection.Text = Date
            
            'wrdApp.ActiveDocument.Bookmarks("Date_Compiled3").Select
            'wrdApp.Selection.Text = Date
            
    
            
            wrdApp.ActiveDocument.Bookmarks("Doc_No1").Select
            wrdApp.Selection.Text = rst("Site_ID") & "_" & rst("Rams_Issue")
            
            wrdApp.ActiveDocument.Bookmarks("Doc_No2").Select
            wrdApp.Selection.Text = rst("Site_ID") & "_" & rst("Rams_Issue")
      
            wrdApp.ActiveDocument.Bookmarks("site_details1").Select
            wrdApp.Selection.Text = rst("Site_Name") & vbCrLf & rst("Address_1") & vbCrLf & rst("Address_2") & vbCrLf & rst("Address_3") & vbCrLf & rst("Postcode")
         
            wrdApp.ActiveDocument.Bookmarks("CompanyAndProject").Select
            wrdApp.Selection.Text = rst("company_name") & "_" & Me.txtProject
            
            
            wrdApp.ActiveDocument.Bookmarks("Project").Select
            wrdApp.Selection.Text = Me.txtProject
         
            wrdApp.ActiveDocument.Bookmarks("Project1").Select
            wrdApp.Selection.Text = Me.txtProject
      
         
        'Dim test As String
        'test = Me.Project
        'Debug.Print test
        
    wrdApp.ActiveDocument.Save
    wrdApp.ActiveDocument.Close
    
        End With
     '   Set rst = Nothing
        
    Application.Echo True
    DoCmd.SetWarnings True
    
    
     MsgBox "RAMS are saved: " & sDFolder, vbInformation, "Done!"
    
    Me.Refresh
    Me.Requery
    
    
    '---------------------------------------------------RISKS-----------------------------------------
    
    
        
        '------------------------------paste path to excel-----------------------------------
        
      
       
        xl.worksheets("PasteSpecial").Activate
        xl.Range("i1").Value = sNewFile
        
        '------------------------------paste path to excel-----------------------------------
     
     
    'Step 2:  Make Excel visible
     '  xl.Visible = True
    
    
      
        'xl.Run "ThisWorkbook.datarefresh"
        xl.Run "ThisWorkbook.BetterExcelDataToWord"
     
    'Step 4:  Close and save the workbook, then close Excel
       'xl.activeworkbook.Close (True)
       'xl.Quit
     
    'Step 5:  Memory Clean up.
       'Set xl = Nothing
    
    
    '---------------------------------------------------RISKS-----------------------------------------
    Exit Sub
    Error_Handle:
    
       MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description
    
    
    
    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So you run this code in Access to do some setup of Word and Excel then want Excel to run its procedure.

    Can't put that procedure in Access VBA to continue with the manipulation of Excel and Word?

    If you comment the On Error GoTo line, might get a more informative error message when the code breaks.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    confirmed with the debug that it is indeed this line causing the error:

    Code:
        xl.Run "ThisWorkbook.BetterExcelDataToWord"
    onky when "BetterExcelDataToWord" is ran from access.

    I did initially try to have this all coded within access whilst still using excel, but encountered problems (cant remember the specifics it was a while ago).

    Edit: just to be sure ive ran it again from inside excel with no problems. I dont know what else to do apart from just ignore it.

    I've read in a few places that it could be a registry issue. Our ICT support wont allow us to use any registry cleaner (cutting out that option for the time being unless i can prove its definatly that).

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe need a workbook object.

    Code:
    Sub runExcelProcFromAccess()
        Dim xlObj As Excel.Application, wkbk As Excel.Workbook
        Set xlObj = CreateObject("Excel.Application")
        Set wkbk = xlObj.Workbooks.Open("c:\somedir\yourwkbk.xls")
        wkbk.Application.Run "subSuchnSuch"
        wkbk.Close
        xlObj.Quit
        Set xlObj = Nothing
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I appreciate the help. I think you are correct.

    I'm having trouble implementing it.

    Here are the parts of code dealing with excel:

    Code:
    im xl As Excel.Application, wkbk As Excel.Workbook
     
    'Step 1:  Start Excel, then open the target workbook.
       Set xl = CreateObject("Excel.Application")
       
     Set wkbk = xl.Workbooks.Open("\\SERVER\general\Documents\!Management\VBA_Templates_do_not_modify\HAZARDS\HAZARDS - AH.xlsm")
     
         xl.activeworkbook.refreshall
    
     xl.worksheets("PasteSpecial").Activate
        xl.Range("i1").Value = sNewFile
     wkbk.Application.Run "BetterExcelDataToWord"
    
    I'm getting an error saying this macro is not available in the workbook. I'll keep digging but if you have a suggestion that would be great.

  8. #8
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    So... to fix that last error i moved the vba in excel to a module rather than a work sheet. But now Im back at square one with a 440 error. a different message this time:

    Code:
    run-time error '440':
    Method 'Run' of object '_application' failed
    At least the thread title on here is still correct I guess..

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Example code in post 6 works for me (Except for typo, I really opened an xlsm workbook).

    You should test that code to call a procedure that just executes a MsgBox. If that works then attempt expanding code with the other actions you want.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Thanks for all the help. I'll do as you suggested.

  11. #11
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Okay, I have some time today to work on this. Using your example on a new excel document.

    (just trying to open a message box). this generates error 1004 : cannot run the macro'test' the macro may not be avaiable in this workbook or all macros may be disabled.

    I've put a button within this excel file to run the mesage box and that works. (I've also made sure its closed properly before trying in access).

    I believe the issue is excel doesnt open. It's running in the background.. I have to go to task manager to end the process or it says the program is beign run by another user (me).

    Ill investigate/uptate shortly.

  12. #12
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Maybe this is beyond the scope of the origional post.. I can make a new one if required.

    Code in access:
    Code:
    Private Sub Command22_Click()
    
    On Error GoTo Error_Handle
    
        Dim xlObj As Excel.Application, wkbk As Excel.Workbook
        Set xlObj = CreateObject("Excel.Application")
        Set wkbk = xlObj.Workbooks.Open("\\server\general\Documents\!Management\VBA_Templates_do_not_modify\HAZARDS\test.xlsm")
        wkbk.Application.Run "test"
        wkbk.Close
        xlObj.Quit
        Set xlObj = Nothing
        
    Error_Handle:
    
       MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description
    
    End Sub
    code in excel:

    Code:
    Sub test()
    MsgBox "sucessful test"
    End Sub
    The code is located in a module not a work sheet. The code works when ran from a button within excel.

  13. #13
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    almost cracked it.... (incase anyones looking to reply) seems like the lack of visability is the issue when running code. Ill update with the solution.

  14. #14
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    It was a visability issue. Excel didnt like to run the macro from access without excel being visable. (I'd rather it hidden but at least this works).

    Thanks june for the help troubleshooting.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Interesting, my code worked without setting Excel visible.

    Although I do see Excel display in the app bar when the MsgBox pops up. It goes away as soon as I OK the MsgBox. So I tried code that set value of cell, and it also works.

    Glad you have it working anyway.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. automation error message
    By Jen0dorf in forum Access
    Replies: 11
    Last Post: 12-10-2015, 11:39 AM
  2. Automation Error from form
    By gemadan96 in forum Forms
    Replies: 5
    Last Post: 05-21-2014, 12:27 PM
  3. IE Automation Error
    By bucko_oz in forum Programming
    Replies: 1
    Last Post: 09-19-2010, 11:28 PM
  4. automation error
    By ashiers in forum Forms
    Replies: 0
    Last Post: 04-16-2009, 11:38 AM
  5. Automation Error
    By aouellette in forum Forms
    Replies: 0
    Last Post: 09-12-2008, 08:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums