Joel,

Thanks for the input. I'm assuming you were responding to the 2nd part. I
understand how to modify the Access module to take inputs, and I know how to
use the InputBox in Excel to get the user to input the parameters. I just
don't know how to get the parameters that the user enters in the inputBox in
Excel into Access.

ans = InputBox("How many accounts would you like to look at?" & vbNewLine &
vbNewLine & _
"Please note that the more accounts you choose the longer the queries will
take to run.", "PC Report")
ReDim accts(1 To ans)
For i = 1 To ans
accts(i) = InputBox("Enter account " & i & ":", "Account Parameters")
Next i
startDate = InputBox("Enter the start date for the range of the report." &
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")
endDate = InputBox("Enter the end date for the range of the report." &
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")

Function LoopReport(accts() as String, startDate as Date, endDate as Date)
'do all this stuff
End Function

I more need to know the proper code to write in Excel to pass the parameters
into Access. Something along the lines of ...
A.DoCmd.RunModule "LoopReport(" & accts() & "," & startDate & "," endDate &
")" ??

Does that make sense? I didn't want to post the current Access module
because it's long, but if you feel it's necessary please see below. Thanks.

--
Cheers,
Michelle
"Anyone who says he can see through women is missing a lot." Groucho Marx

---------------------------------------------
Option Compare Database

Function LoopReport()
'Make sure the "Microsoft DAO 3.6 Object Library" is checked in
'Tools - References menu (should be checked already though)

Const rawFile As String = "G:\PC Reports New\RawAcctDump.xls"
Dim i As Integer, ans As Integer, tmp As Integer
Dim startDate As Date, endDate As Date
Dim varStatus As Variant
Dim accts() As String
Dim qdf As QueryDef

'User input boxes to get parameters for the MakeTable query
ans = InputBox("How many accounts would you like to look at?" &
vbNewLine & vbNewLine & _
"Please note that the more accounts you choose the longer the
queries will take to run.", "PC Report")
ReDim accts(1 To ans)
For i = 1 To ans
accts(i) = InputBox("Enter account " & i & ":", "Account Parameters")
Next i
startDate = InputBox("Enter the start date for the range of the report."
& vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")
endDate = InputBox("Enter the end date for the range of the report." &
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")

DoCmd.SetWarnings False
For i = 1 To ans
'To notify user which acct is going through the process
varStatus = SysCmd(acSysCmdSetStatus, "Processing account " &
accts(i) & "...")
If i = 1 Then Kill rawFile

'Run MakeTable query based on user input
CurrentDb.TableDefs.Delete "DailyInfo"
Err.Clear
Set qdf = CurrentDb.QueryDefs("PC_Report__DailyInformation")
qdf.Parameters("Account") = accts(i)
qdf.Parameters("firstDate") = startDate
qdf.Parameters("lastDate") = endDate
qdf.Execute
Set qdf = Nothing

'all the other queries necessary
DoCmd.OpenQuery "BegEndDate"
DoCmd.OpenQuery "CreateDates"
DoCmd.OpenQuery "PC_Report_Prepymt"
DoCmd.OpenQuery "PC_Report_MBS"
DoCmd.OpenQuery "PC_Report_TS1"
DoCmd.OpenQuery "PC_Report_TS2"
DoCmd.OpenQuery "PC_Report_FX1"
DoCmd.OpenQuery "PC_Report_FX2"
DoCmd.OpenQuery "PC_Report_Corp_Credit1"
DoCmd.OpenQuery "PC_Report_Corp_Credit2"
DoCmd.OpenQuery "PC_Report_Corp_Credit_Syn"
DoCmd.OpenQuery "PC_Report_Structured"
DoCmd.OpenQuery "PC_Report_EM1"
DoCmd.OpenQuery "PC_Report_EM2"
DoCmd.OpenQuery "PC_Report_Sort"
'Export data for use in Excel macro
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_Report_FINAL", _
rawFile, _
True, _
accts(i) & "_RangeData"

DoCmd.OpenQuery "PC_MaxDayInfo"
DoCmd.OpenQuery "PC_Report_PrepymtD"
DoCmd.OpenQuery "PC_Report_MBS_D"
DoCmd.OpenQuery "PC_Report_TS1D"
DoCmd.OpenQuery "PC_Report_TS2D"
DoCmd.OpenQuery "PC_Report_FX1D"
DoCmd.OpenQuery "PC_Report_FX2D"
DoCmd.OpenQuery "PC_Report_Corp_Credit1D"
DoCmd.OpenQuery "PC_Report_Corp_Credit2D"
DoCmd.OpenQuery "PC_Report_Corp_Credit_SynD"
DoCmd.OpenQuery "PC_Report_StructuredD"
DoCmd.OpenQuery "PC_Report_EM1D"
DoCmd.OpenQuery "PC_Report_EM2D"
DoCmd.OpenQuery "PC_Report_SortD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_Report_FINALd", _
rawFile, _
True, _
accts(i) & "_LastDay"

DoCmd.OpenQuery "PC_Contr_bottomD"
DoCmd.OpenQuery "PC_Contr_bottomMTD"
DoCmd.OpenQuery "PC_Contr_topD"
DoCmd.OpenQuery "PC_Contr_topMTD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrBottomD", _
rawFile, _
True, _
accts(i) & "_BottomDaily"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrBottomMTD", _
rawFile, _
True, _
accts(i) & "_BottomMTD"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrTopD", _
rawFile, _
True, _
accts(i) & "_TopDaily"
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel8, _
"PC_ContrTopMTD", _
rawFile, _
True, _
accts(i) & "_TopMTD"
Next i
'Set status bar back to normal to signify module is done running.
varStatus = SysCmd(acSysCmdClearStatus)
DoCmd.SetWarnings True
End Function
---------------------------------------------

"Joel" wrote:

You have to modify the access macro or make a new macro in access that will
run either:

1) Using prompt for inputs
2) Take input from input parameters.

You need to post the Access macro.