VB project needs to import data from excel file


I am making my first proper vb project and need help.  I need to import the data from an excel workbook. (sheet names, and data within the sheets i pick)

I know how to work with the string data once i get it (or I think I do) but haven't any idea how to get it in the first place.

To show my ignorance this is what i tried

Dim xlFile As Object
xlFile = OpenFileDialog.ShowDialog()

Which i never expected to work (and of course it didn't)

Does anyone know how to do this?

I am using visual studio express.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pramod BugudaiCommented:
Ok. Follow this link. It has an example to download also.

rowanscottAuthor Commented:
Thanks for that but I could not get it to work. I managed to set the excel reference but could not work out how to use the download sample even.  I am very new at vb (but well versed in vba.)

The users of the application will need to browse for an excel file which contains the data needed to run my application.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

If you dont know how to retrieve a file, perhaps this would be easier to understand if you broke this request down to individual tasks or you might get overwhelmed trying to understand it all. I have some subs/functions that will automatically import excel data (in a tabular format) directly into your dataset but again I'm unsure if you are proficient to work with a dataset at this point.

To show the open file dialog and retrieve the file name and path:

'Show dialog
If dlgOpen.ShowDialog <> DialogResult.OK Then Exit Sub

'File name and path
strExcelFile = dlgOpen.FileName

Call to excel function to import excel file into a datatable

Dim dtMyTable As New DataTable

dtMyTable = ImportExcelIntoDataTable(strExcelFile)

Public Function ImportExcelIntoDataTable(ByVal strFile As String, Optional ByVal strWorkSheet As String = "Sheet1", Optional ByVal blnHeaderRow As Boolean = True, Optional ByVal blnDataAsText As Boolean = True) As DataTable

        Dim dtXls As New DataTable
        Dim strDbCon As String = ""

        'Connection string to Excel file
        strDbCon = CreateExcelConnectionString(strFile, blnHeaderRow)

        Using con As New OleDbConnection(strDbCon)
            Dim daXls As New OleDbDataAdapter("Select * From [" & strWorkSheet & "$]", con)
        End Using 'con

        Return dtXls

    End Function

    Private Function CreateExcelConnectionString(ByVal strFile As String, Optional ByVal blnHeader As Boolean = True) As String

        Dim bldrCon As New OleDb.OleDbConnectionStringBuilder
        bldrCon("Provider") = "Microsoft.Jet.OLEDB.4.0"
        bldrCon("Data Source") = strFile

        'blnHeader specifies whether a header row is included or not
        'IMEX=1; - Tells the driver to read everything as intermixed text

        If blnHeader = True Then
            bldrCon("Extended Properties") = "Excel 8.0;HDR=YES"
            bldrCon("Extended Properties") = "Excel 8.0;HDR=NO"
        End If

        Return bldrCon.ConnectionString

    End Function

rowanscottAuthor Commented:
Thats more like it. Thanks a million.

Can I use

'File name and path
strExcelFile = dlgOpen.FileName

to actually create an excel workbook object in my code so that i can pick my way through the excel workbook using range and worksheet references rather than the data table

Something like

Dim wkBook As Excel.Workbook
        wkBook=  ???? strExcelFile???

The reason if you are wondering is because the workbook will contain very specific information rather than just a table of data because it is an earlier version of the new vb application.
Automating Excel is something completly different. What I have done is used ODBC to extract data from an existing Excel file without the need of the user even needing Excel. You can however manipulate it to specifiy ranges & worksheet names. My call example to import doesnt show it but there is a second parameter to allow you to specify the worksheet name, it defaults to "Sheet1" if nothing is specified.

The import function itself is specified to select all from the worksheet
("Select * From [" & strWorkSheet & "$]", con)
However you can change "Select *" to Select Cell Ranges

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rowanscottAuthor Commented:
I've got it thanks to your clues.

strName = OpenFileDialog.FileName
xlApp = New Excel.Application
wkBook = xlApp.Workbooks.Open(strName)

I'm sure that data table is going to come in handy later too but this is what i'm needing now.
rowanscottAuthor Commented:
Thanks. Very helpfull
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.