Data from Access to Excel

F

FGM

windows 2000 xcel/access 2003
I have the following code and am trying to export data from Access to Excel.
However, I want to be able to create worksheets for each set of data and am
sending several groups of data. Need to create x number of worksheets and
name them. Right now my problem is that I have figured out how to send x
number of records with one field but when I use all the fields in the SQL it
does not work.
help would be appreciated.... thanks.

Public Sub TransferSim()
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objQuerySheet As Excel.Worksheet
Dim objResultsSheet As Excel.Worksheet
Dim objXLRange As Excel.Range
Dim db As DAO.Database
Dim strSQL As String
Dim rst1 As Recordset
Dim varResults As Variant
Dim intCount As Integer

Set objXLBook = Workbooks.Add("P:\Templates\Braslau XLT\Simulation.xlt")
Set objXLApp = objXLBook.Parent

objXLBook.Worksheets.Add
objXLBook.ActiveSheet.Name = "test"

objXLApp.Visible = True
objXLBook.Windows(1).Visible = True
' objXLBook.Sheets.Add


' objXLBook.ActiveSheet.Name = "test"


Set db = CurrentDb
' strSQL = "SELECT
ProjectID,SourceNumber,SourceName,ReceiverNumber,RecordNumber,LapDur,DurSec
FROM tblSourceAnswers Where ProjectID = 22 ORDER BY ReceiverNumber"
strSQL = "SELECT ProjectID,ReceiverNumber,SL,SL_DurSec,SPL FROM
qrySimulationC Where ProjectID = 22 ORDER BY ReceiverNumber"


Set rst1 = db.OpenRecordset(strSQL, dbOpenDynaset)
intCount = rst1.RecordCount
'Set qdfLost = dbThis.QueryDefs("qryLostCount")

varResults = rst1.GetRows(intCount)
rst1.Close
db.Close

Set objXLRange = objXLBook.ActiveSheet. _
Range("A2:E" & 1 + intCount)

objXLRange.FormulaArray = Array(varResults)
Exit Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top