Putting database data into a combo box...

  • Thread starter Thread starter Lyndon
  • Start date Start date
L

Lyndon

Hi guys,

I have a working databse query thar returns a list of names. I want to
put this into a combo box. Currently I am inserting the database data
into a range on my spreadsheet (A1) and it uses as many rows as there
are names. In my combo box properties I can specify the ListFillRange
as A1:A5 or whatever and this works okay, but the thing is, as names
are added to the database, I want the range A1:A5 to update
dynamically.

Q1. Can I send the QueryTable data directly to the combo box
ListFillRange...

With ActiveSheet.QueryTables.Add(Connection:=connstring,
Destination:=ComboBox1.ListFillRange etc.

Q2. If I can't do that, is there an Excel function that can tell me
the position of the last record in the range... A10 or A30 or whatever,
so I can use VB code to reset the range after the query returns...

.Refresh
End With

ComboBox1.ListFillRange("A1:" & something here...

Thanks guys.

Cheers,
Lyndon.
 
Hi Lyndon,

You should use ResultRange Property.
ComboBox1.ListFillRange(Sheets("sheet1").QueryTables(1).ResultRange.Address)
Click to expand...

Best

Guillermo

PS: From help...
ResultRange Property
See AlsoApplies ToExampleSpecifics
Returns a Range object that represents the area of the worksheet occupied by
the specified query table. Read-only.

Remarks
The range doesn't include the field name row or the row number column.

Example
This example sums the data in the first column of query table one. The sum
of the first column is displayed below the data range.

Set c1 = Sheets("sheet1").QueryTables(1).ResultRange.Columns(1)
c1.Name = "Column1"
c1.End(xlDown).Offset(2, 0).Formula = "=sum(Column1)"
 
Back
Top