Exporting a table into excel

[Hello1]

Exporting a table with drop down field into excel

Export works fine (on a button click) but I have a field in the table which is a Display contro:list box, Row Source type: Value list... and I can choose between 2 values.
In the new made excel file I cant choose the values and I would like to.
Any way to format those cells so they have the value list box with VBA?
Or is there an easier way maybe?

Thanks

 

[Minty]

Not easily, you are exporting the data, not the underlying source.
Excel doesn't know the data is in a look up.

Generally Lookup fields in tables are frowned upon, although a value list probably isn't quite as bad. Have a read http://access.mvps.org/access/lookupfields.htm for some background.

 

[Hello1]

Alright, the lookup field isnt really important that much, I dont need it in the table.
Is there a way for example, while the workbook is being created to tell excel: take cells from C16 to C200 and open data validation for those cells and set Allow: List and Source: Value1; Value2?

 

[Hello1]

Dim MyList(1) As String
MyList(0) = "Value1"
MyList(1) = "Value2"

With Range("C9").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyList, ";")
End With

I found this and it works just how could I make it select all the cells from C9 down to the last one with data in it (for example C105)?

Edit:

Dim MyList(1) As String
MyList(0) = "Value1"
MyList(1) = "Value2"

With .Range("C9", .Range("C9").End(xlDown)).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyList, ";")
End With

Now it works as I want it to, hopefully it will help someone in future
I will still do some testing