convert access 97 data to excel 97 data via VB6

[dumbnewbie]

hi,

this is a stupid question to ask you guys, but i really need your help

i'm building this small application using VB6, where I need to convert access 97 data to excel 97. the problem is, i don't have a clue on how to do this. can someone out there assist me in solving this problem?

cheers in advance
dumbnewbie

 

[rgh]

This code should do the job. Uses an excel template so that you can format fields to the way you want before running routine. Any queries gimme a shout - Richard@relay.ie

Richard.

Private Sub cmdExport_Click()

Dim objExcel As Excel.Application
Dim intColumnCount As Long
Dim intRowCount As Long
Dim cnnBR As Connection
Dim rstRecordSet As ADODB.Recordset

Screen.MousePointer = vbHourglass

Set objExcel = New Excel.Application

objExcel.Workbooks.Add Template:=c:\export\Export.xlt"

objExcel.Visible = False

cnnBR.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\export\import.mdb"

Set rstRecordSet = New ADODB.Recordset
rstRecordSet.Open "SELECT * FROM ImportTable", cnnBR, adopenKeyset, adLockPessimistic, adCmdText

intColumnCount = 1
intRowCount = 1

With objExcel.Application
While Not rstRecordSet.EOF
If intRowCount = 1 Then
For intCounter = 0 To rstRecordSet.Fields.Count - 1
.Cells(intRowCount, intCounter + 1) = rstRecordSet.Fields.Item(intCounter).Name
Next
intRowCount = intRowCount + 1
End If
For intCounter = 0 To rstRecordSet.Fields.Count - 1
.Cells(intRowCount, intCounter + 1) = CStr(Trim(rstRecordSet.Fields.Item(intCounter).Value & "&quot)
Next
intRowCount = intRowCount + 1
rstRecordSet.MoveNext
Wend
End With

Screen.MousePointer = vbDefault

objExcel.Visible = True

Set objExcel = Nothing

rstRecordSet.Close
Set rstRecordSet = Nothing

cnnBR.Close
Set cnnBR = Nothing


End Sub