exporting data into excel

  • Thread starter Thread starter Lin Light
  • Start date Start date
L

Lin Light

can you export data into a pre-existing excel spread
sheet? If column headers/names match data base field
names?
LIn
 
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
 
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
Click to expand...
= .Application.Transpose(varACs)
 
Doug Bell said:
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)
Click to expand...

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.

--
 
Back
Top