1

I am converting an old VBA application to VB.NET. This application contains several tools, and I need some help converting one of them over.

Essentially, there is an Excel spreadsheet full of customer information such as the customer's company name, model number, serial number, warranty end date, plan end date, city/country, alt company name and comments.

The tool has a list view control with all the matching columns, same as the Excel spreadsheet, and there is a text box in which the user can type some search criteria, such as a serial number or company name. Any matching results from the Excel spreadsheet are put into the list view with the data from the columns of the spreadsheet, sorted into the correct matching column for the list view control.

The current VBA tool is easily able to import this data into the form as it (the form) has easy access to the spreadsheet data - however; in my case, with my program being external to Excel and VBA - I can't do this without importing the data first.

So my question is:

  1. How do I import an excel spreadsheet into my VB.NET form to use the data (this file is always in the exact same directory on every machine so a file browse dialog is not necessary, just storing the directory of the Excel spreadsheet should do fine).

  2. How do I make it/what would be some appropriate code that allows the user to enter a search criteria, and populate the list view with matching results

  3. When the user completes a search and the list view is populated with matching data from the Excel spreadsheet, how do I sort the data into the fields (sort the data in the columns in Excel into the columns in VB.NET list view, so matching result in Company column from Excel would go into a list view column named 'Company').

I have attached an example of the Excel spreadsheet which contains the data.

https://drive.google.com/file/d/1SiFZQiz8gsHfKgGIJZmLfrbmfhQkot7B/view?usp=sharing]1

This is what the list view looks like:

enter image description here

Improve this question

1 Answer 1

Reset to default
1

We don't really answer questions this large on SO; there's an entire app here and you're asking for advice on how to code it all from start to finish, which includes a good chunk of design work (as opposed to implementation) - a subjective or opinion based thing in itself. I'll answer so as to give some pointers for a direction you can head in, but you should aim to keep future questions quite narrowly focused.

How do I import an excel spreadsheet into my VB.NET form to use the data (this file is always in the exact same directory on every machine so a file browse dialog is not necessary, just storing the directory of the Excel spreadsheet should do fine).

Use an OpenFileDialog to have the user select the file or hard code some logic to find it at a fixed location. Take a look on connectionstrings.com for the connection string you'll need to make the Access/JET database driver connect to the file (it can read Excel files and treat them like DBs). Carl Prothman has a usable looking block of code here - it's C# but you can either convert it to VB using an online converter or just read and understand the general flow of "make new DataTable, make new DataAdapter, set connection string and SQL string of DataAdapter to something sensible, call DataAdapter.fill" and write the VB

Read the file using a SQL query (SELECT * FROM sheetname), and use a DataAdapter to fill a DataTable with the contents of the file

How do I make it/what would be some appropriate code that allows the user to enter a search criteria, and populate the list view with matching results

Provide a text box on the form for the user to type in. Have a button or keypress of return initiate a search. To perform the search simply set the .Filter property of the DataView object returned by the DataTable's DefaultView property. You can find more information on what syntax the Filter property requires in the documentation for the .Expression property of a data column - sounds like an odd place to keep it but it'll make sense later!

When the user completes a search and the list view is populated with matching data from the Excel spreadsheet, how do I sort the data into the fields (sort the data in the columns in Excel into the columns in VB.NET list view, so matching result in Company column from Excel would go into a list view column named 'Company').

"Sort the data into fields" doesn't really make sense in the context of what I'm about to tell you, and sorting as a phrase typically refers to changing the order of rows of data rather than assigning data into columns (which is what I think you mean)

To get your data in the DataTable to show up in a DataGridView the only thing you need to do is set the .DataSource property of the DGV to equal the DataTable instance. The DGV will actually bind to the DefaultView so any filters will take effect. The DGV will automatically render the contents of the table once data binding is set up with this single line of code

All in I would expect this app would be possible to accomplish in around 10 lines of code:

'make new DataTable, possibly as a class level variable for ease 

'event handler for read_file button click
'make new DataAdapter, with connection string pointing to the excel file and SQL string to select the data
'fill DataTable
'assign data source of DataGridView, to filled DataTable


'event handler for filter button click
'set .filter property of class level DataTable.DefaultView property
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

I appreciate this is a lot to ask for, but I really need some example code.
@JoshFace which line of the final listing are you not able to accomplish?
@Mary All of it to be honest. I could do it but I don't know how to import the excel file and then use the data. I mostly know the code for the search.
To be fair, I DID link to another SO question where the guy is reading from an Excel file using a db driver (see the bit in my answer where I mention Carl Prothman. Here's another example I found along the same lines: stackoverflow.com/questions/18511576/…
Thing is, you've rocked up and said "I need an app that does X, giv me teh codez" and we aren't a code writing service - there are hire-a-freelancer sites for that. Ordinarily questions as broad as you've asked are just closed outright as "too broad" but I figured instead I'd put some time to guiding you on how to design an app to meet your needs, including how to sketch out the algorithms. I did this because though the question is broad, it can be done in a few lines of code and it represents a great learning oppo for you to get to grips with db drivers, data binding, ui design etc. Have a go
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.