Using VB Express to Automate Excel 1

[schnabs]

Hello,
I am using VB Express to automate some functions in Excel. Currently I am trying to populate several cells with text. I was able to do this quite easily using the VBA built into Excel, but I want to automate everything through VB, rather than running a macro from another sheet.

The following is the code I used in Excel VBA.

'oxl.ActiveSheet.Range("A65536").End(xlUp).Select()

oxl.ActiveSheet.ActiveCell.Offset(3, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = "KEY"
oxl.ActiveSheet.ActiveCell.Offset(2, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = ". = Late Fee"
oxl.ActiveSheet.ActiveCell.Offset(1, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = "BMI = Office Letter"
oxl.ActiveSheet.ActiveCell.Offset(1, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = "Att = Attorney Letter"
oxl.ActiveSheet.ActiveCell.Offset(1, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = "L = Lien"
oxl.ActiveSheet.ActiveCell.Offset(1, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = "PP = Payment Plan"
oxl.ActiveSheet.ActiveCell.Offset(1, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = "Bankruptcy = Bankruptcy"
oxl.ActiveSheet.ActiveCell.Offset(1, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = "BF = Bank Foreclosure"
oxl.ActiveSheet.ActiveCell.Offset(1, 0).Select()
oxl.ActiveSheet.ActiveCell.FormulaR1C1 = "AF = Association Foreclosure"
oxl.ActiveSheet.ActiveCell.Offset(1, 0).Select()

I can't seem to figure out where I need to modify this in order to be usable in VB. Thanks in advance for the help.

 

[RiverGuy]

There's a bunch of tutorials on this topic out there. Here's one:

http://support.microsoft.com/kb/302094

The bottom line comes down to this: First, you need to decide if you want to reference the Excel library version of your choice, or if you want to use late binding. If you reference Excel, your users will need to have the same version of Excel installed. If you use late binding, they can have another version where your function calls are valid, but you lose intellisense while developing. After that, it's really just about using the correct functions from the Excel libraries.

 

[schnabs]

Well I think that is my problem, the correct functions from the Excel libraries. xlUp doesn't seem to be the right one, but I can't find out for the life of me what the correct syntax is.

 

[RiverGuy]

Here's how you find that out. xlUp is a constant. That means it's a value given a name. So, in your Excel workbook, write a macro that does the following:

MsgBox(xlUp)

This should show you the value of xlUp. You can then use that value in your .Net program.

 

[schnabs]

Wow that's pretty useful. Thanks River.