VB to excel report

[devianleong]

Hi, I've a form which allow a user to select the year and category. Year and category will pass the value to the query :

"SELECT * FROM Shares WHERE Year = '"&yearr.txt&"' AND Category = '"&category.text&"'"

After that I want the report print the report to excel base on the query above. Any Idea? I'm new to VB.net . Please help me . Thank in advance.

[Reverend Jim]

Have you tried google Daniweb? Using Excel from VB.net has been discussed many times.

[Alans123]

hi,
Now I have to develop a simple finance system for my department, I have problem in generate report. I have no idea how to generate excel report using vb 6.0 and my database is Microsoft access 2003. I used Adodc method. Can anyone help me to solve this problem. Here is the code that i have been write

Option Explicit
Dim conn As Connection
Dim sql As String
Dim Excel As Object ' This is the excel program
Dim ExcelWBk As Object ' This is the work book
Dim ExcelWS As Object ' This is the sheet
Private Sub StartExcel()
Set Excel = CreateObject("Excel.Application") 'Create Excel Object.
End Sub
Private Sub CreateWorkSheet()
On Error Resume Next
Set ExcelWBk = Excel.Workbooks.Add 'Add this Workbook to Excel.
Set ExcelWS = ExcelWBk.Worksheets(1) ' Add this sheet to this Workbook
End Sub
Private Sub SaveWorkSheet()
On Error Resume Next
' ExcelWBk.SaveAs "C:\Documents and Settings\user\My Documents\MIS\Report.xls"
End Sub
Private Sub CloseWorkSheet()
On Error Resume Next
ExcelWBk.Close ' Close the WorkBook
Excel.Quit ' Quit Excel app
End Sub
Private Sub Command1_Click()
On Error Resume Next
If Me.cmbMonth = " & cmbMonth & " And Me.cmbYear = " & cmbYear & " Then
MsgBox "Please select MONTH and YEAR"
Exit Sub
End If

StartExcel
CreateWorkSheet

test

SaveWorkSheet
CloseWorkSheet
Unload Me

End Sub

Private Sub test()
On Error Resume Next

Screen.MousePointer = vbHourglass


dtaOverhead.Recordset.Open "SELECT * from variable WHERE [MONTH] = '" & cmbMonth & "' AND [YEAR] = " & cmbYear & " ", adOpenStatic, adLockReadOnly

ExcelWBk.Worksheets.Add
Set ExcelWS = ExcelWBk.ActiveSheet
ExcelWS.Activate
ExcelWS.Name = "PG Variable Overhead"

ExcelWS.Cells(4, 4) = "Act "
ExcelWS.Cells(4, 6) = "Bud "
ExcelWS.Cells(4, 8) = "Remarks "
ExcelWS.Cells(3, 4) = "Month : "
ExcelWS.Cells(3, 6) = "Year : "
ExcelWS.Cells(5, 1) = "FL Milling"
ExcelWS.Cells(6, 1) = "Raw Mat Int"
ExcelWS.Cells(7, 1) = "Warehouse"
ExcelWS.Cells(8, 1) = "Labrotary"
ExcelWS.Cells(9, 1) = "Workshop"
ExcelWS.Cells(10, 1) = "SALARIES & WAGES"

ExcelWS.Cells(11, 1) = "FL Milling"
ExcelWS.Cells(12, 1) = "Raw Mat Int"
ExcelWS.Cells(13, 1) = "Warehouse"
ExcelWS.Cells(14, 1) = "Workshop"
ExcelWS.Cells(15, 1) = "SALARIES FOREIGN WORKERS"


Dim x, y, z As Integer
x = 4
y = 6
z = 8

Do Until dtaOverhead.Recordset.EOF
ExcelWS.Cells(3, 5) = dtaOverhead.Recordset.Fields("MONTH")
ExcelWS.Cells(3, 7) = dtaOverhead.Recordset.Fields("YEAR")

ExcelWS.Cells(5, x) = dtaOverhead.Recordset.Fields("SWACTFM")
ExcelWS.Cells(5, y) = dtaOverhead.Recordset.Fields("SWBUDFM")
ExcelWS.Cells(5, z) = dtaOverhead.Recordset.Fields("SWREMFM")

ExcelWS.Cells(6, x) = dtaOverhead.Recordset.Fields("SWACTRAW")
ExcelWS.Cells(6, y) = dtaOverhead.Recordset.Fields("SWBUDRAW")
ExcelWS.Cells(6, z) = dtaOverhead.Recordset.Fields("SWREMRAW")

ExcelWS.Cells(7, x) = dtaOverhead.Recordset.Fields("SWACTWHS")
ExcelWS.Cells(7, y) = dtaOverhead.Recordset.Fields("SWBUDWHS")
ExcelWS.Cells(7, z) = dtaOverhead.Recordset.Fields("SWREMWHS")

ExcelWS.Cells(8, x) = dtaOverhead.Recordset.Fields("SWACTLAB")
ExcelWS.Cells(8, y) = dtaOverhead.Recordset.Fields("SWBUDLAB")
ExcelWS.Cells(8, z) = dtaOverhead.Recordset.Fields("SWREMLAB")

ExcelWS.Cells(9, x) = dtaOverhead.Recordset.Fields("SWACTWORK")
ExcelWS.Cells(9, y) = dtaOverhead.Recordset.Fields("SWBUDWORK")
ExcelWS.Cells(9, z) = dtaOverhead.Recordset.Fields("SWREMWORK")

ExcelWS.Cells(10, x) = dtaOverhead.Recordset.Fields("SWACTTOTAL")
Excel.ActiveSheet.Cells(10, x).Font.ColorIndex = 3
ExcelWS.Cells(10, y) = dtaOverhead.Recordset.Fields("SWBUDTOTAL")
Excel.ActiveSheet.Cells(10, y).Font.ColorIndex = 3
ExcelWS.Cells(10, z) = dtaOverhead.Recordset.Fields("SWREMTOTAL")
Excel.ActiveSheet.Cells(10, z).Font.ColorIndex = 3

dtaOverhead.Recordset.MoveNext
Loop

Screen.MousePointer = vbDefault

End Sub

[Reverend Jim]

Three problems here. First, you posted this in the wrong forum so I moved it for you. Second, you hijacked a thread. From now on please start a new thread for a new problem. Third, you didn't use the Code tool to insert code. Your code went in as text which means it does not display properly. I corrected that as well.

[AndreRet]

As for the original poster, I have asked that this be moved to vb.net.

[Reverend Jim]

Sorry. My mistake. I didn't realize that the third poster (who specified VB6) was not the OP. They both had the same generic avatar and I didn't spot the name. I'm going to move it back to VB.Net.