Send data from Access to Excel

[Guest]

windows 2000 excel / access 2002

Trying to send several groups of data to excel. Want to be able to create
and name the worksheets from the program and put the data on the worksheet.
I have been able to send one field but not all the fields.. I think I do not
know how to work the objXLRange.FormulaArray = Array(varResults).
Does anyone know where I can read more about transfer data from access to
excel through vba? Thanks

code follows:

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

 

[Klaus Oberdalhoff]

Hi,

here several parts which may be helpful ...

If i have to create an Excel-file without formatting quick and dirty, just
the data, i create a query with the name of the worksheet and then just do a
Transferspreadsheet. PS: To create several worksheets within one file, just
do a Transferspreadsheet with the same filename and a different query ...

To read about Office XP Automation
(Link in one line)

http://www.microsoft.com/downloads/...c8-fbee-4164-b8e9-589464f3fcad&DisplayLang=en

If you need to open excel to get hold of the Excel-object-model, well then i
use the following routine

that's what i do to open the Excel-model late-bound.
just cut & paste from my code so it wont run directly


Dim xlObj As Object
Dim objActiveWkb As Object
Dim objActSheet As Object
Dim xl_ForeignOpen As Boolean

'--- Open Excel
On Error Resume Next 'See if Excel is running
Set xlObj = GetObject(, "Excel.Application")
'--- If Excel not running: Error - start Excel
If Err.Number <> 0 Then 'Excel Not running
Err.Clear ' Clear Err object in case error occurred.
'--- Create a new instance of Excel
Set xlObj = CreateObject("Excel.Application")
xl_ForeignOpen = True
Else
'--- Activate instance of Excel
xlObj.Activate
xl_ForeignOpen = False
End If

DoEvents
On Error GoTo Error_sub

'--- Set Excel-Object
With xlObj
.Windowstate = xlMinimized
' .Windowstate = xlMaximized
.Visible = True
' .Visible = False

' .Workbooks.Add 'create new table
.Workbooks.Open myFilename, 0, True
' .Workbooks.RejectAllChanges ' set Excel file read only

.DisplayAlerts = False ' To supress any "are you sure" questions

Set objActiveWkb = xlObj.Application.ActiveWorkbook
End With

Set objActSheet = objActiveWkb.Worksheets(1)
.....
.....
.....
'closing ....

' With objActiveWkb
' myFilename = "C:\hugo.xls"
' .SaveAs Filename:=myFilename
' End With

' objActSheet.Close False
' objActiveWkb.Saved True
objActiveWkb.Close False
Set objActiveWkb = Nothing
xlObj.DisplayAlerts = True

If xl_ForeignOpen Then
' DoEvents
' xlObj.Application.Quit
' xlObj.Quit
' DoEvents

' Set xlObj = Nothing
End If

To fill an Excel-sheet partially ...
The for loop shows how to do it "manually"
The CopyFromRecordset is a much faster method to achieve the same
It just fills the rows and colums at the given starting-cell ...
It's from the 2003 help, but i think, it even worked in Office 97 ...
Using DAO there is not much difference between 97 and 2003 ...

With objActiveWkb.Worksheets("Data")

For iCols = 0 To rst.Fields.Count - 1
.Cells(10, iCols + 1).Value = rst.Fields(iCols).Name
Next

.Range("A11:AH70").CopyFromRecordset rst

End With
To import an Excel-File with schema.ini

'http://support.microsoft.com/kb/241477/en-us

'Use the Data Access Object (DAO) Execute method to import the text file.
You can instruct this method to use
'the Text ISAM driver. This driver is designed to automatically use a
Schema.ini file, as long as the file
'resides in the same folder as the text file.
'
'Sub ImportSchemaTable()
' Dim db As DAO.Database
' Set db = CurrentDb()
' db.Execute _
' "SELECT * INTO NewContact FROM
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\My documents;].[Contacts#txt];", _
' dbFailOnError
' db.TableDefs.Refresh
'End Sub
'
to create the schema.ini programmatically
http://support.microsoft.com/kb/210001/en-us

--
mfg

Klaus Oberdalhoff (e-mail address removed)

Ich beantworte keine NG-Fragen und -Nachfragen per Mail!
Newbie-Info: http://www.doerbandt.de/Access/Newbie.htm
KnowHow-mdb und andere Beispiele: http://www.freeaccess.de
Sofern Access 200x bitte beachten:
http://www.freeaccess.de/MS-Access-Artikel.asp?ID=99

Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)