Data from Access
 

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

Data from Access
 

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!

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

Data from Access
 

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

"Ed Ferrero" wrote:

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!

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

Data from Access
 

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.)

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

Data from Access
 

You're a genius, Ed!

Thanks so much for the help!

Bruce

"Ed Ferrero" wrote:

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.)

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