Importing an excel file and displaying it on a datagridview

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 17578 Views - Last Post: 21 December 2013 - 06:04 AM Rate Topic: ***** 1 Votes

#16 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 11:22 AM

That's fine, this should help

EDIT:
Here's the situation. Specifying a filename without a path using teh SaveCopyAs method of the ActiveWorkbook apparently defaults to the path being the My Documents folder.

BUT

Specifying just a filename outside of that method is defaulting to the path where the executable resides.

Therefore you need to specify it for the connectionstring using the method in the link.

This post has been edited by CharlieMay: 02 April 2013 - 11:29 AM

Was This Post Helpful? 1
  • +
  • -

#17 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3354
  • View blog
  • Posts: 11,354
  • Joined: 12-December 12

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 11:25 AM

View PostMrHappEnose, on 02 April 2013 - 06:20 PM, said:

I didn't want to include the whole filepath because I want it to save and load from the My Documents folder. The application is going to be run on different computers.

I was thinking that maybe later I could let the user specify the exact location they wanted. But right now I'm just trying to get the save/load part working, which is why I set the DirBox text to read-only.

Investigate Environment.SpecialFolder.MyDocuments, and things like System.IO.Path.DirectorySeparatorChar to make your application work on different computers and OSes.
Was This Post Helpful? 1
  • +
  • -

#18 MrHappEnose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 01-April 13

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 12:20 PM

It works now! Saves and loads from the documents folder. Thanks guys.
Was This Post Helpful? 0
  • +
  • -

#19 Dominofoe  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 21-December 13

Re: Importing an excel file and displaying it on a datagridview

Posted 21 December 2013 - 06:04 AM

Sorry for the above post, i cant find the edit button to fix.
Just registered to say the this code -


'Build a connection string template
Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                 "Data Source={0};" & _
                                                 "Extended Properties=""Excel 12.0;" & _
                                                 "HDR=Yes;IMEX=1"""

'sFilename contains the path and file information to your workbook
'use string.format to fill in the placeholders of your connectionstringtemplate.
Dim connectionString As String = String.Format(connectionStringTemplate, sFileName)
'Create a select statement
Dim sqlSelect As String = "SELECT * FROM [IMPORT$];" ' Where you have a sheet named IMPORT 

Dim workbook As New DataSet 'Instantiate a dataset
'instantiate an adapter and pass it the sql statement and the connectionstring
Dim excelAdapter As System.Data.Common.DataAdapter = _
    New System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)

excelAdapter.Fill(workbook)'Use the adapter to fill the dataset from its query result.
'Set your datagridview's datasource to your datatable 
dgvImport.DataSource = worksheet.Tables(0)


Works FLAWLESSLY. i am very grateful.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2