Macro/Database question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lj - 28 Sep 2006 22:27 GMT

I am using a macro to connect to the database and update a stored
procedure. I am able to disconnect the excel sheet from the database
manually by going to "data" in the menu, then "import external data",
then "data range properties", and unchecking "save query definition" in
the window. When I try to do this in my macro the macro does not
record my disconnecting the spreadsheet from the database. Any
suggestions on how else to disconnect the query from the spreadsheet in
the macro? Thanks for your help!

Reply to this Message

Huff - 29 Sep 2006 10:25 GMT

lj

Are you trying to run an Access macro from within an Excel macro, or
merely bring results from an Access query onto an Excel sheet?

For the former you could use something like this:
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase "\\[path and file name of database
here].mdb"
appAccess.Visible = True
appAccess.DoCmd.RunMacro "[Access macro name]"
appAccess.CloseCurrentDatabase
appAccess.Quit

For the latter you could create a refresh query on the Excel sheet that
you want the data displayed on, then run the refresh query through the
macro using a method like:
Worksheets("[Sheet name the refresh query is
on]").Cells([cell reference in R1 C1 style that the query runs
on]).QueryTable.Refresh BackgroundQuery:=False

Hope there's something of use in there.

Reply to this Message