purecomedy
05-09-05, 02:18 PM
I have a very small program that I have created that looks every couple of minutes at an Excel file and imports the latest results if the file has been saved since the last import.
The Excel file is on a network drive and my company uses Novell to put permissions on different paths.
If I have the file on my local drive or on a network folder where I have full access (write, modify, create in addition to file scan and read) the 3rd line of code works fine. If I have file scan and read access my program generates an error. I can double click on the spreadsheet and Excel will open the file in read-only mode no problem...so why can't I do a select query on it with just read access?
I used this approach using the ExcelReader to avoid some of the hassles of the alternative I was using. Before I was opening up a new Excel session (Excel_App = New Excel.Application), and grabbing the data with an offset function. The downside of this approach is that Microsoft seems to get confused about which Excel session to use (the one I opened up with the program or one that I have opened myself to do other work). All of a sudden the Excel application I opened blinks with data that should be going to the other Excel session. I determined this was bad design practice so wanted to use this ExcelReader approach.
This is what my code looks like:
Sub1()
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Sheet1$AA1:AB39]", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
......
End Sub
Within the class code I defined m_sConn1 as follows
Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=L:\dir1\spreadsheet.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
The Excel file is on a network drive and my company uses Novell to put permissions on different paths.
If I have the file on my local drive or on a network folder where I have full access (write, modify, create in addition to file scan and read) the 3rd line of code works fine. If I have file scan and read access my program generates an error. I can double click on the spreadsheet and Excel will open the file in read-only mode no problem...so why can't I do a select query on it with just read access?
I used this approach using the ExcelReader to avoid some of the hassles of the alternative I was using. Before I was opening up a new Excel session (Excel_App = New Excel.Application), and grabbing the data with an offset function. The downside of this approach is that Microsoft seems to get confused about which Excel session to use (the one I opened up with the program or one that I have opened myself to do other work). All of a sudden the Excel application I opened blinks with data that should be going to the other Excel session. I determined this was bad design practice so wanted to use this ExcelReader approach.
This is what my code looks like:
Sub1()
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Sheet1$AA1:AB39]", conn1)
Dim rdr As OleDbDataReader = cmd1.ExecuteReader
......
End Sub
Within the class code I defined m_sConn1 as follows
Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=L:\dir1\spreadsheet.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""