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

Topic

New Reply

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!

Reply | Quote

Replies

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)

Reply | Quote

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

Reply | Quote

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

xlSheet.Range("A1").Select
Set rngRangeToSelect = ActiveCell.CurrentRegion

use

Set rngRangeToSelect = xlSheet.Range("A1").CurrentRegion

This works, provided rngRangeToSelect has been declared as Excel.Range (or as Object).

Reply | Quote

Thank you Hans! and goodbye brick wall

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

Dim rngRangeToSelect as Range BADBADBAD!

instead of

Dim rngRangeToSelect as Excel.Range

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

Reply | Quote