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