Joel,

Hm.... I guess my lack of knowledge is confusing both of us. The macro I
pasted was created fully in Excel with its macro recorder. The Access table
has been successfully linked into Excel and the references are to that linked
table. I'm trying to replace the specific ranges in the macro with
"structured references", which I believe are new to Excel 2007. They permit
formulas and functions to reference areas of a table which can expand,
contract, change. That way the user does not have to manually go back in to
the forumula and change the range. I have 10 or so tables being analyzed
with the regression tool and will have a lot of updates in the base Access
table (which get automatically updated in the Excel table, and thus the
changes in range values). But your code has given me an idea how I might try
to do this with VBA instead of a macro. I haven't used VBA in many years but
know I have to probably bite the bullet and see if I can solve my problem
with that tool.

I guess I don't understand your comment "Application.Run
"ATPVBAEN.XLAM!Regress" is using an Excel
Application to run an Access Macro." How does an Access Macro come into play here?

Thanks for you help,

J Austin

"Joel" wrote:

An Excel Application is not the same as an Access Application. They are two
different tools. Application.Run "ATPVBAEN.XLAM!Regress" is using an Excel
Application to run an Access Macro. It won't work. You can run an Access
Application in Excel by doing this

Set obj = CreateObject("Access.Application")
obj.Visible = True
MyDb = obj.OpenCurrentDatabase("C:\temp\mydb.mdb")
obj.Run "ATPVBAEN.XLAM!Regress", _
Table_12Month_Sold[[#All],[Net_SalesPrice]], _
ActiveSheet.Range("$D$1:$M$25"), False, True, ,
ActiveSheet.Range("$A$1") _
, True, False, False, False, , False

You may have to enble a reference library for it to work if you get an error.

Excel VBA Menu - Tools - References - Microsoft Access XX.X Object Library

Make sure you click the box next to the libray and press OK. I'm using
Excel 2003 so enabling the Reference Library may be different in Excel 2007.


"J Austin" wrote:

Joel,
I'm sorry I was not very clear in my question. I'm able to link my access
table into Excel 2007 and as a structured reference the table keeps up to
date with changes in the Access table. I'm trying to run regression analyses
on that table and not wanting to have to manually change the ranges each time
the table updates. I ran a macro doing the regression and it showed the the
ranges in the table. I attempted to replace these (in the macro) with the
structured reference syntax. When I run the macro I get a compile syntax
error. For the example below, I only replaced the first required reference,
but it was the same when I replaced all ranges with the structured reference
syntax. Below is the simple macro. I'm suspecting that the statistical
package add-in does not recognize structured references. Thanks for any help.

Sub Macro3()
'
' Macro3 Macro
'

'
Application.Run "ATPVBAEN.XLAM!Regress",
Table_12Month_Sold[[#All],[Net_SalesPrice]], _
ActiveSheet.Range("$D$1:$M$25"), False, True, ,
ActiveSheet.Range("$A$1") _
, True, False, False, False, , False
End Sub


"Joel" wrote:

When I have trouble getting queries working I perform the operation manually
while recording a macro. Then apply the syntax from the recorded macro to
macro I'm writing.

First turn on the Macro Recorder (tools Macro - start Recording). then
perform the new query (Data - Import External Data - New Database Query).

I never use the recorded macro. Instead I modify the code eliminating
unecessary lines of code; and I also remove SELECTION and replace with actual
ranges.

"J Austin" wrote:

I have named tables linked to Access tables. I am doing repeated regression
analysis on these tables, which change with new data. Can structured
references be put in a macro that would run the regression analyses on these
tables? I'm currently getting a syntax error when I try this approach.

Thanks in advance,

J Austin