to transfer data to each sheet of a excel workbook [modified]

[sanyexian]

Hi,everyone.I am hoping that the text that I input in the textbox can be captured in the excel each sheet ,column 3, rows 2-20 for example. Code like under,I think there are many errors in it,please tell me how to write it,please. Thanks!

Imports Office = Microsoft.Office.Core
Imports Microsoft.Office.Interop.Excel

Private Sub OperateExcel()

    Dim Xls As Object
    Dim Xlsbook As Excel.Workbook
    Dim Xlssheet As Excel.Worksheet
    Dim Xlsrange As Excel.Range

    Xls = CreateObject("excel.application")
    Xlsbook = Xls.Workbooks.open("D:\\text.xls")

    Xls.Visible = True

    Xlssheet = Xlsbook.Worksheets("sheet1")

    For Each Xlssheet In Xls.Xlsbook
        Xlsrange = Xlssheet.Range("H3")
        Xlsrange.Value = txtProduct.Text
        Xlsrange = Xlssheet.Range("H4")
        Xlsrange.Value = txtName.Text
        Xlsrange = Xlssheet.Range("H5")
        Xlsrange.Value = txtNum.Text
    Next
    Xlsbook.Close()
    Xls.Quit()

    releaseObject(Xls)
    releaseObject(Xlsbook)
    releaseObject(Xlssheet)

End Sub

modified on Friday, May 7, 2010 1:12 AM

[Johan Hakkesteegt]

Where to start... I am not exactly sure what you are trying to achieve, and I take it you assembled this code from examples, or copied it as is. So let's start from the beginning. If you suspect that this code has errors, the first thing you should do is put all the code inside the sub into a try catch clause:

Private Sub OperateExcel()
Try
'the rest of the code here'
Catch ex as Exception
MsgBox(ex.tostring)
End Try
End Sub

Then at least you'll find out if this code works at all. After that comes the important part, comparing what this code does to what you want it to do. A simple way to start is to write comments into the code. One comment for each step, and then fill in the code bits doing each step.

My advice is free, and you may get what you paid for.

[Sonhospa]

Hi sanyexian, I changed your code a little bit so that it works. Still you might encounter many traps, since communication with Excel (i.e. COM / Office) can be very tricky sometimes. Also don't forget the small things, like making your assembly COM visible (project properties). You'll find tons of material about the issue here and in the web - and a lot is worth reading. Good luck ;)

Sub OperateExcel()
    ' Compile with Option Strict Off to allow late binding.
    Dim xlsApp As Object
    Dim xlsBook As Object
    Dim xlsSheet As Object

    xlsApp = CreateObject("Excel.Application")
    ' Late bind an instance of an Excel workbook.
    xlsBook = xlsApp.Workbooks.Add
    ' Late bind an instance of an Excel worksheet.
    xlsSheet = xlsBook.Worksheets(1)
    xlsSheet.Activate()
    ' Show the application.
    xlsSheet.Application.Visible = True

    For Each xlsSheet In xlsBook.worksheets
        ' put your text into the cells in a simpler way
        xlsSheet.Range("H3").Value = "txtProduct"
        xlsSheet.Range("H4").Value = "txtName"
        xlsSheet.Range("H5").Value = "txtNum"
        ' A more useful way since you can iterate through rows/colums
        ' Here: Place some text in the second row of the sheet.
        xlsSheet.Cells(2, 8) = "This is column H row 2"
    Next
    xlsBook.SaveAs("D:\\text.xls")
    xlsBook.Close()
    xlsApp.Quit()

    xlsSheet = Nothing
    xlsBook = Nothing
    xlsApp = Nothing
End Sub

[Johan Hakkesteegt]

How did that saying go again, about giving a man a fish...? ;)

My advice is free, and you may get what you paid for.

[Sonhospa]

You're surely right on one side. On the other side I've experienced so many other traps communicating with Office (and still do, as you know from my own thread ;)), that he will have to dig deeper for himself anyway. Probaly he even gets more motivated by having an easier start :thumbsup:

[sanyexian]

Hi,Michael.I apologize for the late reply.Thanks for your suggestion.Now I could open a Excel file,but there is an error stoped me going on. xlsSheet = xlsBook.Worksheets(1) I watched "xlsSheet",it display that its value was "nothing" and VS told me " HRESULT:0x80020003 (DISP_E_MEMBERNOTFOUND))" I don't know why it happened...the file had been opened ,but why it could not get its worksheet? Plzzz help me ,thank you!

modified on Saturday, May 8, 2010 9:34 PM

[sanyexian]

Hi,Johan,thanks for your suggestion.I apologize for the late reply...I used try catch clause in my code and then it told me an exception like this: system.runtime.interop services.COMException(0x80020003):member not found. it comes from:xlssheet = xlsworkbook.worksheet(1) Could you give me suggestions to solve it?Thank you :wtf:

modified on Saturday, May 8, 2010 9:35 PM

[Sonhospa]

Hi Sanyexian, you can try xlsSheet = xlsBook.Worksheets.Add to add a new worksheet instead of referring the first available sheet with "1" (as suggested: play with IntelliSense!). The code is very basic anyway, you will need to catch exceptions (Try... Catch) and much more. You might also want to download some samples from the web and learn from them - as I wrote COM-Interaction can be tricky. Good luck! :thumbsup: