By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,154 Members | 1,024 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,154 IT Pros & Developers. It's quick & easy.

Excel to VB.net to Access HELP!!

P: 3
HELLO world, this is my first post and heres my problem.

I have an excel spreadsheet with 22 columns, and undefined amount of rows. I need vb.net to read the data from excel. Then use that data to populate an existing access database table that has been added to visual studio. I've tried multiple approaches and have come to a standstill.

this was approach 1

Expand|Select|Wrap|Line Numbers
  1. Imports System.Data.OleDb
  2. Imports System.Data.SqlClient
  3. Imports Excel = Microsoft.Office.Interop.Excel
  4.  
  5. Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportFile.Click
  6.         'Open File Dialog to Find the file.
  7.         FD.InitialDirectory = "C:\"
  8.         FD.Title = "Choose an Excel File to load"
  9.         FD.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*"
  10.         FD.ShowDialog()
  11.         'Open File
  12.         FD.OpenFile()
  13.  
  14.         'Read Excel Filei
  15.         Dim xlApp As Excel.Application
  16.         Dim xlWB As Excel.Workbook
  17.         Dim xlSheet As Excel.Worksheet
  18.  
  19.  
  20.         Dim range As Excel.Range
  21.         Dim intRowCount As Integer
  22.         Dim intColumnCount As Integer
  23.  
  24. Dim taMeasurement As New FOSRLab3DataSetTableAdapters.Sample_MeasurementsTableAdapter
  25.         xlApp = New Excel.ApplicationClass
  26.         xlWB = xlApp.Workbooks.Open(FD.FileName)
  27.         xlSheet = xlWB.Worksheets(1)
  28.         range = xlSheet.UsedRange
  29.         Dim ta As New FOSRLab3DataSetTableAdapters.Sample_MeasurementsTableAdapter
  30.  
  31.  
  32.  
  33.         For intRowCount = 2 To range.Rows.Count
  34.         SiteID = CType(range.Cells(intRowCount, 1), Excel.Range)
  35.         drSiteID.SiteName = SiteID.ToString
  36.         Next
  37.       xlWB.Close()
  38.         xlApp.Quit()
  39.         releaseObject(xlApp)
  40.         releaseObject(xlWB)
  41.         releaseObject(xlSheet)
  42.  
  43.  
  44.     End Sub
  45.  
Heres another failed approach
Expand|Select|Wrap|Line Numbers
  1. 'For Each row As Microsoft.Office.Interop.Excel.Worksheet
  2.     'xlSheet.Cells(1, 1) = "SiteName"
  3.     'Next
  4.     'MsgBox(xlSheet.Cells(1, 1).ToString)
  5.  
  6.  
  7.  
  8.     'Try
  9.     'Dim MyConnection As System.Data.OleDb.OleDbConnection
  10.     'Dim DtSet As System.Data.DataSet
  11.     '     Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
  12.     '    MyConnection = New System.Data.OleDb.OleDbConnection("provider = Microsoft.Jet.OLEDB.4.0;"" Data Source = FD.FileName';""Extended Properties = Excel 8.0;")
  13.     '   MyCommand = New System.Data.OleDb.OleDbDataAdapter("SELECT * from [Site Data]", MyConnection)
  14.     '  MyCommand.TableMappings.Add("Table", "TestTable")
  15.     ' DtSet = New System.Data.DataSet
  16.     'MyCommand.Fill(DtSet)
  17.     'DataGridView1.DataSource = DtSet.Tables(0)
  18.     'MyConnection.Close()
  19.     'Catch ex As Exception
  20.     'MsgBox(ex.ToString)
  21.     'End Try
  22.  
Oct 20 '08 #1
Share this Question
Share on Google+
1 Reply


joedeene
100+
P: 583
Would this help maybe? If not, have you tried Google?

joedeene
Oct 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.