How to Read Excel File AT VB.NET RSS

• • Reply
  

  baokychen

  Member

  1 Points

  15 Posts

  How to Read Excel File AT VB.NET

  Aug 06, 2008 03:23 AM|LINK

  Hi anyone know how do i read excel file at VB.net & display the result in a label or something..

   

  e.g i know cell A2 is a string of text - e.g Hello this is the P/L for the month 

   

  i want grab that and put in a label etc.

  
  Thanks , it would be good if can refer me to url on using Microsoft Excel Library Object & its coding to connect, close connection , grab data from cell and input back the data.

   

  Regards,
  Bao
  
  
   

  read excel file

  • Moved by SGWellens on Aug 06, 2008 03:33 AM
• • Reply
  

  grahamlower

  Participant

  1370 Points

  264 Posts

  Re: How to Read Excel File AT VB.NET

  Aug 06, 2008 03:57 AM|LINK

  With the proper connection string (http://www.connectionstrings.com/?carrier=excel) you can query it like any datasource.  Using the Excel Interop is, frankly, sometimes a pain.

  Failure is always an option. Avoid situations where it is the only option.
• • Reply
  

  baokychen

  Member

  1 Points

  15 Posts

  Re: How to Read Excel File AT VB.NET

  Aug 06, 2008 08:29 AM|LINK

  if by setting a connection string to excel.

   

  how do i query it like any data source?  i never try before querying excel, is there any tutorial or sample

   

  Thanks,

  Baoky 

  excel query

• • Reply
  

  BlueJam

  Member

  441 Points

  84 Posts

  Re: How to Read Excel File AT VB.NET

  Aug 06, 2008 09:00 AM|LINK

  Heres some code I use to connect to an excel data range  :-

  Using m_connexcel As OleDbConnection = New OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                    "Data Source={0};" & _
                                                                    "Extended Properties=""Excel 8.0;HDR=NO""", SPREADSHEET_NAME))
                          m_connexcel.Open()
                          Using cmd As OleDbCommand = New OleDbCommand(String.Format("SELECT  * FROM [{0}]", DATARANGE), m_connexcel)
                              Using oRDR As OleDbDataReader = cmd.ExecuteReader
                                  While (oRDR.Read)
                                     If Not oRDR.IsDBNull(oRDR.GetOrdinal("F1")) Then
                                        MsgBox(oRDR.GetString(oRDR.GetOrdinal("F1")))
                                     End If
                                 End While
                              End Using
                          End Using
                          m_connexcel.Close()
  End Using

  Set 'HDR' in the connection string to 'YES' if you want to use the first row as the field names, otherwise the fields will be called
  F1, F2, F3 etc...

   

• • Reply
  

  baokychen

  Member

  1 Points

  15 Posts

  Re: How to Read Excel File AT VB.NET

  Aug 08, 2008 02:53 AM|LINK

  How do i query it after i connect to the excel file.

   

  e.g , i want query from A1 to A20 or A1 to A10+ B1 to B10

   

   

  Thanks

  Rgds,

  Bao 

  ole db excel

  • Edited by baokychen on Aug 08, 2008 06:19 AM
• • Reply
  

  BlueJam

  Member

  441 Points

  84 Posts

  Re: How to Read Excel File AT VB.NET

  Aug 08, 2008 09:36 AM|LINK

  You basically wrap the data you want to query in a named data range (Insert Menu -> Name -> Define) and then you can select the info using a  SQL style select statement as above.
• • Reply
  

  khakz

  Member

  2 Points

  1 Post

  Re: How to Read Excel File AT VB.NET

  Feb 05, 2009 05:42 AM|LINK

  i used the codes in this http://vb.net-informations.com/excel-2007/vb.net_excel_oledb.htm a message box appears that says system.data.oledb.oledbexception: cannot update. database or object is read-only. what seems to be the problem?

• • Reply
  

  BlueJam

  Member

  441 Points

  84 Posts

  Re: How to Read Excel File AT VB.NET

  Feb 09, 2009 03:12 PM|LINK

  Did you have the excel file open when your program was running?

• • Reply
  

  BarbaMarioli...

  Member

  208 Points

  136 Posts

  Re: How to Read Excel File AT VB.NET

  Jul 29, 2009 01:34 PM|LINK

  Hi,
  
  Best way to deal with Excel in VB is by using 3rd party component. It's much faster and easier way then Excel Automation. Try using GemBox spreadsheet component for exporting Excel. Component is free for commercial use but with limit of 150 rows.
  
  Here you can see a list of reason why GemBox.Spreadsheet is much better tool to work with Excel then Excel Automation.
  
  Filip
  GemBox.Spreadsheet - easiest way to create, read and write Excel files in .NET
  

• • Reply
  

  Kusno

  Member

  13 Points

  63 Posts

  Re: How to Read Excel File AT VB.NET

  Aug 05, 2009 06:56 AM|LINK

  Hi, How do I read an Excel file that is located in a client PC?

  
  

  Thanks
  

  Never Never Never Give Up !!