|
making the forms get information from an excel spreadsheet and then put the info in the right text box
matthew davis
|
|
|
|
|
Matt,
a) You've got a bit of work ahead of you.
b) you're in the wrong forum, this one is for VB.NET
c) anyway, here's a class and some code I used to read and write to Excel files.
----
Option Explicit
Public sFileName As String
Dim sCurrentWorksheet As String
Dim bExcelWasRunning As Boolean
Public oExcel As Object
'Sub EnterValue()
' Worksheets("Sheet1").Cells(6, 1).Value = 10
'End Sub
'The Cells property works well for looping through a range of cells, because you can substitute variables for the index numbers, as shown in the following example.'
'Sub CycleThrough()
' Dim counter As Integer
' For counter = 1 To 20
' Worksheets("Sheet1").Cells(counter, 3).Value = counter
' Next counter
'End Sub
Public Function Init(ByVal asExcelFileName As String) ' , ByVal asCurrentWorksheet) as boolean
' Get handle to currently loaded instance of Excel if there is one.
Init = False
bExcelWasRunning = False
Err.Clear
On Error Resume Next
Set oExcel = CreateObject("Excel.Application")
If Err.number <> 0 Then
MsgBox ("Could not start up Excel. [E9810180901]")
Exit Function
End If
On Error GoTo OpenError
If asExcelFileName = "" Then
Dim xlWb As Object
Dim xlWs As Object
Set xlWb = oExcel.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")
Else
sFileName = asExcelFileName
On Error GoTo OpenError
oExcel.Workbooks.Open asExcelFileName
On Error GoTo 0
Err.Clear
End If
Init = True
Exit Function
OpenError:
MsgBox ("Error while opening the Excel file: " & asExcelFileName & " " & vbCrLf & vbCrLf & Err.Description)
Init = False
End Function
Public Sub destroy()
' Shut down word if we loaded it ourselves, otherwise don't shut down an app we did not load.
If Not bExcelWasRunning Then oExcel.Quit
End Sub
----
----
' some mainline code that uses the class above.
dim objExcel as clsExcel
Set objExcel = New clsExcel
Call objExcel.Init(txtExcelInputFile.Text) ' open the file...like c:\mydirectory\SomeExcelFile.xls
Dim s As String
On Error Resume Next
s = objExcel.sFileName
If Err.number = 91 Then
MsgBox ("Please use File / Open Excel Input File Menu to connect to the Excel file you want to import.")
Exit Sub
End If
On Error Resume Next
With objExcel.oExcel.Worksheets(SheetID) '("Sheet1")
foo = .cells(1,5)
etc...
---------
Finally, you need to be careful pulling cell values from the spreadsheet.
The cell could be empty, not contain the datatype you think should be there,
or worse yet, may contain a formula.
Here's a wrapper function I wrote for pulling out what is suppose to be date-type data
Private Function GetDateField(ByVal sFieldName as string , ByVal iRow As Integer, Optional bReq As Variant) As String
Dim nCol As Integer
Dim s As String
Dim dt As Date
' bReq: is this field Required? if yes, and the data in the cell is missing, put out an error message.
If IsMissing(bReq) Then bReq = False
GetDateField = ""
' pass in the "name" of a field, look up in colInfo array within the application,
' obtain the column that data is in. Example: CityName is mapped to column 14, Salary to col 20, etc)
nCol = ColInfo(sFieldName)
If nCol = 0 Then
If bReq Then
' send error to the log file. the value is missing from the Cell, and it was a required field.
Call AppendError("Row=" & iRow & ", column " & quote(quote(ColInfo(nField, 1))) & _
" is missing from input Excel file. Please correct.")
End If
Exit Function
End If
s = Trim(objExcel.oExcel.Worksheets(SheetID).Cells(iRow, nCol).Value)
If s = "" Then
If bReq Then
Call AppendError("Row=" & iRow & " Column=" & nCol & ", Field " & quote(quote(ColInfo(nField, 1))) & _
" Field is empty. Please correct.")
End If
Exit Function
End If
On Error Resume Next
dt = CDate(s)
If Err.number <> 0 Then
' can't convert the date to a date, we're in trouble.
Call AppendError("Row=" & iRow & " Column=" & nCol & ", Field " & quote(quote(ColInfo(nField, 1))) & _
" Field is invalid: " & quote(s) & ". Please correct.")
Exit Function
End If
On Error GoTo 0
GetDateField = Format(s, "mm/dd/yyyy")
End Function
|
|
|
|
|
Hi, I use a TAB control in my VB.NET Project. I create three tab.
1. tbpSearchCat<br />
2. tbpSearchArt1<br />
3. tbpSearchArt2
But when I close my project and open the project again, my TAB control change the tab order to:
1. tbpSearchArt1<br />
2. tbpSearchArt2<br />
3. tbpSearchCat
I know it change because it sort the tab via the name of the tab (tbpSearchArt1 come before tbpSearchArt2, and tbpSearchArt2 come before tbpSearchCat) I don't want to make it sort like that. I want to keep it remain as I create it. Can any one help me??
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
I am having a bit of difficulty reproducing your problem, Do you have one tab control with 3 tab pages on it? Tab pages themselves do not have a tabstop, on the parent tab control does. Are you meaning the tab page that has focus after you close the project? Or am i up a completely different tree?
Cheers
Tom
|
|
|
|
|
Tom John wrote:
Do you have one tab control with 3 tab pages on it?
Yes that right. My problem is each page is not fix as it position. The first time that I design, I arrange it as an order. But when I start it again the first tab page become the second tab page. And the second tab page become the third tab page. I don't want to do that because it is not the way that I want. But I don't know how to show the problem.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Can you send me an example project cut down to the minimum required to illustrate the problem?
Cheers
Tom
|
|
|
|
|
OK, I create the blank project in vb.net. Then I drag the TAB control on the form. Then I create the TAB page via the tab item property. I created the following 3 tab pages:
tabSearch1<br />
tabSearch2<br />
tabSearchAll
And I want to display it on the form like this order:
tabSearch1 tabSearch2 tabSearchAll
But when I close the project and come back to work again it display:
tabSearchAll tabSearch1 tabSearch2
That is the order that I don't want to display. I like to display as the first time that I create it. Any idea???
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
I'm on VS2003 and can't reproduce this. If i just add the tab control then the pages it always loads (and runs) in the correct order.
Have you checked in the Items property that the pages are listed in the correct order and in the form's InitializeComponent() sub (under the windows generated code region) that the tab pages are added in the correct order.
Sorry i can't help more
Tom
|
|
|
|
|
I need to draw a network diagram using vb.net. It has to be in form of a rectangle box with little compartments in it. These compartments looks as table cell and show data from database table. Is anyone can help?
Many Thanks,
Preesteya.
|
|
|
|
|
Have a look at the GDI+ graphics object:
Here on MSDN[^]
http://www.bobpowell.net/[^] - Great stuff on GDI+
You can use this to draw on a control such as a form or a panel. The following code draws a grid on a panel (crudely):
Private columns As Integer = 5
Private rows As Integer = 4
Private Sub Form1_OnLoad(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
Dim newPanel As New Panel
newPanel.BorderStyle = BorderStyle.FixedSingle
newPanel.Location = New Point(20, 20)
Me.Controls.Add(newPanel)
AddHandler newPanel.Paint, AddressOf GridPaint
End Sub
Private Sub GridPaint(ByVal sender As Object, ByVal e As PaintEventArgs)
Dim p As Panel = CType(sender, Panel)
Dim g As Graphics = e.Graphics
Dim width As Integer = CInt(p.Width / columns)
Dim height As Integer = CInt(p.Height / rows)
For x As Integer = width To p.Width Step width
g.DrawLine(SystemPens.ControlDark, x, 0, x, p.Height)
Next
For y As Integer = height To p.Height Step height
g.DrawLine(SystemPens.ControlDark, 0, y, p.Width, y)
Next
End Sub
Hope this helps
Tom
|
|
|
|
|
hello,am a new user to .NET. when i use a datetimepicker ( format set to short), along with some more controls in a form, the first time i executed the project, Iam able to get the focus in the Datetimepicker control(by pressing tab).
But if u select a new value in the control and use tab again am not getting the focus in datetime control. IS THIS A PATCH IN DATETIME CONTROL?
But when u click on it , the mousedown event makes the selection. so, iam thinking of invoking the mousedown evnet in the Enter Event(when the control is active by pressing tab). But not sure how to do it. or is there any other way of achiveing this?
Please help me at the earliest. Much Thanks.
Regards,Manivannan.P
|
|
|
|
|
Verify that all your controls have an appropriate TabStop value and that their TabIndex has the correct order you expect. There are no know problems with TabStop in Windows Forms.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
ive never used Visual Basic 6.0 before so i have very little knowledge on how to best do certain operations. I want the user to enter a value into a text field. This string can be anything but id like a number. Next i want to convert the string to a number and do some calculation on it (lets say input + 2).
Now in a later form i want to check and see if the value that was input earlier was a number or not before the conversion. If it was 11 for instance then i want to display "13" in a label. If it was anything besides a number (float or integer) i will display "Error" in a label.
What i did was create a global string variable named aInput. In the form where this value is input into a text box (txtInputA) i used aInput = Val(txtInputA.Text) to make the inputted string a number. This helps out the strings that are actually numbers, however it messes up because it makes a character string 0, so instead of getting an error later i'll get 2.
Example Of What I Want:
User inputs 3...........later on will display 5
User inputs 0...........later on will display 2
User inputs c76e........later on will display error
My guess is that there is a simple way to do this, or that im doing this in a very inconvenient method, but this was all i could do with my knowledge of this language.
|
|
|
|
|
In vb6 you can use the function IsNumeric() to determine whether a string can be converted into numeric values or not. It returns boolean True/False.
Hope it helps!
Edbert P.
Sydney, Australia.
|
|
|
|
|
The VB6 Numeric function has a shortcoming that I can't recall just now. I think it may parse a string like "12 34 56" that contains spaces automatically, and return 12 to you. Something like that.
Anyway, over the years I've found that you can't give a user too much information when it comes to error messages. Sometimes its just late and they can't tell a 0 from a o, or a 1 from a l. You're code knows exactly what the problem is, so you might as well be precise in your error message. Here's the routine I use.
Public Function Numeric(ByVal sNum As String, ByRef sErrMsg As String, _
Optional sOtherLegalChars As Variant, _
Optional vIncludeValidCharMsg As Variant) As Boolean
Const sValidDigits = "0123456789"
Dim sValidChars As String
Dim i As Integer
If IsMissing(sOtherLegalChars) Then sOtherLegalChars = ""
sValidChars = sValidDigits & sOtherLegalChars
If IsMissing(vIncludeValidCharMsg) Then vIncludeValidCharMsg = True
' Return error message only if a problem is found.
' Caller will get a true AND an empty errMsg string.
' or
' will get False AND an error message in the errMsg String.
sErrMsg = ""
If Len(sNum) = 0 Then
Numeric = False
sErrMsg = "Numeric value is missing or empty. Numeric digits were not found. [E0203262000]"
Exit Function
End If
For i = 1 To Len(sNum)
If InStr(sValidChars, Mid(sNum, i, 1)) = 0 Then
Numeric = False
sErrMsg = "The value " & quote(sNum) & " contains the non-numeric character " & _
quote(Mid(sNum, i, 1)) & " in position " & CStr(i) & ". [E0203262001]" & _
IIf(vIncludeValidCharMsg, vbCrLf & "The following are the only valid characters you can use: " & quote(sValidChars), "")
Exit Function
End If
Next i
On Error Resume Next
Dim L As Long
L = CLng(sNum)
Const vbOverflowErr = 6
If Err.number = 0 Then
Numeric = True
ElseIf Err.number = vbOverflowErr Then
sErrMsg = "Value " & quote(sNum) & " is too big. the biggest number allowed is about 2 billion. [E0301152200]"
Numeric = False
Else
sErrMsg = "Value " & quote(sNum) & " is not numeric [E03011522010]. The Microsoft error message is " & quote(Err.Description)
Numeric = False
End If
On Error GoTo 0
End Function
|
|
|
|
|
I'm sorry that I could not understand your question. But if I guest I think you want to know how to work with each control on the form. You might need the reference for each control. If you want to learn about the reference for each control try to check here[^], and let me know if it match what you need or not.
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
Ehmm.. not to work with each control. 
Okay, maybe I should explain my project.
I have a main form with a main menu. From this
menu I want to load a 'Form' that shows data from an Access database.
This 'Form' is a User Control designer.
But I cant find any information on how to open my UC designers
in my main form. I have tried to inherit with System.Windows.Forms.Form
which opens a new window with the UC designer loaded.
But I don't want this to happen.
I want my UC designer to be loaded directly in my main form. Not
open it in a new window.
This is how my menu code looks like to open my UC designer.
(Possibly its wrong to use showdialog() and or show() )
<br />
':: frmMain.vb ::<br />
Private Sub mnuItemNews_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuItemNews.Click<br />
Dim ucFrm As New ucnews<br />
ucFrm.ShowDialog(Me)<br />
ucFrm.Dispose()<br />
End Sub<br />
And in my code for the UC designer I have an inherit to system.windows.forms.form.
Like this...
<br />
':: ucnews.vb ::<br />
Public Class clsNews<br />
Inherits System.Windows.Forms.Form<br />
'some code...<br />
End Class<br />
I hope you understand my question here! it's marked with bolded text 
About my post yesterday!
Yesterday I tried to find information about UC designers.
But all papers, tutorials and guidelines made me confused because
they described User Controls as it was the designer form!
They never mentioned the User Control designer and how to use it within your
project.
|
|
|
|
|
[not sure if this most appropriate place to post, but..]
background:
i am working on a, mostly, basic mp3 player, so its functionality is exactly how i want, not how M$, etc, thinks it should be (just a personal player for fun). i am, however, using the Windows media player COM component (sry if terms incorrect). i dont use the visual one though, i declare a player in code using "... as new WMPLib.WindowsMediaPlayer()", and create my own gui. and if it matters: it automatically put the DLLs AxInterop.WMPLib and Interop.WMPLib into the bin folder for my project... also, i downloaded the WMP.10.SDK, which its using.
quick question:
as of now, my mp3 player [basic functionality] uses anywhere from 15-24mb ram. i was wondering if there is a less memory-intensive mp3 control like this that i could use? [instead of WMP]
update: sry, im using VB.net. i checked out the winAmp site, but could only find sdk for c/c++.
input appreciated, thanks.
------------------------
Jordan.
III
|
|
|
|
|
Dear All
Does anybody know if it is possible to create functon in a dll with vb and call it the same way as you call a function in a c++ dll?
Thanks
Henka
|
|
|
|
|
VB6 can only make COM .DLL's. It can't make a library .DLL like C++ can.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Add Dll in Reference
Import This Dlll Name Space in Module /Form
Example:
ccsMsgBox is a Dll Complined In Vb.6
Inlist is Function
Imports ccsmsgBox.Class1
Module M1
Inlist(.....)
End Module
|
|
|
|
|
I am currently building a mud client for fun and amhaving some problems with the interaction between the command entry textbox and the main textbox.
it breaks down like this i have a regular one line textbox at the bottom of the form. i have a multiline rich textbox in a tab control at the top of the form. i currently have the tabcontroll1_keypress event checking for the return key (Asc(e.KeyChar) = 13) then taking the text in the command box and putting it in the rich textbox. now i have bunch of other issues but mainly whats bothering me is every time i press enter the computer makes a noise. that windows set noise like if you try typing where you cant (like typing into a read only box). how can i make it stop beeping at me?
i have accepts return to true for the entry box and the form has no accept or cancel button set.
|
|
|
|
|
Try adding e.Handled = True at the end of the keypress event.
|
|
|
|
|
hi all to make a program which i can connect to the outlook through it and make a tree on my form the have inbox, deleted, sent, i want to display all the emails with their attachments to be able to save them on my pc on a specified folder not the personal one which i sprecified in the outlook, i use VB6.0 i need a code could any one help me.
Thanks alooooooooooooot 
Thanks alot
Metal Man
|
|
|
|
|
Try to check the information from here.[^]
A thousand mile of journey, begin with the first step.
APO-CEDC
Save Children Norway-Cambodia Office
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
