Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB Databases Basics
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 18th, 2012, 05:10 AM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Apr 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default generate excel report in vb

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



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

Set ExcelWS = ExcelWBk.ActiveSheet
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"

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


Screen.MousePointer = vbDefault

End Sub

Last edited by orked; April 18th, 2012 at 05:12 AM..
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Make the report in excel using vb.net dhaval229 Visual Studio 2005 2 June 4th, 2015 03:31 AM
Generate Excel spreadsheet with ASP.Net (VB) James Diamond ASP.NET 1.0 and 1.1 Professional 2 December 11th, 2013 08:15 AM
dynamically generate crystal report s_sanks Crystal Reports 0 March 3rd, 2008 07:19 AM
How can i generate OLAP Report manoj_k79 Classic ASP Basics 0 December 15th, 2006 08:28 AM
How to generate a report using vba ? method Access VBA 1 May 3rd, 2005 04:48 AM

All times are GMT -4. The time now is 10:21 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.