Joel,

I tired making LoopReport a sub instead of a function. When I ran it did
not close again. This time I took note of the message that pops up if I
click on Excel, "Microsoft Excel is waiting for another application to
complete an OLE action." As soon as I click OK the .mdb seems to close fine
and my Excel VBA code continues as it should. Below is the code I now have
in LoopReport since it has changed a bit from before. Thoughts? See below.
Thanks for taking time to look into this.

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

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

Sub LoopReport(accts() As String, startDate As Date, endDate As Date)
'Make sure the "Microsoft DAO 3.6 Object Library" is checked in
'Tools - References menu (should be checked already though)
Dim rawFile As String
Dim i As Integer, tmp As Integer, numAccts As Integer
Dim varStatus As Variant
Dim qdf As QueryDef

rawFile = "G:\PC Reports New\RawAcctDump.xls"
numAccts = UBound(accts)

DoCmd.SetWarnings False
For i = 1 To numAccts
varStatus = SysCmd(acSysCmdSetStatus, "Processing account " &
accts(i) & "...")
If i = 1 Then Kill rawFile

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

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"
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
varStatus = SysCmd(acSysCmdClearStatus)
DoCmd.SetWarnings True
End Sub
--------------------------------------------------------------------------


"Joel" wrote:

Why do you have LoopReport as a function when it is not returning any
variables. Why not make it a Sub. I believe the crashing is occuring
because you have a stack error. When you declare the routine a function it
is expecting something to be put on the stack and something to be taken of
the stack. You are calling LoopReport from excel like a Sub which isn't
putting anything on the stack. Then Access is leaving a space on the stack
for a return variable where excel didn't make room for the return variable on
the stack. This create a stack mis-match and the cause of the crash.

"Michelle" wrote:

Joel,

Thank you for your idea. I actually can't use the line
Results = LoopReport(NumberofAccounts, accts, startDate)
because LoopReport is a function in Access not Excel where I'm trying to
call it from. Excel won't recognize LoopReport and error out using the above
line. I did, however, figure out the syntax to properly pass parameters into
Access from Excel.

Dim A As Access.Application
Set A = New Access.Application
A.Visible = True
A.OpenCurrentDatabase "G:PC Reports New\PC Report.mdb"
A.Run "LoopReport", ans, accts(), startDate, endDate

Where the function is set up as
Function LoopReport(NumAccts As Integer, accts() As String, startDate As
Date, endDate as Date)

Now for my other question as to why Access doesn't always close, I'm still a
little perplexed. I have tried running the procedure directly from Access
and not using Excel. (I was testing my function before moving on to the
Excel portion.) I never received any errors in Access. Everything seemed to
run properly. Do you think it has something to do with the fact that I have
the database automatically compact and repair the .mdb file every time it
closes? I do this because otherwise the .mdb file seems to grow very big
very quickly. I neglected to mention this before, my apologies. Your
thoughts?

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


"Joel" wrote:

I suspect that Access isn't closing because you are getting some errors in
Access and it is not returning to excel

Does the code below help with the parameter passing? With the IsMissing you
can either call the function with or without parameters. The call wil be
with

Sub ExcelCall
Dim Accts as Variant

NumberofAccounts = 10
Redim Accts(5)
Accts(0) = "Michelle Account"

startDate = "3/1/09"

Results = LoopReport(NumberofAccounts, accts, startDate)

end Sub


Function LoopReport(Optional NumberofAccounts as Integer, Optional accts As
Variant, Optional startDate as String)

If IsMissing(NumberofAccounts) Then
NumberofAccounts = 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")
End If

If IsMissing(accts) Then
ReDim accts(1 To NumberofAccounts)
For i = 1 To NumberofAccounts
accts(i) = InputBox("Enter account " & i & ":", "Account Parameters")
Next i
End If
If IsMissing(startDate) Then
startDate = InputBox("Enter the start date for the range of the report."
& _
vbNewLine & vbNewLine & _
"Please do not input more than 1 month.", "Date Range")
End If


End Function