How do I export data to an existing Excel File?

[Iowa Joe]

Hi all; I have a complicated problem (or at least it is to me) that I hope
someone can shed some light on.

I have an Access database which includes a form. This form autopopulates a
chart using a temporary table which is created on a click of a button. What I
would like to do is use this temporary table called "TempTable" as data in an
excel file "Test.xls", which I use sort of as a template. The table populates
fine and the excel file works fine when you put data in it. All I need to
know is the code to take the data from my "TempTable" and input it into the
first two columns starting at the second row of my "Test.xls" file. This
problem has been driving me mad for the last two days and anyone who could
help me figure out the code to do this automatically would be great. Here is
the function I call to create a copy of my template in a new folder so far:

Option Compare Database
Option Explicit

Sub ExportChart()

Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Set myConnection = CurrentProject.Connection
myRecordSet.ActiveConnection = myConnection
myRecordSet.Open "TempTable", , adOpenDynamic
Dim xlObj As Excel.Application
Dim wkbk As Excel.Workbook
Dim SourcePath As String
Dim SourceDoc As String
Dim Sheet As Object
Dim i As Integer

SourcePath = Left(CurrentDb.Name, Len(CurrentDb.Name) -
Len(Dir(CurrentDb.Name)))
SourceDoc = SourcePath & "Test.xls"

Set xlObj = CreateObject("Excel.Application")
xlObj.DisplayAlerts = False
xlObj.WindowState = xlMaximized
Set wkbk = xlObj.Workbooks.Open(SourceDoc)
SourceDoc = "Test1newName.xls"
SourceDoc = SourcePath & "newfolder\" & SourceDoc
wkbk.SaveAs SourceDoc
wkbk.Close
xlObj.Quit
Set xlObj = Nothing

End Sub

 

[Brotha Lee]

Iowa,

You can use the copyfromrecordset command.

In your example the code will look like this:
Dim mySht as Excel.worksheetsheet

set mysht = wkbk.sheet(1)

mysht.range("a2").copyfromrecordset myRecordset

 

[George Nicholson]

.....
wkbk.SaveAs SourceDoc
wkbk.Worksheet(1).Range("A2").CopyFromRecordset myRecordset
wkbk.Save
wkbk.Close
......

If your recordset contains more than 2 columns but you only want to copy the
first 2 into Excel:
wkbk.Worksheet(1).Range("A2").CopyFromRecordset myRecordset,,2

HTH,