Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation Excel from Access (Access 2k SR-1)

    Trying OLE automation into Excel from code within Access. Have code that extracts data from 2 sheets within a workbook. It works well except for those workbooks that have been hidden. When the workbook is opened using Excel and set 'unhiden' and the file is saved in the unhidden state, the code works without error. This error, btw, is not trapable from the Access code.

    'The following is an excerpt from the code.

    Public Function pexExtractFromTimesheets(objXLApp As Excel.Application, _
    strTSPath As String, _
    lngEmpNo As Long) As Long

    Dim objXLBook As Excel.Workbook
    Dim objWrkSheet As Excel.Worksheet

    On Error GoTo HandleErr

    Set objXLBook = objXLApp.Workbooks.Open(strTSPath)

    '-- Extract the entered start - finish times
    Set objWrkSheet = objXLBook.Worksheets("Sheet-1")
    objWrkSheet.Visible = xlSheetVisible
    'call to extract code here

    '-- Extract times spent on activities
    Set objWrkSheet = objXLBook.Worksheets("Sheet-2")
    objWrkSheet.Visible = xlSheetVisible
    'call to extract code here

    Set objWrkSheet = Nothing
    Set objXLBook = Nothing
    objXLBook.Close

    ExitHere:

    pexExtractFromTimesheets = True

    Exit Function
    ----------------------------------------------------
    The error:
    Run-time error '1004':
    Method 'Range' of object '_Global' Failed

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Excel from Access (Access 2k SR-1)

    I had a similar error (1004) apparently in code but eventually discovered it was not the code at all but one of the ranges I had set in Excel itself. There was a typing error. Check the ranges you are calling in XL and make sure they are correct.
    I can't guarantee this is your problem of course but it's worth a look.
    Peter

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation Excel from Access (Access 2k SR-1)

    At this stage I don't think it's the content or structure of the worksheet. I suspect that it has to do with Automation. The error occurs on this line:

    Set objXLBook = objXLApp.Workbooks.Open(strTSPath)

    There is no error when you open the hidden workbook using Excel. However, when attempting to open this file (strTSPath) from Access, there is an error (dialog box on the screen) that has to be responded to. This dialog occurs regardless of the state of the Excel application object properties (Visible and/or DispalyAlerts). I suspect that the error is being presented from the Excel application object as it not able to be trapped (On Error ...) within Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •