Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel automation from Access (Access and Excel 2003 sp 2)

    Hi - I have code which works fine in Excel but when I try it from Access i get an error "ActiveX component cannot create object".

    In Access I have a project reference set to Excel and I'm using early binding. All I want to do is transfer the results of a query to a worksheet and then open the sheet in Excel and set a range object.

    Here is the code:
    Private Sub butOpenInExcel_Click()
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim strFilePath As String
    Dim fso As FileSystemObject
    Dim rngStartCell As Range

    Set xlApp = CreateObject("Excel.Application")
    strFilePath = xlApp.DefaultFilePath & "Calls.xls"

    Set fso = CreateObject("Scripting.FileSystemObject")
    With fso
    If .FileExists(strFilePath) Then
    .DeleteFile (strFilePath)
    End If
    End With

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCustomReport", strFilePath, True

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True

    strFilePath = xlApp.DefaultFilePath & "Calls.xls"

    xlApp.Workbooks.Open strFilePath
    Set xlBook = xlApp.ActiveWorkbook
    Set xlSheet = xlBook.ActiveSheet

    'NO PROBLEM THIS FAR, BUT NOW I CAN'T SET A RANGE
    'I"VE TRIED LOTS OF CODE BUT NOTHING WORKS, E.G.
    xlsheet.Range("A1").Select
    set rngStartCell = selection ' ERROR "ActiveX component cannot create object".
    ' OR
    set rngStartCell = xlsheet.Cells(1,1) 'ERROR "Application defined or object-defined error"

    End Sub

    Any idea greatly appreciated! <img src=/S/eargear.gif border=0 alt=eargear width=20 height=20> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel automation from Access (Access and Excel 2003 sp 2)

    The problem is that you use Selection without specifying what it belongs to. Change the line

    Set rngStartCell = Selection

    to

    Set rngStartCell = xlApp.Selection

    Some remarks:
    - There is no need to create an Excel.Application object twice.
    - There is no need to assign strFilePath twice.
    - You can combine the lines

    xlApp.Workbooks.Open strFilePath
    Set xlBook = xlApp.ActiveWorkbook

    into one:

    Set xlBook = xlApp.Workbooks.Open(strFilePath)

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel automation from Access (Access and Excel 2003 sp 2)

    Thank you Hans, especially for elegant "Set xlBook = xlApp.Workbooks.Open(strFilePath)" . Apologies the duplicate code - i sloppily edited more complicated code trying to illustrate my problem. What I *really* need <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23> is an AutoFilter and some column widths formatted, but for the life of me I can't do the simplest things like assign a range object <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> ... will you look at this with your eagle eye? Everything is inside "With xlApp" so why doesn't it work? I've tried lots of variations on the Set statement to set the range variable, but no joy at all.

    .
    .
    With xlApp
    .Visible = True
    Set xlBook = .Workbooks.Open(strFilePath)
    Set xlSheet = xlBook.ActiveSheet
    xlSheet.Range("A1").Select
    'THE NEXT LINE GIVES "TYPE MISMATCH" AS THO TRYING TO ASSIGN A STRING TO A RANGE OBJECT
    Set rngRangeToSelect = ActiveCell.CurrentRegion
    End With 'xlApp
    .
    .

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel automation from Access (Access and Excel 2003 sp 2)

    Once again, you use an Excel object without specifying what it belongs to, this time ActiveCell.
    In general, it isn't necessary to select cells in Excel VBA code. Instead of
    <code>
    xlSheet.Range("A1").Select
    Set rngRangeToSelect = ActiveCell.CurrentRegion
    </code>
    use
    <code>
    Set rngRangeToSelect = xlSheet.Range("A1").CurrentRegion
    </code>
    This works, provided rngRangeToSelect has been declared as Excel.Range (or as Object).

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel automation from Access (Access and Excel 2003 sp 2)

    <big>Thank you Hans! and goodbye brick wall </big> <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Nothing I tried would work! I spent HOURS and it was all because I went

    <font face="Georgia">Dim rngRangeToSelect as Range </font face=georgia> BADBADBAD!

    instead of

    <font face="Georgia">Dim rngRangeToSelect as Excel.Range </font face=georgia>

    At last I can start making progress. Thank you again.

Posting Permissions

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