jannypan
|
|
Group: Forum Members
Posts: 3,
Visits: 2
|
this example works great with vb6, and I see now that you were wanting vb.net example. Tera OnlineRift PlatinumTera Gold
|
|
|
dannyrose12
|
|
Group: Forum Members
Posts: 13,
Visits: 5
|
Do I necessity to person an vesture, I was superficial righteous to expend the content from what I **** designated in the discharge downs and transpose that content to excel.
Remote Control Cars | HGH supplements | Dissertation Help
|
|
|
pso
|
|
Group: Forum Members
Posts: 794,
Visits: 338
|
Sorry friend, I am swamped. Hopefully someone will come in here and finish up for you. try PM member Mark. He can help
|
|
|
montekass
|
|
Group: Forum Members
Posts: 20,
Visits: 11
|
Ok great firgured it out needed to reinstall office 2003 I went further in the code and now im at the point where, I wanna be able to keep a summary of what I did like this Building number unit number area 1 101 bedroom 2 201 bathroom 10 308 living room and so on then be able to transfer to excel, I guess have a button after I am finished with the single selections that I would click named STORE, then after I have made all my selections, how many ever there are lets say 40, and have a summary to show me the selection, and then click the transfer button as the final step in exporting it into excel, I tried createing a form for the summary but I think I going the wrong way for this????????????
|
|
|
montekass
|
|
Group: Forum Members
Posts: 20,
Visits: 11
|
this part: Dim oApp As New Excel.ApplicationDim oWB As Excel.Workbook = oApp.Workbooks.AddDim oWS As Excel.Worksheet = DirectCast(oWB.Worksheets(1), Excel.Worksheet)Dim oRng1, oRng2, oRng3 As Excel.Range I referenced excel 11.0 and this is what happened, I can only use 11.0 and 5.0 and I know excel 2003 is 11.0 code looks great and I understand too, thank you but this seems to a probelm here wonder why?
|
|
|
pso
|
|
Group: Forum Members
Posts: 794,
Visits: 338
|
My Excel 9.0 , your 11.0, don't worry about it This might be enough to get you started: Option Strict On Module Module1 Public xls As Excel.Application Public CurrentBuilding As String = "" Public CurrentUnitArea As String = "" Public CurrentUnitNumber As String = ""
Sub main() Dim frmmain As New Form1
Application.Run(frmmain) End Sub End Module Public Class Form1 Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New() MyBase.New()
'This call is required by the Windows Form Designer. InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub
'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. Friend WithEvents Button1 As System.Windows.Forms.Button Friend WithEvents Building As System.Windows.Forms.ComboBox Friend WithEvents Number As System.Windows.Forms.ComboBox Friend WithEvents Area As System.Windows.Forms.ComboBox Private Sub InitializeComponent() Me.Button1 = New System.Windows.Forms.Button Me.Building = New System.Windows.Forms.ComboBox Me.Number = New System.Windows.Forms.ComboBox Me.Area = New System.Windows.Forms.ComboBox Me.SuspendLayout() ' 'Button1 ' Me.Button1.Location = New System.Drawing.Point(139, 170) Me.Button1.Name = "Button1" Me.Button1.Size = New System.Drawing.Size(120, 25) Me.Button1.TabIndex = 0 Me.Button1.Text = "Button1" ' 'Building ' Me.Building.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList Me.Building.Location = New System.Drawing.Point(0, 0) Me.Building.Name = "Building" Me.Building.Size = New System.Drawing.Size(121, 21) Me.Building.TabIndex = 2 ' 'Number ' Me.Number.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList Me.Number.Location = New System.Drawing.Point(-2, 32) Me.Number.Name = "Number" Me.Number.Size = New System.Drawing.Size(121, 21) Me.Number.TabIndex = 1 ' 'Area ' Me.Area.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList Me.Area.Location = New System.Drawing.Point(-2, 65) Me.Area.Name = "Area" Me.Area.Size = New System.Drawing.Size(121, 21) Me.Area.TabIndex = 0 ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(262, 209) Me.Controls.Add(Me.Area) Me.Controls.Add(Me.Number) Me.Controls.Add(Me.Building) Me.Controls.Add(Me.Button1) Me.Name = "Form1" Me.Text = "Form1" Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click '\\ MySub() '\\ End Sub Sub MySub() Dim oApp As New Excel.Application Dim oWB As Excel.Workbook = oApp.Workbooks.Add Dim oWS As Excel.Worksheet = DirectCast(oWB.Worksheets(1), Excel.Worksheet) Dim oRng1, oRng2, oRng3 As Excel.Range ' Your Code Goes Here oRng1 = oWS.Range("A1") 'building oRng1.Value = "Building" oRng1 = oWS.Range("A2") oRng1.Value = CurrentBuilding
oRng2 = oWS.Range("C1") 'UnitNumber oRng2.Value = "Unit Number" oRng2 = oWS.Range("C2") oRng2.Value = CurrentUnitNumber
oRng3 = oWS.Range("E1") 'UnitArea oRng3.Value = "Unit Area" oRng3 = oWS.Range("E2") oRng3.Value = CurrentUnitArea
' save the doc now? 'oWB.SaveAs("C:\My Documents\MyWorkbook.xls")
oRng1 = Nothing ' <-- Don't forget! oRng2 = Nothing ' <-- Or Excel will hang... oRng3 = Nothing Cleanup: 'oWB.Close(SaveChanges:=False) 'oApp.Quit() oApp.Visible = True oApp = Nothing oWB = Nothing oWS = Nothing
GC.Collect() ' <-- *** Important! *** End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load ' i placed this here s ' 'Building ' Me.Building.Items.AddRange(New Object() {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "34"}) Me.Building.Text = "1" Me.Building.SelectedItem = 0 ' 'Number ' Me.Number.Items.AddRange(New Object() {"101", "102", "201", "208", "301", "308"}) Me.Number.Text = "101" Me.Number.SelectedItem = 0 ' 'Area ' Me.Area.Items.AddRange(New Object() {"Kitchen", "Bedroom", "Bathroom", "Living Room"}) Me.Area.Text = "Kitchen" Me.Area.SelectedItem = 0
Me.Building.Select() End Sub
Private Sub Area_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Area.SelectedIndexChanged CurrentUnitArea = CType(Area.SelectedItem, String) Debug.WriteLine("Current Unit Area: " & CurrentUnitArea.ToString) End Sub
Private Sub Building_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Building.SelectedIndexChanged CurrentBuilding = CType(Building.SelectedItem, String) Debug.WriteLine("Current Building: " & CurrentBuilding.ToString) End Sub
Private Sub Number_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Number.SelectedIndexChanged CurrentUnitNumber = CType(Number.SelectedItem, String) Debug.WriteLine("Current UnitNumber: " & CurrentUnitNumber.ToString)
End Sub End Class
I decided to leave Option Strict On, since I prefer to program this way. I got my help from herePost back here if problems.
|
|
|
montekass
|
|
Group: Forum Members
Posts: 20,
Visits: 11
|
Im using the excel 11.0 version, seems to be causing some issues, I can only choose the 5.0 and 11.0, when I use the 11.0 version I cant reference excel ??????????????????? now buy using the combo.list I could go through as many times as I want, meaning that it go to the next line in excel for the next set of selections right. First I have to figure out why I keep getting this error for this line of code eBook = eApp.WorksBooks("WorkBook.xls") error is system .runtime.interopservices.COMException' occurred in mscorlib.dll additional info: ember not found but I have it referenced ?????????????????????????????????????????
|
|
|
pso
|
|
Group: Forum Members
Posts: 794,
Visits: 338
|
Hmmm, actually, I referenced the Excel object library 9.0 for the project and added a sub main , turned Option Strict off, changed startup property to Sub main, added a button, pasted my code and transfered object variable around, so end result is:
Option Strict Off Module Module1 Public xls As Excel.Application Sub main() Dim frmmain As New Form1
Application.Run(frmmain) End Sub End Module Public Class Form1 Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New() MyBase.New()
'This call is required by the Windows Form Designer. InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub
'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. Friend WithEvents Button1 As System.Windows.Forms.Button Private Sub InitializeComponent() Me.Button1 = New System.Windows.Forms.Button Me.SuspendLayout() ' 'Button1 ' Me.Button1.Location = New System.Drawing.Point(62, 187) Me.Button1.Name = "Button1" Me.Button1.Size = New System.Drawing.Size(174, 40) Me.Button1.TabIndex = 0 Me.Button1.Text = "Button1" ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(292, 266) Me.Controls.Add(Me.Button1) Me.Name = "Form1" Me.Text = "Form1" Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 'Add a reference to Microsft excel object library xls = New Excel.Application ' here is where you place code that gets all your entries ' and places them in multi-dimentional array '\\ 'that's your job Dim theArray(9, 7) As String Dim Counterx As Integer, Countery As Integer, x As Integer, y As Integer ' fill theArray() with your entry values For x = 0 To 9 For y = 0 To 7 theArray(x, y) = "pso is ugly!"
Next
Next '\\
'\\ Dim row As Integer, col As Integer
' start a new workbook xls.Workbooks.Add() With xls.ActiveWorkbook.ActiveSheet For row = 0 To 9 For col = 0 To 7 If Len(Trim(theArray(row, col))) > 0 Then ' add values to cells in worksheet, replacing the "Ö" with "0" .Cells(row + 1, col + 1).Value = theArray(row, col) End If Next Next End With
xls.Visible = True 'xls.Quit <- will Ask to quit Excel xls = Nothing ' always set object back to nothing when finished with it End Sub End Class
And it fired right up. no errors. Maybe you referenced Ecel object lib 5, and maybe that is the difference and why you got an error. I don't know. Anyhow, to answer your q, no you don't have to use an array. I just used it so I wouldn't have to code using a bunch of drop down combos However, just get the listindex of each combo and pass the text to the excel cell.... cell(1,1).value = comboUnit.List(x)
|
|
|
pso
|
|
Group: Forum Members
Posts: 794,
Visits: 338
|
oh boy, this example works great with vb6, and I see now that you were wanting vb.net example. so I goofed. sorry I'll check back later to see if I can fix my err.
|
|
|
montekass
|
|
Group: Forum Members
Posts: 20,
Visits: 11
|
I'm getting an error xls.workbooks.add() telling me An unhandled exception of type 'System.runtime.interopservices.COMException' occurred in mscorlib.dll Addtional information: Memeber not found????????????????????????????????????????????????????
|
|
|