Printable View
Hello,
I've been doing some searching and so far only found related posts for previous versions of VB :(.
Essentially all I am wanting to do is have a user form where you enter a reference number, hit enter and it searches an excel 2010 spreadsheet for that reference number.
If the reference number is found it returns the associated name which is in the cell in the column on the right. (e.g. ref = A1 & name = B1).
Thanks in advance,
Chris.
Here are bits and pieces that when put together correctly should allow you to get what you are after. The idea is to read a worksheet using OleDb and read sheets into DataTable object or read a range back i.e.
A working example where you will need to alter the data providerCode:Sheet1$A3:B3
http://www.vbforums.com/showpost.php...05&postcount=4
For your data provider: Microsoft.ACE.OLEDB.12.0
In the connection string ConnectionNoHeader you may need to tweak the extended properties. I would run my project and see how things work prior to using it in your project.
Aother note, in my project the following gets data back
The sheet does not have headers so if we do not alias them for display purposes they will have headers of F1, F2 etc.Code:select F1 As Column1, F2 As Whatever, F3 As LastCol from [Sheet3$A3:D100]
Your example seems to return an entire grid rather than making a comparison with a user entered value and a value already existing within a database.
As it stands I have a successful OleDb connection to my .xls but can't compare/extract any data from it.
Even if I got to the stage where I could have a boolean result determining if the field existed I reckon I could figure it out from there.
Also, when I do manage to search the spreadsheet for a value.. would it just be best to create a loop or is there another option?
There's going to be a massive amount of data within this sheet so I'm wondering what sort of speed this is going to run at.
What I gave you is essentially the building blocks for working with Excel worksheet data.
You can read an entire sheet very fast using OleDb.
Example, a worksheet with say 3,000 rows by 20 columns should execute in less than two seconds.
Once you have the data back but the returning data into a DataTable then the DataTable becomes the DataSource of a BindingSource.
The BindingSource allows you to search, return the row number which then knowing the column you search on can convert the row/column into Excel format i.e. A3. Depending on how you perform a search, just one column or mutiple columns the operation using OleDb is fast, split second fast, unless you use a poor method of searching which would be hard to do.
In regards to doing a compare, no I do not show how to but as mentioned above provide building blocks for you to run with, expand on.
A good source of reference http://www.codeproject.com/KB/miscct...ta_access.aspx
Code for converting a numeric to alpha for providing the cell in Excel format.
Code:Public Module ExcelExtensions
<System.Diagnostics.DebuggerStepThrough()> _
<System.Runtime.CompilerServices.Extension()> _
Public Function ExcelColumnName(ByVal Index As Integer) As String
Dim chars = New Char() _
{ _
"A"c, "B"c, "C"c, "D"c, "E"c, "F"c, "G"c, "H"c, "I"c, _
"J"c, "K"c, "L"c, "M"c, "N"c, "O"c, "P"c, "Q"c, "R"c, _
"S"c, "T"c, "U"c, "V"c, "W"c, "X"c, "Y"c, "Z"c _
}
Index -= 1
Dim columnName As String
Dim quotient = Index \ 26
If quotient > 0 Then
columnName = ExcelColumnName(quotient) + chars(Index Mod 26)
Else
columnName = chars(Index Mod 26).ToString()
End If
Return columnName
End Function
End Module
Thanks for that link, really helpful article.
I've actually managed to use your original post to piece together what I need, just working on moving through the rows and comparing variables.
Quick question, I've read about an Excel function Range.Find which returns the number of rows used. Just wondering if this is usable from VB or would it make more sense to just loop until the cell = ""?
Thanks for all the help, really appreciated.
Following on from the previous question, could I make use of the row feature you have included in that sample?
The only problem I foresee is the fact that I'm not using a range of fields like you have, instead I'm specifying a cell.
Also with the line below, how can I make the From [] into a variable name? I need to be able to change it in order to be able to loop through the rows.
vb Code:
I whipped together an example which is attached in VS2008. The code featured below does not directly solve your task but provide methods to work with to put together your own solution. Also note by whipping a project together there may be extra/unneeded code as that is what whipping together is about but most important it works.
Points of interest.
The connection object is form level as this is better than creating a new connection each time you open the data.
This example searchs a worksheet which the first row has headers, searchs the column LastName for Smith. Note how the first column and the variable for the where clause uses embedded expressions which would allow you to do replacement parms. One column is hidden from view in the DataGridView as we do not need to see it.
The following example loads a worksheet, first searches on first name column the the last name column. Note how the positioning is determined in that we need to increment the position an item is located because the BindingSource is zero based and we must compensate for the header row.Code:Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim LastName As String = "Smith"
Dim cmd As New OleDbCommand
Dim dr As System.Data.IDataReader
If cn.IsClosed Then
cn.Open()
End If
cmd = New OleDbCommand()
cmd.Connection = cn
cmd.CommandText = _
<SQL>
SELECT
<%= CustomerIdentifier %>,
FirstName,
LastName
FROM
[Customers$]
WHERE
LastName="<%= LastName %>"
</SQL>.Value
Dim dtData As New DataTable
dr = cmd.ExecuteReader
dtData.Load(dr)
dtData.Columns(CustomerIdentifier).ColumnMapping = MappingType.Hidden
bsCustomers.DataSource = dtData
DataGridView1.DataSource = bsCustomers
DataGridView1.Columns("FirstName").HeaderText = "B"
DataGridView1.Columns("LastName").HeaderText = "C"
End Sub
Code:Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim cmd As New OleDbCommand
Dim dr As System.Data.IDataReader
If cn.IsClosed Then
cn.Open()
End If
cmd = New OleDbCommand()
cmd.Connection = cn
cmd.CommandText = _
<SQL>
SELECT <%= CustomerIdentifier %>, FirstName, LastName FROM [Customers$]
</SQL>.Value
Dim dtData As New DataTable
dr = cmd.ExecuteReader
dtData.Load(dr)
dtData.Columns(CustomerIdentifier).ColumnMapping = MappingType.Hidden
bsCustomers.DataSource = dtData
ListBox1.Items.Clear()
Dim Position As Integer = bsCustomers.Locate("LastName", "Smith")
If Position <> -1 Then
'
' We add +2 for position because we need 1 for minus header and
' the second 1 for the BindingSource is zero based
'
ListBox1.Items.Add(String.Format("Smith was located at C{0}", Position + 2))
End If
Position = bsCustomers.Locate("FirstName", "Kevin")
If Position <> -1 Then
ListBox1.Items.Add(String.Format("Kevin was located at B{0}", Position + 2))
End If
End Sub
You could be use native Excel methods as well, e.gQuote:
Originally Posted by OhGreen
'Require Reference to Microsoft Excel XX.X ObjectLibrary
Code:Imports System.IO
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Threading
Imports System.Globalization
'------------------------------
'Usage
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim result As String() = testFind("C:\myXlFile.xls", TextBox1.Text, "A")
MsgBox(result(0) & vbLf & result(1))
End Sub
Public Function testFind(ByVal xlfileName As String, ByVal strToSearh As String, ByVal colName As String) As String()
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US")
Dim result() As String = New String(1) {}
Dim xlApp As Excel.Application = Nothing
Dim xlBooks As Excel.Workbooks = Nothing
Dim xlBook As Excel.Workbook = Nothing
Dim xlSheet As Excel.Worksheet = Nothing
Dim xlRange As Excel.Range = Nothing
Dim xlFindRange As Excel.Range = Nothing
Dim missing As Object = Type.Missing
Try
xlApp = New Excel.Application()
xlApp.Visible = True
xlApp.DisplayAlerts = False
xlApp.UserControl = True
xlBooks = DirectCast(xlApp.Workbooks, Excel.Workbooks)
'Change full path of Excel file here:
xlBook = DirectCast(xlBooks.Open(xlfileName, True, False, missing, "", missing, _
False, missing, missing, True, missing, missing, missing, missing, missing), Excel.Workbook)
'get first sheei in the workbook
xlSheet = DirectCast(xlBook.Worksheets.Item(1), Excel.Worksheet)
'get used range of certain sheet
xlRange = DirectCast(xlSheet.UsedRange, Excel.Range)
'' get colunmn "A" of this range
xlRange = DirectCast(xlRange.Columns(colName), Excel.Range)
xlFindRange = DirectCast(FindInColumn(xlRange, strToSearh, Nothing, _
Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, _
Microsoft.Office.Interop.Excel.XlLookAt.xlWhole), Excel.Range)
If xlFindRange Is Nothing Then
MsgBox("Referenced value was not found")
End If
result(0) = xlFindRange.Value.ToString()
result(1) = xlFindRange.Offset(0, 1).Value.ToString()
DirectCast(xlBook, Excel._Workbook).Close(True, missing, missing) ''<-- see here
releaseObject(xlRange)
releaseObject(xlSheet)
releaseObject(xlBook)
releaseObject(xlBooks)
Catch ex As Exception
MsgBox("Error:" & vbLf & ex.Message & "Trace: " & vbLf & ex.StackTrace)
Finally
xlApp.Quit()
releaseObject(xlApp)
End Try
Return result
End Function
' autor unknown
Function FindInColumn(ByVal rng As Excel.Range, _
Optional ByVal what As String = "*", _
Optional ByVal after As Excel.Range = Nothing, _
Optional ByVal lookin As Microsoft.Office.Interop.Excel.XlFindLookIn = Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, _
Optional ByVal lookat As Microsoft.Office.Interop.Excel.XlLookAt = Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, _
Optional ByVal order As Microsoft.Office.Interop.Excel.XlSearchOrder = Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, _
Optional ByVal direction As Microsoft.Office.Interop.Excel.XlSearchDirection = Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, _
Optional ByVal matchcase As Boolean = False) As Excel.Range
If after Is Nothing Then
after = rng.Cells(1, 1)
End If
Return rng.Find(What:=what, _
After:=after, _
LookIn:=lookin, _
LookAt:=lookat, _
SearchOrder:=order, _
SearchDirection:=direction, _
MatchCase:=matchcase)
End Function
Public Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
obj = Nothing
Catch ex As System.Exception
System.Diagnostics.Debug.Print(ex.ToString())
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Personally I have a problem with native Excel methods but Fixo is correct in that this is a valid way to do this.
My first choice is using Aspose Cells which does not require any references or even have Excel installed for that matter. This is what I use for work but for many the cost is too much to justify.
Second choice is OleDb simple because other than no ability to format data the only other issue is not being able to compile on a 64bit OS as 64bit since 64bit OS do not have OleDb provider to Office but do have it for 32bit so a project must be compiled as 32bit.
Last option is Office automation because when upgrading from one office package to another can break your code plus it requires more code than OleDb to read/write data (again without the ability to format data)
In regards to formatting data and using OleDb a smart idea is to create your Excel file using a preformatted template, same with Aspose Cells although Aspose Cells can format easily anything you want within a workbook.
I've got to the point now where I'm not progressing any further and so I'll try and keep this concise to see if anyone can reply with a direct answer.
The SQL segment I have at the moment returns a value I specify by directly typing it into the SQL as below shows.
vb Code:
I want to be able to create a variable which I can use in the SQL rather than [A4:A4]. This would allow me to change the variable in a loop to progress through the list so that it compares each value in the spreadsheet to the one that the user has entered.
For example:
vb Code:
You can loop in a Do-While or For-Next where the For-Next is demoed below.
Code:Private Sub Demo()
Dim Statement As String = ""
Dim RowIndex As Integer = 4
Dim ColumnName As String = ""
For Iterator As Integer = 1 To 62
ColumnName = Iterator.ExcelColumnName
Statement = String.Format("SELECT * FROM {0}", _
String.Format("[{0}{1}:{0}{1}]", _
ColumnName, _
RowIndex))
Console.WriteLine("{0,3} {1}", Iterator, Statement)
Next
End Sub
Brilliant thanks Kevin.
I'm getting an error ('ExcelColumnName' is not a member of 'Integer'.) for the below line:
vb Code:
Did you add the code modules hereQuote:
Originally Posted by OhGreen
here is the signature
Code:Public Function ExcelColumnName(ByVal Index As Integer) As String
Edit: Fixed.
If the solution is good for you please mark this thread resolved.Quote:
Originally Posted by OhGreen
Thanks for the help Kevin, I've got this up and running now.
Rated & Solved.