outputto new worksheet in existing workbook?

[servoss]

So, it seems both outputto and transferdatabase both write to an Excel file just fine, but is there any way to write table data to a new worksheet within an existing workbook? I want to save several tables to the same Excel file, but am not seeing clearly how to do this. Is it even possible? I've searched a bit with no final "ah ha".

Thanks so much for your help.

Tom

 

[boblarson]

The only way I know of, is to use the Excel Object Model to directly create the new worksheet and enter the data.

 

[servoss]

Easy way to create an mdb file, then?

An alternate approach would be to create an mdb file and then export the data to individual tables within that file.

Again, I've searched for file creation and haven't had much luck. I assume there is a fairly straightforward way to create an mdb file, but am not sure what that is. Any thoughts?

Thanks.

 

[boblarson]

If you're wanting to put the data within an existing Excel file on a new tab, then using the Excel Object model is the only way I know of to make it work. To do so you would do something like this:

to use THIS code, you must set a reference to Excel in references first. You can also use the CreateObject code to do late binding which won't require the reference be set in advance. I use the early binding method when possible to make it easier to use intellisense when writing the code, as late binding won't use intellisense.

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim intCount As Integer

Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\Temp\YourFileName.xls")
Set xlWS = xlWB.Worksheets.Add

xlWS.Name = "Whatever"

Set rs = New ADODB.Recordset
rs.Open "YourTableOrQueryNameHere", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
intCount = 1
xlWS.Range("A1").Select
Do Until rs.EOF
    xlWS.Range("A" & CStr(intCount)) = rs(1)
    intCount = intCount + 1
    rs.MoveNext
Loop

xlWB.Close
xlApp.Quit
Set xlWB = Nothing
Set xlApp = Nothing

Now, just remember that this is just a simple sample. There are also ways to use the range object and fill it with an array (which I am not experienced with).

 

[servoss]

Thanks, but...

I'd like to not have to rely on someone having Excel if I can get away from it.

Anyway, I appreciate the code and will definitely give it a try in case I need to use this approach.

Thanks, again.

Tom

 

[boblarson]

If they don't have Excel, the things you noted in your first post will not work. If you do not have Excel, you won't be able to have Access put anything into an Excel Workbook.

 

[servoss]

care to chat via yahoo IM?

care to chat via yahoo IM?

 

[ted.martin]

Bob, what is the reason you have used ADODB instead of DAO? (running Access 2003). thanks

 

[boblarson]

Bob, what is the reason you have used ADODB instead of DAO? (running Access 2003). thanks

At the time I posted this (2006) I really was more familiar with ADO than DAO. Also, I would revise it now because at the time I didn't have a clue about the CopyFromRecordset ability.

 

[ted.martin]

Thanks Bob - CopyFromRecordset is a new one for me too but I see there is stuff on MSDN to help. Regards