Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access doesn't always close (Excel VBA) & passing parameters
Hello all!
I'm using Access 2003 and Excel 2000 (1) I'm trying to run an Access macro from Excel. The code I have works, but Access doesn't always quit. Sometimes there's no problem. Other times I can see the macro is done, but Access doesn't close. I have to click on Excel which gives me an error message which i click "OK", and then closes Access and everything runs as it should. This is the code that I currently have. Dim A As Access.Application Set A = New Access.Application A.Visible = True A.OpenCurrentDatabase "G:\PC Reports New\PC Report.mdb" A.DoCmd.RunMacro "Multiple Accounts Report" A.CloseCurrentDatabase A.Quit Set A = Nothing ' more Excel VBA code that works properly (2) On a related note, I've been scouring around looking to see if there's any way to pass in parameters directly into the Access module. I have found nothing useful. Currently Access starts from Excel and runs the macro which only consists of the line RunCode LoopReport. Once LoopReport starts the user has to input parameters that are then used for a make table query. It would make my life easier if I could have the user input the parameters in Excel and then pass them into Access. Something along the lines of A.DoCmd.RunModule "LoopReport(x,y)" to replace the line A.DoCmd.RunMacro "Multiple Accounts Report" LoopReport being the underlying module in the "Multiple Accounts Report" macro and x,y being the parameters I want to pass from Excel to Access. It appears there's no RunModule method though. Is there something else I can use? Hopefully that all makes sense. Any help would be much appreciated, particularly the first part. Thanks! -- Cheers, Michelle "Anyone who says he can see through women is missing a lot." Groucho Marx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access doesn't always close (Excel VBA) & passing parameters
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. "Michelle" wrote: Hello all! I'm using Access 2003 and Excel 2000 (1) I'm trying to run an Access macro from Excel. The code I have works, but Access doesn't always quit. Sometimes there's no problem. Other times I can see the macro is done, but Access doesn't close. I have to click on Excel which gives me an error message which i click "OK", and then closes Access and everything runs as it should. This is the code that I currently have. Dim A As Access.Application Set A = New Access.Application A.Visible = True A.OpenCurrentDatabase "G:\PC Reports New\PC Report.mdb" A.DoCmd.RunMacro "Multiple Accounts Report" A.CloseCurrentDatabase A.Quit Set A = Nothing ' more Excel VBA code that works properly (2) On a related note, I've been scouring around looking to see if there's any way to pass in parameters directly into the Access module. I have found nothing useful. Currently Access starts from Excel and runs the macro which only consists of the line RunCode LoopReport. Once LoopReport starts the user has to input parameters that are then used for a make table query. It would make my life easier if I could have the user input the parameters in Excel and then pass them into Access. Something along the lines of A.DoCmd.RunModule "LoopReport(x,y)" to replace the line A.DoCmd.RunMacro "Multiple Accounts Report" LoopReport being the underlying module in the "Multiple Accounts Report" macro and x,y being the parameters I want to pass from Excel to Access. It appears there's no RunModule method though. Is there something else I can use? Hopefully that all makes sense. Any help would be much appreciated, particularly the first part. Thanks! -- Cheers, Michelle "Anyone who says he can see through women is missing a lot." Groucho Marx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access doesn't always close (Excel VBA) & passing parameters
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access doesn't always close (Excel VBA) & passing parameters
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 "Michelle" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access doesn't always close (Excel VBA) & passing parameters
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access doesn't always close (Excel VBA) & passing parameters
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access doesn't always close (Excel VBA) & passing parameters
Joel,
I had it as a function because originally I was trying to run the code by calling an Access macro in Excel, and I thought the code had to be set up as a function in order to use the line RunCode LoopReport in a macro. Perhaps I was misinformed on this? Since I now know how to run a function or sub directly using A.Run instead of A.DoCmd.RunMacro I can try your suggestion. Thanks. Will let you know how it goes. -- Cheers, Michelle "Anyone who says he can see through women is missing a lot." Groucho Marx "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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access doesn't always close (Excel VBA) & passing parameters
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query from Excel to Access - Passing Parameters? | Excel Programming | |||
Excel VBA Passing Parameters Through Cell | Excel Programming | |||
Passing Parameters to Word from Excel | Excel Programming | |||
Passing parameters from excel to access | Excel Programming | |||
Passing Excel Objects As Parameters | Excel Programming |