Data from Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BruceS - 12 May 2008 00:09 GMT

Hi, all!

Have a 2003 workbook that is used to print forms. On Sheet 1 is the form
and 3 combo boxes used to select values. The data for the combo boxes comes
from Sheet 2.

User wants to automatically update the combo box list data from an Access
database, so they gave it to me (the one who uses Access the most). I have
created a table in Access to create and store the info, but have been unable
to successfully update Sheet 2. When I use transferspreadsheet method in
Access (with "Sheet2$" as the range), it erases the data in Sheet 2 but adds
the new data following where the old data ended. This makes the 3 named
ranges on Sheet 2 contain blanks.

Figure I'm not using the correct approach here. Can someone (1) suggest the
best way to do this and (2) point me to some documentation that tells me how.

Can really use the help...this is due Tuesday morning.

Thanks,
Bruce

Reply to this Message

Ed Ferrero - 12 May 2008 00:23 GMT

Hi Bruce,

An easy way to automatically update an Excel sheet from a database, is to
build
a data table in Excel.

Use Data -> Import External Data -> New Database Query
(if the data does not fit into a worksheet, you can use a pivot table)

Then right-click on any cell containing the imported data and choose
Data Range Properties from the context menu.

There you will find several options for refreshing the data, including
Refresh Data on File Open.

Note that you can also name the imported data range from this dialog.
The name you set here will automatically expand when new data is
imported.

Ed Ferrero
www.edferrero.com

> Hi, all!
>
[quoted text clipped - 23 lines]
> Thanks,
> Bruce

Reply to this Message

BruceS - 12 May 2008 02:23 GMT

Ed,

Thanks for the great, easy lesson! Only issue I had was, by using the
automatically updated range name, it included the column headings in my
pull-down list, which I did not want. Solved that by defining a second range
for each one that did not include the heading.

It's cumbersome because, if the number of lines returned by the query grows,
the bottom ones may not show up in the pull-down list. Is there any way to
NOT return the headings with the data. (I did not see that option.)

Also, any way to prevent the "Automatic Updates Warning" that appears each
time I open the workbook? I clicked the button to "enable automatic
updates", but the warning dialog still appears every time.

You've been a huge help! Thanks!

Bruce

> Hi Bruce,
>
[quoted text clipped - 45 lines]
> > Thanks,
> > Bruce

Reply to this Message

Ed Ferrero - 12 May 2008 09:22 GMT

> Thanks for the great, easy lesson! Only issue I had was, by using the
> automatically updated range name, it included the column headings in my
[quoted text clipped - 7 lines]
> to
> NOT return the headings with the data. (I did not see that option.)

Name the data query range QueryImport, then use Insert -> Name -> Define
to enter a new named range, say QueryData and enter this formula in the
Refers To box;
=OFFSET(QueryImport,1,0,ROWS(QueryImport)-1)

Now QueryData will expand/contract with the imported data and not include
the headings.

> Also, any way to prevent the "Automatic Updates Warning" that appears each
> time I open the workbook? I clicked the button to "enable automatic
> updates", but the warning dialog still appears every time.

The 'enable automatic refresh' warning happens in versions of Excel after
2002.
There is a registry fix to hide it, but I do not recommend changing the
registry.

Instead, go to the Data Range Properties, disable the Refresh data on file
open
checkbox, then add this bit of code to the ThisWorkbook pane.

To add the code, press {Alt}-{F11}, then double-click ThisWorkbook in the
Project Explorer and copy the following code into the code pane.

Private Sub Workbook_Open()
Dim qdf As QueryTable

Set qdf = ActiveSheet.QueryTables("QueryImport")
qdf.Refresh

Set qdf = Nothing
End Sub

Ed Ferrero
www.edferrero.com

Reply to this Message

BruceS - 12 May 2008 17:04 GMT

You're a genius, Ed!

Thanks so much for the help!

Bruce

> > Thanks for the great, easy lesson! Only issue I had was, by using the
> > automatically updated range name, it included the column headings in my
[quoted text clipped - 43 lines]
> Ed Ferrero
> www.edferrero.com

Reply to this Message