• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

VB .NET and excel

Hi Guys

i had a code for vb .net 2008 to edit a existing excel file but i have lost it =[ and cant find it again. i need a code to edit a excel file lets say c:\test.xls and append to it. any ideas?
so textbox1 would go to row 2 column A and textbox2 to row 2 column B etc
0
EfrenM
Asked:
EfrenM
  • 6
  • 4
1 Solution
 
wrmichaelCommented:
many different ways to do this.

you could use a ODBC driver to do it or create an instance of the excel.application.

Do you remember how you did it in the past?
0
 
wrmichaelCommented:
0
 
wrmichaelCommented:
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
EfrenMAuthor Commented:
sorry dont remember i think it hasd usedrange but not to sure, i took at look at those two links doesnt look like it :(
0
 
EfrenMAuthor Commented:
Well i found this code but it is giving a error at the line below, attached is also the picture of the error any ideas?

ObjWS.Cells(iNextRow, 1).value = "new row data, second column"
Dim ObjExcel As New Excel.Application()
        ObjExcel.Visible = False

        ObjExcel.Workbooks.Open("c:\test.xlsx")
        Dim ObjWS As Excel.Worksheet = ObjExcel.Worksheets("Sheet1")


        ' MsgBox(ObjWS.Cells(1, ObjWS.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row).Value.ToString)



        Dim iNextRow As Integer = ObjWS.UsedRange.End(Excel.XlDirection.xlDown).Row + 1

        ObjWS.Cells(iNextRow, 1).value = "new row data, second column"
        


        Dim objW As Excel.Workbook = ObjExcel.ActiveWorkbook
        objW.Save()

        ObjExcel.Quit()

Open in new window

error1.png
0
 
wrmichaelCommented:
You can not access the row because it does not exists.

maybe a row.add then reference it that way.

0
 
wrmichaelCommented:
'add row

myWorkSheet.Rows("1:1").Select()

myWorkSheet.Rows.Insert(shift:=Excel.XlDirection.xlDown)

'add values

http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/e0c9ab1e-cc04-413f-a583-ce787fb0211c
0
 
EfrenMAuthor Commented:
ok got it working YaY!!1 finally all day lol, this time i make a backup of the code, i have attached the working code .

so the word yummy will go into the first column and the word wee will go into the second.. had to change from used range to special cells
im ObjExcel As New Excel.Application()
        ObjExcel.Visible = False

        ObjExcel.Workbooks.Open("c:\test.xlsx")
        Dim ObjWS As Excel.Worksheet = ObjExcel.Worksheet("Sheet1")

Dim iNextRow As Integer = ObjWS.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row + 1

        ObjWS.Cells(iNextRow, 1).value = "Yummy"
        ObjWS.Cells(iNextRow, 2).value = "WeEE"
        


        Dim objW As Excel.Workbook = ObjExcel.ActiveWorkbook
        objW.Save()

        ObjExcel.Quit()

Open in new window

0
 
EfrenMAuthor Commented:
found the answer will looking at your tips
0
 
wrmichaelCommented:
Great!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now