How do you export an access table/query to an excel file (several sheets)

[Lefke123]

hello,


does anyone know how to write certain information from a table / query using VB into a new excel sheet

for example; i press a button and a printable excel file is made with the name "random [day month year].xls", including 2 sheets, both with different strings of text

id only need the basic codes for it, if anyone knows them?



thank you in advance

[muddasirmunir]

if you use crystal report 10 it has a default option to export report
to excel

hello,


does anyone know how to write certain information from a table / query using VB into a new excel sheet

for example; i press a button and a printable excel file is made with the name "random [day month year].xls", including 2 sheets, both with different strings of text

id only need the basic codes for it, if anyone knows them?



thank you in advance

[Lefke123]

the thing is, im using visual basic express edition (the 2005 one)

any thoughts?

[9815402440]

hi

use following code


Public Sub SaveInExcelShee t(rstRST As Recordset, fileNameWithPat h As String)
Dim lngReccount As Long
lngReccount = rstRST.RecordCo unt
If lngReccount = 0 Then
MsgBox "No data found.", , "Informatio n!"
Exit Sub
End If
Dim objExcel As New Excel.Applicati on
If objExcel Is Nothing Then
MsgBox "Could not start Excel."
Screen.MousePoi nter = vbNormal
Exit Sub
End If
Dim wb As Workbook
Dim ws As Worksheet
Set wb = objExcel.Workbo oks.Add
Set ws = objExcel.Worksh eets.Add
' make column headings
' CurRow = 1
' CurCol = 1
' For ctr = 0 To rstRST.Fields.C ount - 1
' ws.Cells(CurRow , CurCol).Value = rstRST.Fields.I tem(ctr).Name
' CurCol = CurCol + 1
' Next
ws.Cells(1, 1).Value = "Heading"

ws.Cells(2, 1).Value = "Sub Heading"

ws.Cells(4, 1).Value = "Party Name"
ws.Cells(4, 2).Value = "Address"
ws.Cells(4, 3).Value = "Bill No"
ws.Cells(4, 4).Value = "Bill Date"
ws.Cells(4, 5).Value = "Basic Amount"
ws.Cells(4, 6).Value = "TCS"
ws.Cells(4, 7).Value = "Surcharge"
ws.Cells(4, 8).Value = "Edu Cess"
' make col headings bold
Dim cellRange As String
cellRange = ws.Cells(1, 1).Address
cellRange = cellRange & ":" & ws.Cells(4, rstRST.Fields.C ount).Address
On Error Resume Next
ws.Range(cellRa nge).Select
With Selection
.Font.Bold = True
End With
On Error GoTo 0
Err.Clear
rstRST.MoveFirs t
Dim lngRow As Long
lngRow = 5
While Not rstRST.EOF
'Replace field names
ws.Cells(lngRow , 1).Value = rstRST.Fields(" strPartyName"). Value
ws.Cells(lngRow , 2).Value = rstRST.Fields(" strAddress").Va lue
ws.Cells(lngRow , 3).Value = rstRST.Fields(" strBillNo").Val ue
ws.Cells(lngRow , 4).Value = rstRST.Fields(" datBillDate").V alue
ws.Cells(lngRow , 5).Value = rstRST.Fields(" numSubTotal").V alue
ws.Cells(lngRow , 6).Value = rstRST.Fields(" numTCS").Value
ws.Cells(lngRow , 7).Value = rstRST.Fields(" numSurcharge"). Value
ws.Cells(lngRow , 8).Value = rstRST.Fields(" numEducationCes sAmt").Value
lngRow = lngRow + 1
DoEvents
rstRST.MoveNext
Wend
ws.Cells(lngRow , 4).Value = "Total"
ws.Cells(lngRow , 5).Value = "=SUM(E5:E" & CStr(lngRow - 1) & ")"
ws.Cells(lngRow , 6).Value = "=SUM(F5:F" & CStr(lngRow - 1) & ")"
ws.Cells(lngRow , 7).Value = "=SUM(G5:G" & CStr(lngRow - 1) & ")"
ws.Cells(lngRow , 8).Value = "=SUM(H5:H" & CStr(lngRow - 1) & ")"
ws.Columns.Auto Fit
wb.SaveAs fileNameWithPat h
objExcel.Quit
Set objExcel = Nothing
Set wb = Nothing
Set ws = Nothing
End Sub


regards

manpreet singh dhillon hoshiarpur

[Lefke123]

thank you, thisll really help me a lot

[JustJim]

thank you, thisll really help me a lot

And me! Here's some spaces to make up 20 chars

Jim