EXPORTING VB6 DATAGRID TO EXCEL SPREADSHEET 1

[Hiccup]

I have a DataGrid connected via an Adodc to an Access2K mdb. I would like to export the records of the DataGrid to an Excel spreadsheet formated to landscaped 11X17 for printing onto an 11X17 landscaped sheet of paper.

Can anyone provide the code routine to accomplish this.

Thanks in advance!!

 

[HANSGIELEN]

The following source is from Access but should work under VB as well.

Create a file named Template.XLS and apply all formatting not depending on data pasted.

In code create a copy of your template, fill it, print it save it ...

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

'Replace the following with code to get the values from your datagrid:

For i = 1 To 13
Excel.Cells(4, 0 + 3 * i).value = 123
Excel.Cells(4, 1 + 3 * i).value = 321
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

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

End Sub

 

[Hiccup]

Thanks, HANSGIELEN, here's a star for you!