SQL with excle data

[dhartford]

I have a combo box to list data from a spreadsheep. Can I use ADO connection and recordset to SQL data from a excel spreadsheep? If so, what is the syntex?

Thanks in advance.

[Bob Phillips]

Are you trying to update Access from Excel?

Do you want to update Access with a combo range, or something else?

[dhartford]

Are you trying to update Access from Excel?

Do you want to update Access with a combo cgange, or something els?

Yes, I'm trying to update a combo box in Access with the data from Excel.

Thanks.

[stanl]

Can I use ADO connection and recordset to SQL data from a excel spreadsheep?


Should be a matter of opening up a connection object to the worksheet that contains the data, then populating the combo box with additem() assuming you have created an Access form.

There is no use in proceeding unless you can verify the Excel data can be queried in ADO. No use guessing at that, best if you could attach a sample of the Excel data. .02 Stan

[Bob Phillips]

It is simple to to use ADO to query Excel, and you can always retrieve the data intoan array, and load the combo with the array.

[Slyboots]

Here's one approach. Let's say I have an external Excel spreadsheet, where the first page contains a list of items I want to use to populate the dropdown.

In Access, I select File/Get External Data/Link Tables
In the dialog box, I select the Excel file type from the dropdown and point to the Excel file.

I now have a link table in Access that points to the Excel sheet. Now, I can write a simple query for it, and then use that query as the source for the dropdown.

If this works for you, there's no need to use ADO or recordset. The Access link will take care of it.

S

[dhartford]

If this works for you, there's no need to use ADO or recordset. The Access link will take care of it.

S

Thank you for your reply. But I don't want user to view the data in spreadsheet, so can't link the spreadsheet in.

[Norie]

You don't want the user to view the data from the spreadsheet?:huh:

[CreganTur]

dhartford,

take a look at my response to your listbox problem (http://www.vbaexpress.com/forum/showthread.php?t=22448). You can use everything I provided for your listbox to populate your combobox with data- you just need to reference the correct object.