I have an access report that I want to call the "analyze it with Microsoft Excel" option on but I need to do it from VBA so the end users see just the Excel report not the Access report. I'm not sure if this a DoCmd function or not. Any help would be appreciated...
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Call Analyze with Excel from VBA
- Thread starter KimmieB
- Start date
yvonneveltmaat
IS-IT--Management
What I usually do is use the following to export a query directly into Excel: DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, <queryname>, etc. etc.
Is this helpful?
Is this helpful?
MichaelRed
Programmer
In general, you can instantiate Excel from Ms. A.
It is equally pratical to simply use (most) Excel functions from within Ms. A. simplt by adding a reference to the Excel Library to your Ms. A. App. Tricky pary of this is that you must supply 'VALID' arg lists to the functions. MOSTLY, these args are simply range references in Excel, but translates to Congruent ARRAYS or simple variables when called from Ms. A.
Another issue wit SOME functions would be that the Ms. A. CALLING function must be prepared to 'Accept' the return value (type). I suspect this may be an issue for this specific function, "Analyze It" (to the best of my limited memory) wants to generate a "report" based on a single cell (location) which must be able to accept a variable set of results.
Finally, you couldn't send Excel a report at all, you would be sending the (subset of) the report's data on which the analysis would be based and then using the return(s) to extend the report itself.
MichaelRed
It is equally pratical to simply use (most) Excel functions from within Ms. A. simplt by adding a reference to the Excel Library to your Ms. A. App. Tricky pary of this is that you must supply 'VALID' arg lists to the functions. MOSTLY, these args are simply range references in Excel, but translates to Congruent ARRAYS or simple variables when called from Ms. A.
Another issue wit SOME functions would be that the Ms. A. CALLING function must be prepared to 'Accept' the return value (type). I suspect this may be an issue for this specific function, "Analyze It" (to the best of my limited memory) wants to generate a "report" based on a single cell (location) which must be able to accept a variable set of results.
Finally, you couldn't send Excel a report at all, you would be sending the (subset of) the report's data on which the analysis would be based and then using the return(s) to extend the report itself.
MichaelRed
- Status
Similar threads
- Locked
- Question
- Locked
- Question