asked on

excel automation inside VB missing declarations

Hi.

I have an excel sheet inside my VB application. I need to make a drop down box for selections.
The code to make the drop down, according to the VBA inside Excel is:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$2:$A$4"

Only problem, is inside VB, it does not recognize "xlValidateList" or "xlValidAlertStop" or "xlBetween"

It IS clunky having to use Excel inside VB, but unfortunatly i need it.

I have the following references:

* axowc11
* excel
* microsoft.office.core
*OWC11

What am i missing?

Thanks
Victoria

vadim63

Hi enari,

Add this line before your class:

Imports Microsoft.Office.Interop

And then, VB will recognize it:

Excel.XlDVType.xlValidateList
Excel.XlDVAlertStyle.xlValidAlertStop
Excel.XlFormatConditionOperator.xlBetween

Cheers!

enari

ASKER

Thanks, but I get the following error then:

Namespace or type 'Interop' for the Imports 'Microsoft.Office.Interop' cannot be found.

ASKER CERTIFIED SOLUTION

vadim63

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enari

ASKER

I had the wrong interops

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno2k3ta/html/OfficePrimaryInteropAssembliesFAQ.asp

enari

ASKER

except...

Run-time exception thrown : System.MissingMemberException - Public member 'Validation' on type 'Range' not found.

For i = 2 To irows - 1
For j = 2 To icols - 1
loc = Me.cbxAlpha.Text & i
With Me.SpdAttend.Worksheets(1).range("b5").Validation
.Delete()
.Add(Type:=Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, Operator:=Excel.XlFormatConditionOperator.xlBetween, Formula1:="=$Z$1:$Z$4")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With