5 replies to this topic

[lordwittie]

hi,

I want my program to write text to an excel file.
I want if that it always adds the new text. I have 4 textboxes, each textbox should be written in another cell, next to each other. If I add new information, these 4 things should appear beneath the 4 others.
I think it's not that hard, but I can't find how to do it...

I tried this for checking if it could be usefull:

        Dim objUser, strExcelPath, objExcel, objSheet, objGroup

        strExcelPath = "c:\UserGroup.xlsx"

        objExcel = CreateObject("Excel.Application")

        objExcel.Workbooks.Add()

        objSheet = objExcel.ActiveWorkbook.Worksheets(1)

        objSheet.Name = "User Groups"

        objSheet.Cells(1, 2).Value = "User Common Name"

        objSheet.Cells(2, 2).Value = "sAMAccountName"

        objSheet.Cells(3, 2).Value = "Display Name"

        objSheet.Cells(4, 2).Value = "Distinguished Name"

        objSheet.Cells(5, 2).Value = "Groups"

        objExcel.ActiveWorkbook.SaveAs(strExcelPath)

        objExcel.ActiveWorkbook.Close()

        objExcel.Application.Quit()

        objUser = Nothing

        objGroup = Nothing

        objSheet = Nothing

        objExcel = Nothing

but it always creates a new excel file, so it does not add the info

I am thinking of reading the excel first, then add the new info, and writing it again, but whatever I try, it doesn't work :( any help = greatly appreciated!

LW

[Ray Tawil]

it's only normal that this happens, you can't just copy paste code & assume it will magically do what you need, if you read a little carefully

objExcel.Workbooks.Add()

this will create a new workbook each time.

instead you should open the existing workbook & append on it

objExcel.Workbooks.Open("c:\...\your_file.xls")

then you should get the last data that is already filled then start adding new records

[lordwittie]

ok, now I did it with this code:

        Dim objUser, strExcelPath, objExcel, objSheet, objGroup, objworkbook, objworksheet
        objExcel = CreateObject("Excel.Application")
        objExcel.Visible = True
        objExcel.DisplayAlerts = False
        objworkbook = objExcel.Workbooks.Open("C:\UserGroup.xlsx")
        objworksheet = objworkbook.Worksheets(1)
        Dim i As Integer
        i = 1
        Do
            If objworksheet.cells(i, 1).value = Nothing Then
                objworksheet.cells(i, 1).value = ComboBox2.Text
                objworksheet.cells(i, 2).value = ComboBox1.Text
                objworksheet.cells(i, 3).value = TextBox3.Text
                objworksheet.cells(i, 4).value = TextBox1.Text
                objworksheet.cells(i, 5).value = TextBox2.Text
                GoTo save
            Else
                i += 1
            End If
        Loop
save:
        objworkbook.Save()
        objExcel.Quit()
        objworkbook = Nothing
        objworksheet = Nothing
        objExcel = Nothing

only problems now:
- the excel opens on the screen everytime you click the button
- the process EXCEL.exe doesn't stop after closing the program, so it takes memory for nothing; and many EXCEL.exe are running after using it for a while :(

thanks for your help!

[Ray Tawil]

to solve the excel that shows just change this

objExcel.Visible = True

to
objExcel.Visible = false

you also need to close the object that you initialized so it closes :)

[lordwittie]

thanks!

but I can't get teh closing to work :( I tried
objExcel.close()
and
objExcel.Quit() and objExcel = Nothing are already there, but it doesn't work :(
now I have googled, and tried this:

        objworkbook.Save()
        objExcel.Workbooks.Close()
        objExcel.Quit()

but still doesn't work :(

I found some more information on these sites:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q317109
http://bytes.com/topic/net/answers/475347-cant-close-excel-vb-net

and tried much, but still it doesn't close :(

[lordwittie]

I finally tried this:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=811

I think there much be another way, but it's working...