exporting data into excel

[Lin Light]

can you export data into a pre-existing excel spread
sheet? If column headers/names match data base field
names?
LIn

 

[Doug Bell]

I use an Excel template and code to to push data into it.

It involves getting the recordset and determining how large it is,
then opening a new Workbook based on the template using automation
then taking blocks of data (size depends on the number and size of columns
you want to export)

eg. this is the code snippet that actually pushes the data into the
Worksheet object

Set xlAp = New Excel.Application
Set xlWkBk = xlAp.Workbooks.Add(stFile)
Set xlWkSt = xlWkBk.Worksheets("NextYrBudget") 'Open Budget

With xlAp
xlWkBk.Windows(1).Visible = True
.WindowState = xlMaximized
.Visible = True
End With

Dim xlRng As Excel.Range, varACs As Variant
Dim intYlst As Integer, intY As Integer, intX As Integer
With rs
.MoveFirst
intYlst = 6
Do While Not .EOF
'Copy rs into Array
varACs = .GetRows(intRows)
'Get Array boundaries
intX = UBound(varACs, 1)
intY = UBound(varACs, 2)
'Set Range for DataSet
With xlWkSht
.Activate 'Set selected Worksheet (xlWkSht as Active WkSht
Set xlRng = .Range(.Cells(intYlst, 1), _
.Cells(intYlst + intY, intX + 1))
'Paste Array into XLWkSht
xlRng.FormulaArray = .Application.Transpose(varACs)
intYlst = intYlst + intY + 1
End With
Loop
End With

 

[Lin Light]

Is there a way to do this with wizards?
Lin

-----Original Message-----
I use an Excel template and code to to push data into it.

It involves getting the recordset and determining how large it is,
then opening a new Workbook based on the template using automation
then taking blocks of data (size depends on the number and size of columns
you want to export)

eg. this is the code snippet that actually pushes the data into the
Worksheet object

Set xlAp = New Excel.Application
Set xlWkBk = xlAp.Workbooks.Add(stFile)
Set xlWkSt = xlWkBk.Worksheets ("NextYrBudget") 'Open Budget

With xlAp
xlWkBk.Windows(1).Visible = True
.WindowState = xlMaximized
.Visible = True
End With

Dim xlRng As Excel.Range, varACs As Variant
Dim intYlst As Integer, intY As Integer, intX As Integer
With rs
.MoveFirst
intYlst = 6
Do While Not .EOF
'Copy rs into Array
varACs = .GetRows(intRows)
'Get Array boundaries
intX = UBound(varACs, 1)
intY = UBound(varACs, 2)
'Set Range for DataSet
With xlWkSht
.Activate 'Set selected Worksheet (xlWkSht as Active WkSht
Set xlRng = .Range(.Cells(intYlst, 1), _
.Cells(intYlst + intY, intX + 1))
'Paste Array into XLWkSht
xlRng.FormulaArray

= .Application.Transpose(varACs)

 

[Jamie Collins]

I use an Excel template and code to to push data into it.

It involves getting the recordset and determining how large it is,
then opening a new Workbook based on the template using automation
then taking blocks of data (size depends on the number and size of columns
you want to export)

Note that until Excel2002, Application.Transpose was limited to 5461
elements. Also, your approach is long winded. If you are taking on the
overhead of automation, the CopyFromRecordset method is preferable,
which supported ADO recordsets from Excel2000, because you don't have
to e.g. find out large the recordset is etc.

However, automation is usually not needed. Preferable to use a INSERT
INTO..SELECT query e.g. to update an existing Excel table:

INSERT INTO
[Excel 8.0;database=C:\MyWorkbook.xls;].MyBookLevelName
(MyCol1, MyCol2, MyCol3)
SELECT
ColA AS MyCol1,
ColB AS MyCol2,
ColC AS MyCol3
FROM
MyTable
;

To create a new Excel workbook/worksheet/table:

SELECT
ColA AS MyCol1,
ColB AS MyCol2,
ColC AS MyCol3
INTO
[Excel 8.0;database=C:\NewWorkbook.xls;].Sheet1
FROM
MyTable
;

Jamie.

--