Retrieving Sheet Names from Excel file

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  July 31, 2005 at 8:03 pm

  #89321

  I need to create generic procedure to import data from XLS files coming from customers.

  I'm using query:

  SELECT *

  FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

    'Data Source="\\MyServer\MyDirecrory\X_Extract.xls"; User ID=Admin;Password=;Extended properties=Excel 8.0')...[DataSheet$]

  But I'm not really sure that in every file coming from customers the sheet with data will be always named "DataSheet". Procedure suppose to find names of all sheets and process them one by one.

  But I have no idea how to get list of sheets in XLS file to SQL Server.

  Can anybody help me with that?

   

  Thanks.

  _____________
  Code for TallyGenerator

• Murthy-152277

  Right there with Babe

  Points: 720

  More actions

  August 2, 2005 at 4:51 am

  #578780

  Hi Sergiy,

  The following method will return all the Sheet Names as a Collection.U need to pass the Excel File Name as param to this method.

   

  Public Function GetSheetNames(ByVal strFileName As String) As Collection

  Dim SheetNames As New Collection

  Dim dt As New DataTable

  Dim objConn As New System.Data.OleDb.OleDbConnection

  Try

  Dim strConn As String = "Provider=Microsoft.Jet.OleDb.4.0;" _

  & "data source= " & strFileName & " ;" _

  & "Extended Properties=Excel 8.0;"

  objConn =

  New System.Data.OleDb.OleDbConnection(strConn)

  objConn.Open()

  dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,

  New Object() {Nothing, Nothing, Nothing, "TABLE"})

  For lintcount As Integer = 0 To dt.Rows.Count - 1

  SheetNames.Add(dt.Rows(lintcount).Item(2))

  Next

  Return SheetNames

  Catch ex As Exception

  MsgBox(ex.ToString)

  End Try

  End Function

  Thanks,

  Murthy.