0

I am changing my application from Windows Forms to a Windows Service.

conn.GetSchema("Tables") is working differently in the two code bases.

In the new code base, I cannot get the Excel worksheets returned. conn.GetSchema("Tables").Rows.Count is 0. I have double checked all of the references and nothing is different. I can't find any other differences in my code that I can think would have this effect. Any ideas and what else I can look for?

Here is my full function (yeah, I know I should be using a For Each):

Private Function GetWorksheets(FileName As String) As String()

    Dim rv As List(Of String)
    Dim conn = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" & FileName & ";Extended Properties=Excel 8.0;")

    conn.Open()
    For x As Integer = 0 To conn.GetSchema("Tables").Rows.Count - 1
        rv.Add(conn.GetSchema("Tables").Rows(x)("TABLE_NAME"))
    Next x
    conn.Close()
    Return rv.ToArray

End Function

Thanks, Brad

Brad Mathews
  • 1,567
  • 2
  • 23
  • 45

1 Answers1

1

You need to include the path of web server (System.Web.HttpContext.Current.Server.MapPath), so try replacing the "conn" assignment with the following:

Dim conn = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" &  System.Web.HttpContext.Current.Server.MapPath(FileName) & ";Extended Properties=Excel 8.0;")
ron tornambe
  • 10,452
  • 7
  • 33
  • 60
  • I am not doing this from a web server, but a windows service. I am already using an absolute path. But, you led me to the solution! There is a bug in earlier code and I am passing the wrong path to the file. I would have expected an exception, but in fact the connection opens (conn.state is Open) even though it points to a non-existent file. – Brad Mathews Oct 17 '12 at 18:35
  • Brad, have a look at this SO answer: http://stackoverflow.com/questions/2714262/relative-path-issue-with-net-windows-service – ron tornambe Oct 17 '12 at 18:57