Dim App As Excel.Application
Dim objBook As Excel._Workbook
Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim excelBook = objBook
App.Workbooks.Open(lblStatus.Text, 0, False, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, True, False, System.Reflection.Missing.Value, False)
Dim excelSheets As Excel.Sheets = excelBook.Sheets
Dim wSheet As Excel.Worksheet = excelSheets(1)
Dim cell1 As Excel.Range = wSheet.Range("B4:FZ4", Type.Missing)
Dim cell2 As Excel.Range = wSheet.Range("A4:A5", Type.Missing)
cell2.Value2 = "SampleText"
excelBook.Save()
App.Quit()
End Sub
Im getting Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. on Line 6 of the code above.
You made an objBook object and then you are trying to assign it to excelBook while objBook does not have anything in it. not similar. The App.WorkBooks.Open method returns a workbook which you are not using, can you please compare your code with the code
that I gave.
Dim strExcelConn
As
String =
"Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=C:\Book1.xls;Extended Properties='Excel 8.0;HDR=Yes'"
Dim connExcel
As
New OleDbConnection(strExcelConn)Dim cmdExcel
As
New OleDbCommand()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema
As DataTable
Dim ds
As
New DataSet
Dim da
As
New OleDbDataAdapterdtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
Nothing)
connExcel.Close()
Dim SheetName
As
String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
Thank you both for you replies last night, i am back on the case again this morning FYI my file upload script is as follows:
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
If Not fileUpEx.HasFile = Nothing Then
Dim filepath As String = fileUpEx.PostedFile.FileName
Dim pat As String = "\\(?:.+)\\(.+)\.(.+)"
Dim r As Regex = New Regex(pat)
'run
Dim m As Match = r.Match(filepath)
Dim file_ext As String = m.Groups(2).Captures(0).ToString()
Dim filename As String = m.Groups(1).Captures(0).ToString()
Dim file As String = filename & "." & file_ext
'save the file to the server
fileUpEx.PostedFile.SaveAs(Server.MapPath(".\eof_uploads\") & file)
lblStatus.Text = "File uploaded: " & file
End If
End Sub
So in answer to your question Bullpit llbstatus.text contains the file name (i changed it this morning it did contain the full path to the file last night).
Mudassarkhan, i tried your code but i am getting the following error: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." I have had a look into this and it appears this is a config setting i need to change, this could be a show stopper as i do not have access to the server to change the machines config settings, and if there are security implications in doing so it is possible they will not allow me to get this changed.
So so close now i am getting There is no row at position 1.
Dim FilePath
As String = lblStatus.TextDim fullpath
As String = Server.MapPath(".\eof_uploads\") & lblStatus.TextDim strExcelConn
As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" &
"Data Source=" & fullpath &
";Extended Properties='Excel 8.0;HDR=Yes'"
MsgBox(strExcelConn)
Dim connExcel
As New OleDbConnection(strExcelConn)Dim
cmdExcel As New OleDbCommand()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema
As DataTableDim ds
As New DataSetDim da
As New OleDbDataAdapterdtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
Nothing)
connExcel.Close()
Dim SheetName
As String = dtExcelSchema.Rows(0)("Order Summary").ToString()
stephen.adsh...
Member
20 Points
76 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 04:09 PM|LINK
Thanks Bullpit,
I have tinkered with the code and come up with:
Dim App As Excel.Application Dim objBook As Excel._Workbook Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click Dim excelBook = objBook App.Workbooks.Open(lblStatus.Text, 0, False, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, True, False, System.Reflection.Missing.Value, False) Dim excelSheets As Excel.Sheets = excelBook.Sheets Dim wSheet As Excel.Worksheet = excelSheets(1) Dim cell1 As Excel.Range = wSheet.Range("B4:FZ4", Type.Missing) Dim cell2 As Excel.Range = wSheet.Range("A4:A5", Type.Missing) cell2.Value2 = "SampleText" excelBook.Save() App.Quit() End SubIm getting Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. on Line 6 of the code above.
bullpit
All-Star
21838 Points
4822 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 04:24 PM|LINK
You made an objBook object and then you are trying to assign it to excelBook while objBook does not have anything in it. not similar. The App.WorkBooks.Open method returns a workbook which you are not using, can you please compare your code with the code that I gave.
What does lblStatus.Text have?
Max
Let Me Google That For You!
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 04:25 PM|LINK
A working example
import these namespace
Imports
System.DataImports
System.Data.OleDbAnd use this code
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Book1.xls;Extended Properties='Excel 8.0;HDR=Yes'" Dim connExcel As New OleDbConnection(strExcelConn)Dim cmdExcel As New OleDbCommand()cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As DataTable Dim ds As New DataSet Dim da As New OleDbDataAdapterdtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)connExcel.Close()
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()cmdExcel.CommandText =
"SELECT * From [" + SheetName + "A5:B5]"da.SelectCommand = cmdExcel
da.Fill(ds)
connExcel.Close()
Contact me
stephen.adsh...
Member
20 Points
76 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 27, 2009 09:07 AM|LINK
Hi,
Thank you both for you replies last night, i am back on the case again this morning FYI my file upload script is as follows:
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click If Not fileUpEx.HasFile = Nothing Then Dim filepath As String = fileUpEx.PostedFile.FileName Dim pat As String = "\\(?:.+)\\(.+)\.(.+)" Dim r As Regex = New Regex(pat) 'run Dim m As Match = r.Match(filepath) Dim file_ext As String = m.Groups(2).Captures(0).ToString() Dim filename As String = m.Groups(1).Captures(0).ToString() Dim file As String = filename & "." & file_ext 'save the file to the server fileUpEx.PostedFile.SaveAs(Server.MapPath(".\eof_uploads\") & file) lblStatus.Text = "File uploaded: " & file End If End Sub
So in answer to your question Bullpit llbstatus.text contains the file name (i changed it this morning it did contain the full path to the file last night).
bullpit
All-Star
21838 Points
4822 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 27, 2009 12:03 PM|LINK
You have to use this path to open the excel file:
Server.MapPath(".\eof_uploads\") & file
Max
Let Me Google That For You!
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 27, 2009 12:07 PM|LINK
No its a syntax error What is pasted works for me
Contact me
stephen.adsh...
Member
20 Points
76 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 27, 2009 12:54 PM|LINK
So so close now i am getting There is no row at position 1.
Dim FilePath As String = lblStatus.Text Dim fullpath As String = Server.MapPath(".\eof_uploads\") & lblStatus.Text Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fullpath & ";Extended Properties='Excel 8.0;HDR=Yes'"MsgBox(strExcelConn)
Dim connExcel As New OleDbConnection(strExcelConn)Dim cmdExcel As New OleDbCommand()cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As DataTable Dim ds As New DataSet Dim da As New OleDbDataAdapterdtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)connExcel.Close()
Dim SheetName As String = dtExcelSchema.Rows(0)("Order Summary").ToString()cmdExcel.CommandText =
"SELECT * From [" + SheetName + "A1:A3]"da.SelectCommand = cmdExcel
da.Fill(ds)
connExcel.Close()
My Excel document has the following:
1 2 3
A TEST TEST TEST
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 27, 2009 01:30 PM|LINK
Yes bcoz 1st row is header So either set header No
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fullpath & ";Extended Properties='Excel 8.0;HDR=No'"
or add a new row
Contact me
stephen.adsh...
Member
20 Points
76 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 27, 2009 03:56 PM|LINK
hmm tried that same error. Would send you my Test Excel doc but attachments are not allowed on these forums.
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 27, 2009 05:04 PM|LINK
Dim SheetName As String = dtExcelSchema.Rows(0)("Order Summary").ToString()
Above line is wrong
hey use TABLE_NAME you cannot change like below
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
Contact me