Open/ modify Excel through ADO.net

[G_Waddell]

Hi all,

I'm writing an application that imports and tidy up address data into a cleaned, deduped excel workbook ordered in rows with each column as an address field.

One of the issues I've ran into is that we sometimes get a workbook where the multiple address fields are held in a single cell with line breaks.

I've written code to extract all of this data out of the excel sheet in to a dataset with each sheet as a table and the address fields contained within.

I now want to create a new Excel workbook from this data with the address fields in Row and columns the problem I have is I can Open/create the Workbook but when I try to insert a new Sheet (Table) I get an error saying the Workbook is read only....

Private Sub MakeSimpleXL(ByRef DsTemp As DataSet)
        Dim dsTable As Data.DataTable
        Dim tblName As String
        Dim dsRow As Data.DataRow
        Dim sFile, xlConn As String
        Dim intItem, intRow, intTable As Integer
        Dim sQry, sFields, sValues As String 
        Dim mCommand As OdbcCommand = OdbcCommand
        Dim OConn as OdbcConnection

        sFile = Replace(xlDest, ".xl", "_src.xl")
        If File.Exists(sFile) Then File.Delete(sFile)
        xlConn = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & sFile & ";DefaultDir=c:\mypath;"
        OConn = new OdbcConnection(xlConn)
       OConn.open ' excel sheet is created....
        For Each dsTable In DsTemp.Tables
            'creation of new worksheet in workbook
            intTable = intTable + 1
            If dsTable.TableName Is Nothing Then
                tblName = "Page " & intTable
            ElseIf dsTable.TableName = "" Then
                tblName = "Page " & intTable
            Else
                tblName = dsTable.TableName
            End If
            sQry = "CREATE TABLE [" & tblName & "$]"
            mCommand.CommandText =sQry
            mCommand.CommandType = CommandType.Text
            mCommand.Connection = OConn
            'error occurs here
             mCommand.ExecuteNonQuery()

Here is the error:

System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC Excel Driver] Cannot modify the design of table '3 _ 10$'. It is in a read-only database. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteNonQuery() at AddressImport.DalBase.DynamicInsert(String SQL, String& sError) in C:\Documents and Settings\george\My Documents\Project work\PrintDocs\srccode\AddressImport\AddressImport\Dalbase.vb:line 107

It looks to me like it is trying to create the table (Sheet) but has the workbook opened as read only....

[G_Waddell]

Hi all,

I'm writing an application that imports and tidy up address data into a cleaned, deduped excel workbook ordered in rows with each column as an address field.

One of the issues I've ran into is that we sometimes get a workbook where the multiple address fields are held in a single cell with line breaks.

I've written code to extract all of this data out of the excel sheet in to a dataset with each sheet as a table and the address fields contained within.

I now want to create a new Excel workbook from this data with the address fields in Row and columns the problem I have is I can Open/create the Workbook but when I try to insert a new Sheet (Table) I get an error saying the Workbook is read only....

Private Sub MakeSimpleXL(ByRef DsTemp As DataSet)
        Dim dsTable As Data.DataTable
        Dim tblName As String
        Dim dsRow As Data.DataRow
        Dim sFile, xlConn As String
        Dim intItem, intRow, intTable As Integer
        Dim sQry, sFields, sValues As String 
        Dim mCommand As OdbcCommand = OdbcCommand
        Dim OConn as OdbcConnection

        sFile = Replace(xlDest, ".xl", "_src.xl")
        If File.Exists(sFile) Then File.Delete(sFile)
        xlConn = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" & sFile & ";DefaultDir=c:\mypath;"
        OConn = new OdbcConnection(xlConn)
       OConn.open ' excel sheet is created....
        For Each dsTable In DsTemp.Tables
            'creation of new worksheet in workbook
            intTable = intTable + 1
            If dsTable.TableName Is Nothing Then
                tblName = "Page " & intTable
            ElseIf dsTable.TableName = "" Then
                tblName = "Page " & intTable
            Else
                tblName = dsTable.TableName
            End If
            sQry = "CREATE TABLE [" & tblName & "$]"
            mCommand.CommandText =sQry
            mCommand.CommandType = CommandType.Text
            mCommand.Connection = OConn
            'error occurs here
             mCommand.ExecuteNonQuery()

Here is the error:

It looks to me like it is trying to create the table (Sheet) but has the workbook opened as read only....

Hi all,

Solved the issue I used an oleDB connections string which by default opens as read only, I have to sepecify ReadOnly=False.

I also encountered an error in my insert statement I now use:

CREATE TABLE [" & tblName & "] (Address1 char(255), Address2 varchar(255), Address3 char(255), Address4 char(255))"

Not

CREATE TABLE [" & tblName & "$] "

This will put in the sheet with the column names specified but I can not insert data into the sheet. When I open the Workbook in Excel, I see all the sheets with the column names but instead of Address1 I see 'Address1 instead!!!???

[Baldur Levesque]

stuck with ado.net for opening excel, the way is slowly. you can use a c# excel component, may useful to you named Spire.XLS.