excel commands

[mlocurci]

Where can I find a list of commands that are few writing from a database to excel using VB6?

 

[HANSGIELEN]

First of all you should use Excel to record macros and analyze
the resulting code.

The following source is from Access but should work under VB as well.
Depending on the structure of your app, you may as well read the data directly from Excel or
create the excel files from your database app.

The code shown is the direct approach, appropriate for a small to medium
amount of data pasted to Excel.

For huge amounts of data (100.000 items and up) this is a rather slow process.
A faster approach is to write the data to a temp-file and launch an excel macro
which reads the data from the file and pastes the data into the cells.
Because the values are pasted from within Excel, this is at least 10 times as fast.
If you are interested I could provide the Excel VBA-Code and a VB-Sample of use as well...

But first the standard approach:

Global Const xlUp = -4162
Global Const xlNone = -4142
Global Const xlEdgeBottom = 9
Global Const xlContinuous = 1
Global Const xlMedium = -4138
Global Const xlAutomatic = -4105
Global Const xlDiagonalDown = 5
Global Const xlDiagonalUp = 6
Global Const xlEdgeLeft = 7
Global Const xlEdgeTop = 8
Global Const xlEdgeRight = 10
Global Const xlInsideVertical = 11
Global Const xlInsideHorizontal = 12
Global Const xlThin = 2
Global Const xlAscending = 1
Global Const xlYes = 1
Global Const xlTopToBottom = 1
Global Const xlDescending = 2
Global Const xlGuess = 0

Sub Sample()
Dim Excel As Object
Dim sourceFile As String

'Create a copy of your template
sourceFile = "C:\Data\MyTemplate.Xls"
destinFile = "C:\Data\Result.Xls"
FileCopy sourceFile, destinFile

Set Excel = CreateObject("Excel.Application&quot

'Open the file in Excel
Excel.Workbooks.Open destinFile, 0

'Activate the desired worksheet
Excel.Worksheets("Summary&quot.Activate

'Fill in the header
Excel.Cells(1, 1).value = "Title "
Excel.Cells(3, 2).value = Format$(Date, "dd.mm.yyyy&quot
For i = 1 To 13
Excel.Cells(4, 0 + 3 * i).value = currentWJ - 1
Excel.Cells(4, 1 + 3 * i).value = currentWJ
Next i

'Select a range and do number formatting
Excel.Range("C5:AO" + CStr(5 + numKunden)).Select
Excel.Selection.NumberFormat = "#,##0"

'Select certain rows and delete them
Excel.Rows(CStr(6 + numKunden) + ":1000&quot.Select
Excel.Selection.Delete Shift:=xlUp

'Doing some border formatting
Excel.Range("A" + CStr(5 + numKunden) + ":AO" + CStr(5 + numKunden)).Select
With Excel.Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

'Sorting the whole thing
Excel.Cells.Select
Selection.Sort Key1:=Excel.Range("A1&quot, Order1:=xlAscending, Key2:=Excel.Range("B1&quot _
, Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

Excel.Cells(1, 1).Select

'Saving and closing, quit excel
Excel.Application.ActiveWorkbook.Save
Excel.Application.ActiveWorkbook.Close
Excel.Application.Quit
Set Excel = Nothing

End Sub

 

[ggriffit]

check out the code and links at :

http://www.greggriffiths.org/webdev/both/excel/