Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Oct 21, 2011 01:02 PM by Richard Williams
Feb 26, 2009 04:09 PM|LINK
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"
Im getting Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. on Line 6 of the code above.
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?
Feb 26, 2009 04:25 PM|LINK
A working example
import these namespace
And use this code
cmdExcel.Connection = connExcel
da.SelectCommand = cmdExcel
Feb 27, 2009 09:07 AM|LINK
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)
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
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.
Really appreciate both your responses so far.
Feb 27, 2009 12:03 PM|LINK
So in answer to your question Bullpit llbstatus.text contains the file name
You have to use this path to open the excel file:
Server.MapPath(".\eof_uploads\") & file
Feb 27, 2009 12:07 PM|LINK
No its a syntax error What is pasted works for me
Feb 27, 2009 12:54 PM|LINK
So so close now i am getting There is no row at position 1.
My Excel document has the following:
1 2 3
A TEST TEST TEST
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
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.
Feb 27, 2009 05:04 PM|LINK
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()