I haven't used Excel 2007 so I don't know anything about advance features.
Let me explain a little about Microsoft Office. All office product have the
same structure. Office files consit of different object like Tables,
Documents, Pictures. both Excel worksheets and Access Databases are Tables.
Excel can Access the table completely random while access you have to move
one row at a time and then featch the data from a particular column.
I'm not sure what you think is the difference between a macro and VBA. I
think anything you can do in VBA can be done in a macro if you know that
right tricks. You can access all the VBA libraries through Wind32 dll using
Excel Macros. You just have to properly define the Library Calls.
I made a mistake with my comment about "Application.Run
"ATPVBAEN.XLAM!Regress". I though this was a macro in an Acces Database, not
an Excel Macro. for this statement to run properly you would neet the excel
file ATPVBAEN.XLAM opened and the file would have a macro called Regress.
I looked at the Excel 2003 help for Application and selected "Method Run".
There is a restrictiion of passing object using the Run Method. This may not
apply to 2007, not sure.
Table_12Month_Sold[[#All],[Net_SalesPrice]]
I think this table would be an object. Changing this to a string may solve
you problem
"Table_12Month_Sold[[#All],[Net_SalesPrice]]"
If you post you Query Regress I think I can solve your problem. Look
closely at the Query. The query consists of strings and Objects. I believe
the "Table_12Month_Sold[[#All],[Net_SalesPrice]]" is really a string inside
the query. and the passed parameter can simply be put into the string portion
of the query using ampersands
Something like this
Sub Results (TableQuery as string, ............)
from
Myquery = "abc ....... Table_12Month_Sold[[#All],[Net_SalesPrice]] ......"
to
Myquery = "abc ....... " & TableQuery & " ......"
J Austin said:
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 said:
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.
:
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
:
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.
:
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
