[2005] VB.net write to Excel?

[Mark Douglas]

I'm very new to VB.net so please excuse me if I'm posting this in the wrong location. I'm hoping someone can get me started on a very very basic example here to get me started.

What I want to do is create a form that a user can fill in two simple combo boxes with some simple information hit OK and write that information to an excel file. I was able to do this in VBA but totally lost on what VB.net code looks like or is structured compared to VBA. So if anyone could post a very basic simple form with one or two combo boxes that write to a simple excel file would be great!!

Thanks for any help, I really appreciate it!!

Mark

[Mecharius]

Hi, this is actually quite easy to do... I think the code below should work, although I haven't tested it. You need to include a reference to Microsoft.Office.Core in the project as well.

EDIT: To be more precise, you have to right click References in the solution explorer, then 'add reference.' Once you have done that click the 'COM' tab and scroll down until you see 'Microsoft Excel 11.0 Object Library' add this by double clicking it, and for good measure, scroll down and add 'Microsoft Office 11.0 Object Library' as well.

'set up Excel variables
        Dim xcApp As New Excel.Application
        Dim xcWB As Excel.Workbook = xcApp.Workbooks.Open("c:/myExcelFile.xls")
        Dim xcWS As Excel.Worksheet = CType(xcWB.Worksheets(1), Excel.Worksheet)
        Dim xcRange As Excel.Range
 
        'set up other variables
        Dim strRange As String  'the cell that is currently being looked at
        Dim intRow As Integer = 1   'the row currently being looked at
        Dim sValue as String = "Write me to file!"
        
        'Change the value at strRange
        strRange = "A" & CStr(intRow)
        xcRange = xcWS.Range(strRange)
        xcRange.Value = sValue
        xcWB.Save()
 
        'clean up
        xcWB.Close()
        xcApp.Quit()
        xcApp = Nothing
        xcWB = Nothing
        xcWS = Nothing
        xcRange = Nothing

Please note that in this example "c:/myExcelFile.xls" must already exist.

To modify another row, simply change the range (e.g. xcRange = "B1"). Note that this could be done with a for loop if you wanted to change a whole lot of values.

I've been trying to find the tutorial where I originally learnt how to use excel in vb but I can't seem to locate it.

[Mark Douglas]

Ok that example seems pretty simple and some what similar to what I've done in VBA. I tried running the code and I'm getting some errors saying.

1. Type 'Excel.Application' is not defined.
2. Type 'Excel.Workbook' is not defined.

I think this has to do with adding the reference to Microsoft Excel 11.0. So what I did was right click in the solutions center, then hit ADD reference. I went to COM and found the two references you said. I CTRL selected each of the two to highlight them, and then hit OK? Then went back into the Add resource and went to Recent and they were listed there are recent?

Is this the correct way to do this? Note: I should of stated this before, I'm running the Microsoft Visual Basic 2005 Express Edition. Hopefully this is still possible.

Thank you very much for your help!!

[RobDog888]

Here is one of my code examples. Its a bit more complete.

What version of vb.net are you runnng?

http://www.vbforums.com/showpost.php...07&postcount=5

[Mark Douglas]

I'm running the Microsoft Visual Basic 2005 Express Edition. (The Free one) Is this what you're asking? Or somewhere else to find the version of Vb.net?

[RobDog888]

Nope, thats what I wanted to know.
As long as you have the references added then the code examples should be good for you.

[Mecharius]

My example (yes I know its basic RobDog888) was for VB.NET 2003. I don't know if it will transfer across... best to use RobDog's!

[Mark Douglas]

Cool these examples got me going. Now in my form I'm writing to my excel file. Well when I keep running the program it keeps writing over the same line in the excel file.

In one of my VBA apps I use this to count the used rows. Then add one to start a new line of information. Is there a similar function for vb.net?

The.....mycount = wksht.UsedRange.Rows.Count.......I think is where vb.net doesn't understand it?

VB code
 
Dim mycount As Long
Dim CurRow As Long
 
mycount = wksht.UsedRange.Rows.Count
CurRow = mycount + 1
 
                MySht.Cells(CurRow, 1) = MyJobNumbernName
                MySht.Cells(CurRow, 2) = MyJobPM

Thanks for any help!!

[RobDog888]

Used rows could be giving the wrong cell address if you have some bogus data somewhere down the sheet.

There is another function for this too that is a bit more flexiable.

wksht.Cells.SpecialCells(xlCellTypeLastCell).Row

[Mark Douglas]

Right on!!..Im gonna give this a try!!

Thanks for the help!!

[RobDog888]

NP, also check out it other constant arguments that make it better to use. You can get the last row/column with a formula in it or several other types.

[Mark Douglas]

hmm...Ok Im getting the

Error 2 Name 'xlCellTypeLastCell' is not declared.

Here is my code so far maybe you could identify my problem?

My Code
 
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
        xlApp.Visible = True
        xlApp.UserControl = True
        Dim oldCI As System.Globalization.CultureInfo = _
            System.Threading.Thread.CurrentThread.CurrentCulture
        System.Threading.Thread.CurrentThread.CurrentCulture = _
            New System.Globalization.CultureInfo("en-US")
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
 
        xlBook = xlApp.Workbooks.Open("c:\JobInfo.xls")
 
        Dim xlSheet As New Microsoft.Office.Interop.Excel.Worksheet
 
        Dim MyName As String
        Dim MyDate As String
        Dim MyType As String
        Dim MyJobNumber As String
        Dim MyJobName As String
        Dim MyScope As String
        Dim MyClient As String
        Dim MyPrincipal As String
 
        MyName = CStr(ListBox1.Text)
        MyDate = CStr(DateTimePicker1.Text)
        MyType = CStr(ComboBox4.Text)
        MyJobNumber = CStr(ComboBox2.Text)
        MyJobName = CStr(TextBox1.Text)
        MyScope = CStr(ComboBox1.Text)
        MyClient = CStr(ComboBox5.Text)
        MyPrincipal = CStr(ComboBox6.Text)
 
        xlSheet = xlBook.Worksheets(1)
 
        'xlSheet.Cells.Item(1, 1) = MyName
        xlSheet.Cells.Item(1, 2) = DateTimePicker1
        xlSheet.Cells.Item(1, 3) = MyType
        xlSheet.Cells.Item(1, 4) = MyJobNumber
        xlSheet.Cells.Item(1, 5) = MyJobName
        xlSheet.Cells.Item(1, 6) = MyScope
        xlSheet.Cells.Item(1, 7) = MyClient
        xlSheet.Cells.Item(1, 8) = MyPrincipal
 
 
        xlBook.Save()
        xlBook.Close()

So here in this section.

'xlSheet.Cells.Item(1, 1) = MyName
xlSheet.Cells.Item(1, 2) = DateTimePicker1
xlSheet.Cells.Item(1, 3) = MyType
xlSheet.Cells.Item(1, 4) = MyJobNumber
xlSheet.Cells.Item(1, 5) = MyJobName
xlSheet.Cells.Item(1, 6) = MyScope
xlSheet.Cells.Item(1, 7) = MyClient
xlSheet.Cells.Item(1, 8) = MyPrincipal

I need to somehow replace the (1, 1) = with something like the (Lastrow, 1). So I can keep running my code and appending to new lines in the code.

So the code you provided I just don't know where it needs to go relative to my code. Sorry to keep bugging, this is all just a lot of reference type stuff and declared variables which I'm getting lost in.

Thanks for any help!!

[Hack]

I've edited your thread title to include the version that you specified in your message body. This helps people that want to help you know what sort of coding advice to provide.

Please use the radio buttons provided to specify what version you are using when creating new threads in the future.

Thanks.

[RobDog888]

Did you add a reference to MS Excel?

[Mark Douglas]

Sorry about the version. I'll do that next time!!

As far as the reference. I'm pretty sure I have the reference to MS Excel set because it writes to an excel file correctly. Just keeps writing over the same line over and over again?

[RobDog888]

I dont see where your referencing the last row to write to?

.Cells.SpecialCells(xlCellTypeLastCell).Row

[RobDog888]

Try referencing the entire class structure for the constant.

.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row

[Mark Douglas]

Ok, so try?

'xlSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row = Myname

?

Or replace my code with

.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row = Myname ?

I don't really know where to place this portion?

Sorry, I'm still really new to all of this..

Mark

[Mark Douglas]

All right!! I've gotten it to find the last row used, set a counter and increment it by one to find the next available line to write to.

Now my final question I'm hoping someone can help me with and I'll be all done.

In the last row of information I have a job number stored in the first column. What I want to do is increment the number in the first column by one? Is it possible to get the information contained in a designated cell. Such as cell A6? I know how to string together the row, and "A" but not sure how to pull this from the excel file and store it as a variable?

Thanks for any help!!

[RobDog888]

This is a way to reference cell A + whatever your last row is.

MessageBox.Show(wksht.Cells(1,wksht.Cells.SpecialCells(xlCellTypeLastCell).Row).Value.ToString)

[Mark Douglas]

Is there some special way to get the (xlCellTypeLastCell) to work? Ive tried all the examples before that had this string of code and they all say "(xlCellTypeLastCell)" not declared? This time its doing it again? I've got excel object 11.0 referenced? And everything else seems to be declared all right but this?

Thanks again!!

[RobDog888]

Sorry, forgot to fully qualify it.

MessageBox.Show(wksht.Cells(1,wksht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row).Value.ToString)

[Mark Douglas]

Thanks Rob for the help, but still not getting my cell contents?

When I add the following code I get the return value of "System._ComObject" in the message box?

Here is the code I'm at, maybe this will help?

Thanks for helping!!

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
 
        Dim Myexcel2 As Excel.Application
        Myexcel2 = CType(CreateObject("Excel.Application"), Excel.Application)
 
            Myexcel2.Workbooks.Open("C:\JobInformationResidential.xls")
 
        Myexcel2.Visible = True
 
        Dim mycount As Long
 
            Dim CurRow As Long
 
            Dim MyExcel As Excel.Application
            Dim MySht As Excel.Worksheet
            MyExcel = CType(GetObject(, "Excel.Application"), Excel.Application)
            MySht = CType(MyExcel.ActiveSheet, Excel.Worksheet)
 
            Dim wksht As Excel.Worksheet
            wksht = CType(Myexcel2.ActiveSheet, Excel.Worksheet)
 
'Gets last row
        mycount = wksht.UsedRange.Rows.Count
        CurRow = mycount + 1
 
        MessageBox.Show(wksht.Cells(1, wksht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row).ToString)
 
 
        Dim MyJobNumber As String
        Dim MyPrincipal As String
 
        MyJobNumber = CStr(ListBox1.Text)
        MyPrincipal = CStr(ComboBox7.Text)
        
        wksht.Cells(CurRow, 1) = MyJobNumber
        wksht.Cells(CurRow, 2) = MyPrincipal
        
        Myexcel2.ActiveWorkbook.Close(True)
 
        End
    End Sub

[RobDog888]

Thats because you forgot to add the .Value.ToString part like I have in my posted code in #22.

Why are you using 2 Excel.Applications? It looks like your using the same active sheet in two objects. The last cell will give you the next cell after the last used one.

[Mark Douglas]

Ok, fixed up the two excel applications. Not sure what I was thinking there? This makes more sense.

As far as the string of code. When I add in the line of code you posted I get an error shown..

MessageBox.Show(wksht.Cells(1, wksht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row).Value.ToString) under the lines underlined and the error is "Option Strict On disallows late binding"? Any idea what this means? But once I chop out the Value part the error message goes away, but of course the code doesn't do anything?

I know I'm doing something wrong?

[RobDog888]

Thats because of using "Option Strict On" but this is a good thing. We just need to CType it to a number.

MessageBox.Show(wksht.Cells(1, CType(wksht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row).Value.ToString, Integer).Value.ToString)

[Mark Douglas]

Hmmm... Ok new errors?

MessageBox.Show(wksht.Cells(1, CType(wksht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row).Value.ToString, Integer).Value.ToString)

At Integer). I get "." expected

Then at Integer) I get "Integer" is a type and cannot be used as an expression.

Then at .Row) . I get "Syntax error in cast operator; two arguments separated by a comma are requsted?

????

[RobDog888]

Ok, try this for getting the last cells contents under Option Strict On.

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim oWB As Excel.Workbook = moApp.Workbooks.Add
        Dim oSht As Excel.Worksheet = DirectCast(oWB.Sheets("Sheet1"), Excel.Worksheet)
        Dim rng As Excel.Range
        Dim i As Integer
        oSht.Cells(10, 1) = "Test"
        i = oSht.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
        rng = oSht.Cells.Range(oSht.Cells(i, 1), oSht.Cells(i, 1))
        MessageBox.Show(rng.Value.ToString) 'Test
    End Sub

[Mark Douglas]

Ok found some code that got it to work for me...

Thanks Rob for all the help!! Sorry I was such a pain with trying to figure this out!!

Dim wksht As Excel.Worksheet
        wksht = CType(Myexcel.ActiveSheet, Excel.Worksheet)
        mycount = wksht.UsedRange.Rows.Count
        CurRow = mycount + 1
 
 
        Dim wkRange As Excel.Range
        Dim lProjNum As String
 
        wkRange = CType(wksht.Cells(mycount, 1), Excel.Range)
        lProjNum = CStr(CLng(wkRange.Value) + 1)

[RobDog888]

NP, yes its the Range object that we both used that was the key to getting around the Strict declaration.

ps, dont forget to Resolve the thread if there is nothing more on this issue.