late binding - creating worksheet object

[Jeff Norville]

So we've used a little useful Access app in-house for a while and are
now building it out for a client. I'm no VBA guru, and am struggling
a bit with the late-binding conversions - seems I'm making things
harder than they should be.

1. I have a the Excel Object Model reference, check;
2. Project takes a parameterized SQL query, instantiates a new Excel
workbook, and pastes the contents into a worksheet;
3. And I think I'm Dimming the right Objects:
-----------------------------------------
Dim xlAp As Object
Dim xlWs As Object
Set xlAp = CreateObject("Excel.Application")
Set xlWs = CreateObject("Excel.Sheet")
-----------------------------------------

Seems the Application object replaces the Workbook object in my Early
Binding app, but I'm failing to open a new worksheet. Next:

-----------------------------------------
xlApp.Visible = True
' welcome to my trouble:
Set xlWs = xlApp.ActiveSheet ' <--- that sets xlWs to a null, I think
since the worksheet still needs added...
' second attempt
Set xlWs = xlApp.Sheets.Add(Type:="xlWorksheet") ' <--- that generates
an 1004: "Application-defined or object-defined error"
-----------------------------------------
From there I just step through my recordset pasting values, though I
anticipate one more problem since Cells is not a method of Sheets (but
of the Application object) ... do I just specify ActiveSheet here?

-----------------------------------------
i = 1
For Each fld In rs.Fields
xlApp.Cells(1, i).Value = fld.Name '
i = i + 1
Next fld

 

[Bob Phillips]

You'll need a workbook

Dim xlAp As Object
Dim xlWb As Object
Dim xlWs As Object

Set xlAp = CreateObject("Excel.Application")
xlAp.Visible = True
Set xlWb = xlAp.Workbooks.Add
Set xlWs = xlWb.Worksheets(1)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)