Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Unanswered: open Excel workbook method fails in VBA from Access

    Hi,

    I am trying to open an Excel spreadsheet from Access using the following code in Access:

    Private mobjXL As Excel.Application

    Private Sub cmdImportFiles_Click()
    Dim wrk As Excel.Workbook
    Set mobjXL = New Excel.Application
    With mobjXL
    .ScreenUpdating = False
    .Visible = false
    .DisplayAlerts = False
    Set wrk = mobjXL.Workbooks.Open("Excel file path and name")
    ' Do something with the Excel spreadsheet
    End With
    ' error handler
    End Sub

    I keep getting an error message of 'Method 'Open' of object Workbook failed' everytime Excel tries to open the spreadsheet (and it can be any spreadsheet). Any suggestions on how to open the Excel spreadsheet in VBA without the error message?

    Regards
    Justin

  2. #2
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Try something on the lines of:

    Private objExcelApp As Excel.Application
    Private objExcelWkb As Excel.Workbook
    Private objExcelWks As Excel.Worksheet

    myPath ="c:\somewhere.xls"
    Set objExcelApp = New Excel.Application
    Set objExcelWkb = objExcelApp.Workbooks.Open(FileName:=myPath)
    Set objExcelWks = objExcelWkb.Worksheets("Sheet 1")


    I've used this code and it more or less works (you need more to actually see the workbook on screen, but you can add stuff to the workbook and save it with this).

    I seem to have declared one more object than you did, and I'm guessing that's were the problem is. (I must admit to not being that confident with this work.)

    Good luck.

  3. #3
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    I have just successfully tried this code.

    Dim wrk As Excel.Workbook
    Dim mobjXL As Excel.Application
    Set mobjXL = New Excel.Application
    With mobjXL

    .Visible = True

    Set wrk = mobjXL.Workbooks.Open("PathName")
    ' Do something with the Excel spreadsheet
    End With
    ' error handler

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    One of the important things when automating, is closing and releasing the object variables again, after they've been used. We don't see any of those. It might be that you have extra instances of Excel running providing the anomalities. Do you have any extra instances of Excel running (check Task Manager)?

    I prefer using createobject in stead of instantiating with the New keyword.
    Roy-Vidar

Posting Permissions

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