Go Back   Computer Forums > General Computing > Hardware
Click Here to Login
Join Computer forums Today


Reply
 
Thread Tools Search this Thread Display Modes
 
Old 03-01-2005, 05:22 PM   #1
Baseband Member
 
Join Date: Jun 2004
Posts: 26
Unhappy Please Help!!!

im doing a project on Microsoft Excel...
but i need help to try and create a table in visual basic as i have no idea
any help will be much appreciated
cheers
__________________

jonworth is offline   Reply With Quote
Old 03-01-2005, 05:32 PM   #2
Fully Optimized
 
jack22's Avatar
 
Join Date: Feb 2005
Posts: 2,124
Default Re: Please Help!!!

Q1371042. Creating charts dynamically from VB
The information on this article applies to

* ChartFX Client Server

Summary
Here is a small VB code sample to create Chart FX on the fly and export it into a specific folder.

Description
Here is the VB code to create ChartFX on the fly:

Dim Chart As Object
Dim ChartObj As ChartFX

Private Sub Form_Load()
Licenses.Add "ChartFX.Chart", "YOUR LICENSE STRING GOES HERE"
Set Chart = Controls.Add("ChartFX.Chart", "chart")

' ActiveX control properties
Chart.Visible = True
Chart.Width = 6000

' Chart FX Properties and Methods
Set ChartObj = Chart.object
ChartObj.Gallery = LINES


ChartObj.UpdateSizeNow ' Very important if Export is called before the chart is made visible
ChartObj.Export CHART_BITMAP, "C: \Chart1.bmp"
End Sub
__________________

__________________
Because of popular demand, the great almighty sig has been changed.



............cxxxxxxc|)::::::::::::> ................
jack22 is offline   Reply With Quote
Old 03-01-2005, 05:35 PM   #3
Daemon Poster
 
ACES WILD's Avatar
 
Join Date: Feb 2005
Posts: 833
Default Re: Please Help!!!

but why would you need to make a table in VB for excel?
usually its the other way around, you need to make a table in Excel for vb....
ACES WILD is offline   Reply With Quote
Old 03-01-2005, 05:36 PM   #4
Fully Optimized
 
jack22's Avatar
 
Join Date: Feb 2005
Posts: 2,124
Default Re: Please Help!!!

Creating Excel Spreadsheets and Charts from VB

This article will show you how to create a new work book, create sheets within that work book, populate the sheets, and create a graph (chart) using data keyed into the sheet.

I have a helpdesk-type application where we report on the number of new calls (Issues), calls fixed, and calls that convert into bug fixes (Stirs).

The helpdesk is broken up into a number of systems; for example, AS/400, NT, and so forth. An issue can be created for any system, so the program will create a sheet for each system.

The first thing to do is to create a reference to the Excel object. In my case, that's the Excel 9.0 object library. My program defines the following:

Dim objExcelA As Excel.Application
Dim objExcelW As Excel.Workbook
Dim objExcelSI As Excel.Worksheet 'Issues Work Sheet
Dim objExcelSS As Excel.Worksheet 'Stirs Work Sheet
Dim objExcelCI As Excel.Chart
Dim cho As Excel.ChartObject
Dim ch As Excel.Chart

'Dim objExcelCI As Excel.Charts
Dim objExcelCS As Excel.ChartObject

Dim adrQry As ADODB.Recordset
Dim adrChgType As ADODB.Recordset

Dim Row As Long
Dim chgtype As Long

Dim LastCell As String

Dim statYear As String

Dim bkmark As Variant

StatYear is used by the user. They can select statistics for a particular year or over the full term of the helpdesk (in other words, to give a trend of calls). The following code determines where the user is going to store the new Excel sheet:

With dlgFileLocation
.DefaultExt = ".XLS"
.DialogTitle = "Where is the Spread Sheet"
.filter = "Excel SpreadSheet|*.XLS|All Files|*.*"
.FilterIndex = 1
.FileName = "Issue Statistics"
.CancelError = True
.Flags = FileOpenConstants.cdlOFNHideReadOnly + _
FileOpenConstants.cdlOFNCreatePrompt + _
FileOpenConstants.cdlOFNOverwritePrompt
.InitDir = "C:\TEMP\"
.ShowSave
End With

Get the statistics year from the user:

statYear = InputBox("Do you want the stats for any particular year? _
(0 implies all years)", "Stats for a year", 0)
If statYear = "" Then 'User probably pressed CANCEL
Exit Sub
End If

Load Excel and start to build the record set that will hold the data for the spreadsheet:

Set objExcelA = New Excel.Application
Set objExcelW = objExcelA.Workbooks.add
Set adrQry = New ADODB.Recordset

Create/Open the recordset:

With adrQry
If statYear = "0" Then
.Source = "Statistics Order By ChangeType, StatsDate"
Else
.Source = "Statistics where Left$(statsdate, 4) = " & _
Chr$(39) & statYear & Chr$(39) & _
" Order By ChangeType, StatsDate"
End If
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.ActiveConnection = adoConnection
.Open , , , , adCmdTable
End With

Get the list of systems defined in the helpdesk:

Set adrChgType = New ADODB.Recordset
With adrChgType
.Source = "Systems"
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockReadOnly
.ActiveConnection = adoConnection
.Open , , , , adCmdTableDirect
bkmark = .bookmark
End With

(Change Type and system are defined as the same thing.)

We will now load the spreadsheet. If we have encountered a new system, the first column of the spreadsheet should have column headings:

Do While Not adrQry.EOF
If chgtype <> adrQry.Fields("ChangeType") Then
adrChgType.Find "SystemId = " & adrQry.Fields("ChangeType"), _
, adSearchForward, bkmark
Set objExcelSI = objExcelW.Worksheets.add
objExcelSI.Name = adrChgType.Fields("ChangeType") & " - Issues"
chgtype = adrQry.Fields("ChangeType")
Set objExcelSS = objExcelW.Worksheets.add
objExcelSS.Name = adrChgType.Fields("System") & " - Stirs"
objExcelSI.Cells(1, 1).Value = "Year / Week"
objExcelSI.Cells(1, 2).Value = "Curent Outstanding"
objExcelSI.Cells(1, 3) = "New Issues This Week"
objExcelSI.Cells(1, 4) = "Completed Issues This Week"
objExcelSS.Cells(1, 1).Value = "Year / Week"
objExcelSS.Cells(1, 2).Value = "Outstanding Stirs"
objExcelSS.Cells(1, 3).Value = "New Stirs This Week"
objExcelSS.Cells(1, 4) = "Completed Stirs This Week"

Row = 2
objExcelW.Charts.add
Set objExcelCI = objExcelW.ActiveChart
objExcelCI.Activate
objExcelCI.Name = adrChgType.Fields("System") & " - Issues Chart"
End If

We have now created a two new sheets: one for the issues (SI) and one for the Stirs/bug fixes (SS). We have also created a chart sheet (the chart sheet will only show a graph of the issues). We will now load the data into the sheet:

objExcelSI.Cells(Row, 1).Value = adrQry.Fields("StatsDate")
objExcelSI.Cells(Row, 2).Value = adrQry.Fields("Curent Outstanding")
objExcelSI.Cells(Row, 3) = adrQry.Fields("New Issues This Week")
objExcelSI.Cells(Row, 4) = adrQry.Fields("Completed Issues This Week")
objExcelSS.Cells(Row, 1).Value = adrQry.Fields("StatsDate")
objExcelSS.Cells(Row, 2).Value = adrQry.Fields("Outstanding Stirs _
This Week")

adrQry.MoveNext

If we have encounted the end of the recordset or the system/change type has changed, we need to build the chart/graph:

If adrQry.EOF Then
LastCell = "D" & Mid$(Str$(Row), 2)
objExcelCI.SetSourceData objExcelSI.Range("a1:" & _
LastCell), _
xlColumns
objExcelCI.ChartType = xlLineMarkers
objExcelCI.Legend.Position = xlLegendPositionBottom
objExcelCI.HasTitle = True
objExcelCI.ChartTitle.Text = objExcelCI.Name
Else
If chgtype <> adrQry.Fields("ChangeType") Then
LastCell = "D" & Mid$(Str$(Row), 2)
objExcelCI.SetSourceData objExcelSI.Range("a1:" & _
LastCell), _
xlColumns
objExcelCI.ChartType = xlLineMarkers
objExcelCI.Legend.Position = xlLegendPositionBottom
objExcelCI.HasTitle = True
objExcelCI.ChartTitle.Text = objExcelCI.Name
End If
End If
Row = Row + 1
Loop

The variable Row is incremented as we write a new row, so the line LastCell = "D" & Mid$(Str$(Row), 2) sets LASTCELL to something like D10.

objExcelCI.SetSourceData objExcelSI.Range("a1:" & LastCell), _
xlColumns

This line tells the chart the range of the data it is to use.

objExcelCI.ChartType = xlLineMarkers

I want my graph to be a line graph.

objExcelCI.Legend.Position = xlLegendPositionBottom

The legend is to go at the bottom of the screen:

objExcelCI.HasTitle = True
objExcelCI.ChartTitle.Text = objExcelCI.Name

The chart has a title and the title should be the same as the issues spreadsheet that created it.

Time to close everything now:

objExcelA.DisplayAlerts = False
objExcelW.SaveAs dlgFileLocation.FileName

objExcelA.Quit

Set objExcelA = Nothing
Set objExcelW = Nothing
Set objExcelSI = Nothing
Set objExcelSS = Nothing
Set objExcelCI = Nothing
Set objExcelCS = Nothing

If adrQry.State = adStateOpen Then
adrQry.Close
End If
If adrChgType.State = adStateOpen Then
adrChgType.Close
End If

Set adrQry = Nothing
Set adrChgType = Nothing

Me.MousePointer = vbNormal

Exit Sub
__________________
Because of popular demand, the great almighty sig has been changed.



............cxxxxxxc|)::::::::::::> ................
jack22 is offline   Reply With Quote
Old 03-01-2005, 05:37 PM   #5
Fully Optimized
 
jack22's Avatar
 
Join Date: Feb 2005
Posts: 2,124
Default Re: Please Help!!!

Hope that helps...i tried


Sorry for double post....didnt want t confuse you.
__________________
Because of popular demand, the great almighty sig has been changed.



............cxxxxxxc|)::::::::::::> ................
jack22 is offline   Reply With Quote
Old 03-01-2005, 07:36 PM   #6
Baseband Member
 
Join Date: Jun 2004
Posts: 26
Default

cheers for the help..i'll have a read over it see what i can do. thx again :-)
__________________

jonworth is offline   Reply With Quote
Reply

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 On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off



All times are GMT -5. The time now is 01:41 PM.


Powered by vBulletin® Version 3.8.8 Beta 4
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.0