|
Hi
I'm trying to make an app that opens a file with excel in vb.net.
I'm trying to separate the text in a sheet with TextToColumns, but i'm stuck on how to do that. I have tested this but get an error that "TextToColumns method in the Range class has failed". Here's what I tried:
Private Sub btnOPEN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOPEN.Click<br />
<br />
OpenFD.ShowDialog()<br />
Dim strfilename As String<br />
strfilename = OpenFD.FileName<br />
<br />
Dim xlApp As Excel.Application<br />
Dim xlWorkBook As Excel.Workbook<br />
Dim xlWorkSheet As Excel.Worksheet<br />
Dim Range As Excel.Range<br />
<br />
xlApp = CreateObject("Excel.Application")<br />
xlApp.Visible = True<br />
<br />
xlWorkBook = xlApp.Workbooks.Open(strfilename)<br />
xlWorkSheet = xlWorkBook.ActiveSheet<br />
Range = xlWorkSheet.Range("a1", "a70")<br />
Range.Font.Bold = True<br />
Range.TextToColumns("a3", Microsoft.Office.Interop.Excel. _<br />
XlTextParsingType.xlDelimited, _<br />
Microsoft.Office.Interop.Excel.XlTextQualifier. _<br />
xlTextQualifierDoubleQuote, _<br />
True, True, False, True, True, False, False, _<br />
False, False, False, False) <br />
End Sub
|
|
|
|
|
Member 4435051 wrote: Range.TextToColumns("a3", Microsoft.Office.Interop.Excel. _
XlTextParsingType.xlDelimited, _
Microsoft.Office.Interop.Excel.XlTextQualifier. _
xlTextQualifierDoubleQuote, _
True, True, False, True, True, False, False, _
False, False, False, False)
Looking at the docs on TextToColumns, you didn't specify the first arugment correctly. The method is looking for a Range object and you gave it a string ("A3") instead. I don't do Office interop, so this may be wrong, but I think you're looking for this or something very similar:
Range.TextToColumns(xlWorkSheet.Range("A3"), ...
|
|
|
|
|
Thank's a lot.
This help is what I really needed.
|
|
|
|
|
It did work great.
Thank's
|
|
|
|
|
Hello Friends,
I am using datagrid to display records from access db. I got the first field as ID which is a primary key. As users should not edit it i made that column as read only in the datagrid but i need that column to be editable when users tries to add a new record(row). I am using windows forms (vb.net). Could anyone solve this issue?
|
|
|
|
|
Member 3936356 wrote: I got the first field as ID which is a primary key. As users should not edit it i made that column as read only in the datagrid but i need that column to be editable when users tries to add a new record(row).
You really should not be allowing users to edit the primary key field. Assigning a primary key should be left up to the database, either by generating a key from entered data in a standardized format or by an autonumber.
|
|
|
|
|
Hello Friends,
I am newbie when it comes to vb.net and i need to import excel sheet into access db using vb.net application. Here is the code i use for now to upload excel sheet and it works perfectly but i when i upload the file again it should include only the new records by checking the existence of ID field which i use as primary key. For now it pops up the message"Table Exists already".Could anyone help me pls?
Dim AccessConnection As New System.Data.OleDb.OleDbConnection
Dim _conn As String
Dim SchemaTable As DataTable
AccessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=somefile.mdb"
AccessConnection.Open()
SchemaTable = AccessConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, "mytable"})
If SchemaTable.Rows.Count <> 0 Then
MsgBox("Table Exists already" )
Else
Dim _filename As String = "myexcelsheet.xls"
_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=Excel 8.0;"
Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Dim _command As OleDbCommand = New OleDbCommand
_command.Connection = _connection
Try
_command.CommandText = "SELECT * INTO [MS Access;Database=somefile.mdb].[accesstbname] FROM [Sheet1$]"
_connection.Open()
_command.ExecuteNonQuery()
MessageBox.Show("The Data Imported Successfully!")
Catch e1 As Exception
MessageBox.Show("Error!.Your Excel Spread Sheet has some problems.")
End Try
End If
|
|
|
|
|
Member 3936356 wrote: _command.CommandText = "SELECT * INTO [MS Access;Database=somefile.mdb].[accesstbname] FROM [Sheet1$]"
In order to do what you want, you have to retrieve the data from the Excel sheet into a DataTable, iterate over the rows in the DataTable, verify that the row's ID is NOT already in the database, then execute an INSERT query to add the new row to the table if it' doesn't already exist.
|
|
|
|
|
Thanks for you suggestions. But still as i am a newbie i donot have any idea about checking excel sheet rows against database unique id field and insert ones that does not exist. I expect some help in coding.
|
|
|
|
|
Can someone please look at this and make some suggestions on how to improve the performance of this snippet this is killing one of our apps.
Private Function fbParseParameters(ByVal sArgs() As String) As Boolean
Dim tTemp As String
'
fbParseParameters = True
Dim iII As Integer = 0
If sArgs.Length > 0 Then
' each parameter is broken into a seperate array item.
While iII < sArgs.Length
tTemp = sArgs(iII).ToUpper
' look at the parms, and figure out which is which...
If tTemp.IndexOf("DATABASEHOST=") > 0 Then
myConnectionInfo.tDatabaseHost = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("USERNAME=") > 0 Then
myConnectionInfo.tUserName = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("USERNUMBER=") > 0 Then
myConnectionInfo.lUserNumber = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("PASSWORD=") > 0 Then
myConnectionInfo.tPassword = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("LOCALE=") > 0 Then
myConnectionInfo.tCurrentLocaleCode = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("LOCALEDESCR=") > 0 Then
myConnectionInfo.tCurrentLocale = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("MODULE=") > 0 Then
strApplicationInfo.tCurrentModule = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("REPORT=") > 0 Then
strApplicationInfo.tReport = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("CLIENT=") > 0 Then
strSystemInfo.lCurrentClient = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("COMPONENTNUM=") > 0 Then
strSystemInfo.lComponentNum = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("ISTHERAPY=") > 0 Then
Dim tTemp2 As String
tTemp2 = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
If tTemp2 = "-1" Or tTemp2 = "0" Or tTemp2 = "1" Or tTemp2.ToUpper = "TRUE" Or tTemp2.ToUpper = "FALSE" Then
strSystemInfo.bIsTherapy = CBool(tTemp2)
End If
End If
If tTemp.IndexOf("PLANTYPE=") > 0 Then
strSystemInfo.tPlanType = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("GRIDMODE=") > 0 Then
strSystemInfo.tGridMode = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("HEADERNUMBER=") > 0 Then
strSystemInfo.lHeaderNum = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("DISCIPLINE=") > 0 Then
strSystemInfo.lDisciplineNum = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
If tTemp.IndexOf("THERAPISTNUM=") > 0 Then
strSystemInfo.lTherapistNum = tTemp.Substring(tTemp.IndexOf("=") + 1, _
(tTemp.Length - (tTemp.IndexOf("=") + 1)))
End If
iII += 1
End While
Else
fbParseParameters = False
Exit Function
End If
' We must at least have username, password, and database host in order to do anything...
If myConnectionInfo.tUserName = String.Empty Or _
myConnectionInfo.tPassword = String.Empty Or _
myConnectionInfo.tDatabaseHost = String.Empty Then
fbParseParameters = False
End If
End Function
Here is an example of the parm string
/DATABASEHOST=TEST/DATABASENAME=TEST /USERNAME=TEST /PASSWORD=TEST /LOCALE=GW /LOCALEDESCR=Glenwood /MODULE=CL /REPORT=TRMonthlyAttendance
Here is where it is loaded into the array
' must format these into an array ...
tParms = txtParm.Text.Split("/")
Dim iII As Integer
For iII = 1 To tParms.Length - 1
tParms(iII) = Trim("/" & tParms(iII))
Next
Humble Programmer
|
|
|
|
|
You're using tTemp.Substring(tTemp.IndexOf("=") + 1 multiple times in each block. If you defined your search strings as constants, you could skip this. For example:
private const DATABASEHOST_ARG As String = "DATABASEHOST="
...
If tTemp.IndexOf(DATABASEHOST_ARG) > 0 Then
myConnectionInfo.tDatabaseHost = tTemp.Substring(DATABASEHOST_ARG.Length(), _
(tTemp.Length - DATABASEHOST_ARG.Length())
End If
I didn't compile, so formatting may be off - and make sure you check to see that I've got the Substring starting index correct. I'm pretty sure that the '+ 1' is no longer necessary.
Simply rinse and repeat for your other blocks and your helpful compiler should replace a lot of "IndexOf" operations with simple math!
|
|
|
|
|
Thank you I will add that with some other things that I have done to it and that should help.
Humble Programmer
|
|
|
|
|
Split the string with delimiter "/" into string array, then from there split with delimiter "=" then put the pair into HashTable.
Then assign your class from the HashTable (check for empty), ie
strSystemInfo.lTherapistNum = hashTable["THERAPISTNUM"].Value;
|
|
|
|
|
In VB6 I used to use the following to automatically select an item in a listbox when a user types in a textbox:
[Module]
Public Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, ByVal wParam As Long, _
ByVal lParam As String) As Long
Public Const LB_SELECTSTRING = &H18C
...}
[Form]
Private Sub txtForLstAllUsers_Change()
If txtForLstAllUsers.Text <> "" Then
SendMessage lstAllUsers.hwnd, LB_SELECTSTRING, -1, txtForLstAllUsers.Text
End If
End Sub
I tried copying and pasting into a VB .Net application and I am getting the following error:
"hwnd is not a member of Systems.Windows.Forms.Listbox."
Also, I noticed there is no Change event for textboxes in .Net.
Can anyone tell me how to re-create this functionality in a .Net project?
Any help will be greatly appreciated!!
|
|
|
|
|
I think the property is now Handle (lstAllUsers.Handle). And try the textbox's TextChanged event.
|
|
|
|
|
Thanks for your response, JZ. I will give it a try.
I did, however, in the meantime find something that works rather well, in lieu of the listbox -
I used a combobox and selected "simple" as the combo type. That put the textbox right over a drop down that's visible; the control looking like a listbox with a textbox on top. I don't have the project in front of me, but I think I may have set another property for the combo that automatically auto selects when someone types in the textbox part of the combo.
|
|
|
|
|
Hi There
This is my first post, I need help
I've a Datagridview filling from tbx and combobox, I want to put a Checkbox in each row (I don't how) so I want to delete a row checked from the datagridview
Please help, I very rookie
I'm doing this...
Public DRow As DataRow<br />
Public Carrito As New DataTable("Detalle")<br />
<br />
Sub Configuratabla()<br />
Dim DC0 As New DataColumn("Item", System.Type.GetType("System.Int64"))<br />
Dim DC1 As New DataColumn("Cantidad", System.Type.GetType("System.Int64"))<br />
Dim DC2 As New DataColumn("Detalle", System.Type.GetType("System.String"))<br />
Dim DC3 As New DataColumn("Valor", System.Type.GetType("System.Int64"))<br />
Dim DC4 As New DataColumn("Descuento", System.Type.GetType("System.Int64"))<br />
Dim DC5 As New DataColumn("Total", System.Type.GetType("System.Int64"))<br />
<br />
'DC4.Expression = "cantidad * Valor "<br />
With Carrito.Columns<br />
.Add(DC0)<br />
.Add(DC1)<br />
.Add(DC2)<br />
.Add(DC3)<br />
.Add(DC4)<br />
.Add(DC5)<br />
<br />
End With<br />
<br />
GrillaDetalle.DataSource = Carrito<br />
<br />
End Sub<br />
<br />
<br />
<br />
DRow = Carrito.NewRow()<br />
DRow(0) = Carrito.Rows.Count + 1 'Item<br />
DRow(1) = CInt(TbxCan.Text) 'Cantidad<br />
DRow(2) = CbxPro.SelectedItem 'Detalle<br />
DRow(3) = Valor1<br />
DRow(4) = CbxDes.SelectedItem 'Descuento<br />
DRow(5) = (CInt(TbxCan.Text)<br />
Carrito.Rows.Add(DRow)
|
|
|
|
|
hi,
i hope that this can help you :
1-to add checkboxcolumn:
Dim mycolumn As New DataGridViewCheckBoxColumn
mydatagridview.Columns.Insert(0, mycolumn)
2-to delete row:
if mydatagridview.SelectedRows(i).Cells(0).Value=true then
mydatagridview.rows.removeat(indexofmayrow)
end if
Il principe 
|
|
|
|
|
...or simply add another Column of Type Boolean to your DataTable
Dim DC6 As New DataColumn("Delete", System.Type.GetType("System.Boolean"))
.Add(DC6)
'***initialize Column
DRow(6) = false
After Binding the DataTable to the DataGridView it will automatically create a CheckBoxColumn out of the DataType Boolean.
|
|
|
|
|
I am writing a VB 6 program that shd run on the network. A user has to login before using the program. Now, I want to prevent multiple user login using one user account. It shdn't allow or prevent a user to login whilst his/her account is in use(the same Cafe Timer Login.
Also, how do i make the client send Username (Text1.Text) and Password (Text2.Text) to the server and make the server put them into the Vars strUser, and strPass?
Please, can anyone help me with this? I would be very grateful if you could help me with this.
|
|
|
|
|
kingascona wrote: I am writing a VB 6 program that shd run on the network. A user has to login before using the program. Now, I want to prevent multiple user login using one user account. It shdn't allow or prevent a user to login whilst his/her account is in use(the same Cafe Timer Login.
You have to track the user logins AND logouts. When the user attempts to log in, you're code has to check to see if the ID is already logged in and, if so, deny the new login until the old login is logged out. You'd normally track this data in your database users table, or some other session tracking table.
But, there's a catch. What if the user's machine crashes and is forced to reboot without logging out of your app? You need some kind of tool that can clear the now-stuck login. Say the last login was 30 minutes ago, but there hasn't been any activity for the last 15. You need to be able to detect and idle session and automatically log it out at some point. Or write some other tool that an administrator can use to clear the login. Otherwise, the user who had to reboot their machine without properly logging out will never be able to log back in.
|
|
|
|
|
I am able to use SQL commands to access any table & any columns in MS access (or in Excel) table. However, when I try to search columns with spaces (ie: Employee IDNumber, Employee Name, ...) then I have errors
If I change to Employee_IDNumber, Employee_Name then it is OK
<br />
Dim m_sModification As String = "UPDATE [Table Name$] SET Employee_Name = 'Jonny White' WHERE Employee_IDNumber = '12345678'"<br />
<br />
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConnection1)<br />
conn1.Open()<br />
But I have the MS Access (or Excel) tables having Column Headers with spaces between, I try to avoid changing all of them with xxxxx_xxxxx
Do we have other ways to deal with this issue?
Thank in-advanced
modified on Thursday, April 3, 2008 10:34 AM
|
|
|
|
|
You have to enclose and table names and columns with spaces, or names with the potential to have spaces, in square brackets ([]). Like this:
UPDATE [Table Name$] SET [Employee Name] = 'Jonny White' WHERE [Employee IDNumber] = '12345678'
But, keep in mind that having spaces in table and column names is considered bad practice. Avoid doing this if at all possible.
|
|
|
|
|
Hi Dave,
It works! I am awared of your comments, however the existed files that has columns with spaces being done long time ago & I am not allow to remove spaces
Many thanks 
|
|
|
|
|
Hello everyone...i need some help!!!
I am currently working on a project and have stumbled across a problem. I wish to display data from a database into a combo box on a VB.net form. I can add data on forms that saves in the database but you cannot view the newly entered data in the combo boxs on the VB forms. Is there a code that refreshes the database connection every time new data is entered on the VB forms? Note that the data i wish to display in the combo box is entered on a seperate form on VB.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
