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
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
Reply
 
Thread Tools 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

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

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


Thread Tools
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 07:15 AM
dynamically generate crystal report s_sanks Crystal Reports 0 March 3rd, 2008 06:19 AM
How can i generate OLAP Report manoj_k79 Classic ASP Basics 0 December 15th, 2006 07: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 04:02 AM.


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