Reading Excel with VB2005

• • Reply

  14 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago

  I'm starting to convert some of my current VB6 projects to .NET and wanted to know if anyone had an example or a good web link to how VB2005 connects to Excel files.  In VB6 I used a objects.  Is there a better way in .net?  My VB6 code is below.
  
  Private Sub ReadXLSFileFormat(FileName As String)
     
      Dim ObjInputXLS As Object
      Dim ObjInputWorkBook As Object
      Dim ObjInputSheet As Object
      Dim x As Integer
     
      Set ObjInputXLS = CreateObject("Excel.Application")
      Set ObjInputWorkBook = ObjInputXLS.Workbooks.Open(FileName)
      Set ObjInputSheet = ObjInputWorkBook.Worksheets(1)
  
      'Some code here
  
      ObjInputXLS.ActiveWorkbook.Close True, FileName
      Set ObjInputSheet = Nothing
      Set ObjInputWorkBook = Nothing
      Set ObjInputXLS = Nothing
  
  End Sub
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by CAMURPHY

  Craig Murphy

  Scotland, UK, United Kingdom

  Joined 15 years ago

  I am strapped for time to convert it myself, but if you are up for a little C# to Visual Basic.net. you will find some C# that might help you over here:

  http://www.developerfusion.co.uk/forums/thread/133997/

   

  HTH

   

  Report abuse
• • Reply

  14 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago

  Thanks for the reply, though I could not convert your code without errors.  I did get this code below to run, but it has a very strange problem.  It will only work if I do not save the project.  Once I save the project, I get an error:
  
  "Request for the permission of type 'System.Data.OleDb.OleDbPermission....' failed"
  
  If I copy and paste the entire code into a new project, it works again!  This is the most bizarre bug I have seen in awhile. 
  
  // EDIT
  Found a setting in .NET Framework where you can increase trust level of 'Local Intranet'.  This needed to be done since the excel file resided on a networked drive.
  // End EDIT
  
  
  Dim sqlConnection As New System.Data.OleDb.OleDbConnection
  Dim sqlDataAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [JOBS$]", sqlConnection)
  Dim sqlDataSet As DataSet = New DataSet
  Dim sqlDataRow As DataRow
  
  sqlConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Brian\WIP\BrianWIPLIST.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
  sqlDataAdapter.Fill(sqlDataSet)
  
  For Each sqlDataRow In sqlDataSet.Tables(0).Rows
      If IsNumeric(sqlDataRow("JOB #")) Then MsgBox(sqlDataRow("JOB #"))
  Next
  
  sqlConnection.Close()
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse