Hi,

I am in the process of determining whether it is feasible to move upstream
into Access, a process currently run in Excel.

BACKGROUND:
1. An Access program extracts data from a data warehouse, massages the data,
and produces Excel spreadsheet(s) to be used as input to the Excel "report
generation" spreadsheet in #2.
2. An Excel "report generation" spreadsheet has been created (not by me, so
I am inheriting this), which has a menu on it, from which, the user may
select up to 8 different Pivot Table reports to generate. When the user
selects a report to run, a subroutine is run, which
a. presents the user with a dialog box to select which Excel spreadsheet
created in #1 above to use as input.
b. Reads the column headings on the input data to create a menu item for
each field in the input sheet, such that, if the user selects that field, it
becomes part of the pivot table.
c. Prompts the user as to where to save the report, suggesting a file
location and file name.

What I would like to do is:
1. Change the Access program to ask the user which report(s) to create, and
the data source file name (Excel input file).
2. Based on the user selection in #1, have Access call Excel to run the
appropriate Excel VBA code to generate the report(s) selected by the user in
#1.

Can anybody shed any light on whether this can be done, and point me in the
right direction for how to accomplish this?

My thought is to devise a method of passing the Excel "report generator"
spreadsheet the parameters necessary to execute the approporiate section of
code and where to store the completed report(s).

Sample code follows, which is how one of the reports is generated currently
through user selection:

Sub DataFile_FC_DOM_Pending()
Dim strInputFile, strOutputFile As String

Title = "FC Dom - Pending Cases"
ReportFileType = "FC_DOM_Pending_"
ReportDateType = "snapshot"
DateCell = "B12"
PrimaryLabel = "Family Court Domestic: Pending Cases" 'this is the label
above pivot table
PivotLabelRange = "B6:B7"
WebLabelRange = "B7"

' Use local paths during testing
LinkSheetsPath = "Y:\Reports\LinksSheet\DomCaseMgt_Links.xls"

LinkSheetName = "Dom1" 'worksheet name in the links workbook
LinkCellLocation = "A3"

' Convert Text File To Excel
strInputFile = "v:\rdm92ax.txt"
strOutputFile = "Y:\PrepData\FC_DOM_Pending_.xls"

Call ConvertToExcel(strInputFile, strOutputFile)
If strOutputFile = "" Then
Exit Sub
End If
' ***

Call Open_Data_File(ReportFileType, Title, "y:\prepdata\")

If DataFileName = False Then Exit Sub
Call Create_Report(ReportFileType, Title)
Call FieldReportShowAll("Rundate", xlRowField)
Call FieldReportShowAll("Court", xlRowField)
Call FieldReportShowAll("Type", xlRowField)
Call FieldReportShowAll("Track", xlRowField)
Call FieldReportShowAll("Over?", xlColumnField)
ReportDate = Year(Range(DateCell).Value) & "-" & _
Month(Range(DateCell).Value) & "-" & _
Day(Range(DateCell).Value)
ReportFileName = ReportFileType & ReportDate
Call Save_Report(PrimaryLabel, ReportFileName, ReportDate, _
ReportDateType, LinkSheetName, LinkCellLocation)

' Delete .xls file used as input which is called "output" but is really
converted from the .txt file
Kill strOutputFile

End Sub

--
David