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 SaveInExcelSheet(rstRST As Recordset, fileNameWithPath As String)
Dim lngReccount As Long
lngReccount = rstRST.RecordCount
If lngReccount = 0 Then
MsgBox "No data found.", , "Information!"
Exit Sub
End If
Dim objExcel As New Excel.Application
If objExcel Is Nothing Then
MsgBox "Could not start Excel."
Screen.MousePointer = vbNormal
Exit Sub
End If
Dim wb As Workbook
Dim ws As Worksheet
Set wb = objExcel.Workbooks.Add
Set ws = objExcel.Worksheets.Add
' make column headings
' CurRow = 1
' CurCol = 1
' For ctr = 0 To rstRST.Fields.Count - 1
' ws.Cells(CurRow, CurCol).Value = rstRST.Fields.Item(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.Count).Address
On Error Resume Next
ws.Range(cellRange).Select
With Selection
.Font.Bold = True
End With
On Error GoTo 0
Err.Clear
rstRST.MoveFirst
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").Value
ws.Cells(lngRow, 3).Value = rstRST.Fields("strBillNo").Value
ws.Cells(lngRow, 4).Value = rstRST.Fields("datBillDate").Value
ws.Cells(lngRow, 5).Value = rstRST.Fields("numSubTotal").Value
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("numEducationCessAmt").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.AutoFit
wb.SaveAs fileNameWithPath
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