Access 2002, "Loop" in VBA to save Query in Excel

[13badar]

[blue]The following command button when clicked, saves the Query in Access and exports that Query to Excel.
I get a Run-Time Error '424' "Object Required".
I set A.BranchName as MSA_ARRAY(Iteration).Name
BranchName is what I want to pull from the Main SQL Query and save it as an Excel Worksheet with the worksheet name specified as BranchName Query. I want to save all BranchNames as separate worksheets in the same Excel File Path.[/blue]


Private Sub cmdExport_Click()
Dim ReportType As String
Dim Path As String
Dim SQL As String
Dim db As Database
Dim MSA_ARRAY
Dim Counter As Integer
Dim Iteration As Integer

ReportType = cboReport.Value
MSA_ARRAY = Array("Altamont", "Castanoan", "Blossom")
Counter = 0
Iteration = -1
Do While Counter <> 4
Counter = Counter + 1
Iteration = Iteration + 1

SQL = "SELECT A.PID, A.CID, A.month, A.contractyear, "
SQL = SQL & "A.[AM/SE], A.[members], A.[Final PMPM], "
SQL = SQL & "A.BranchName, B.Report"
SQL = SQL & " FROM tbl1 AS B INNER JOIN tbl2 AS A"
SQL = SQL & " ON B.BranchName = A.BranchName"
SQL = SQL & " WHERE B.Report='" & ReportType & "'"
SQL = SQL & " And A.month>=" & cboBegMonth.Value
SQL = SQL & " And A.month<=" & cboEndMonth.Value
SQL = SQL & " And A.contractyear>=" & cboBegYear.Value
SQL = SQL & " And A.contractyear<=" & cboEndYear.Value
SQL = SQL & " and A.BranchName=" & MSA_ARRAY(Iteration).Name
SQL = SQL & " Order by A.PID"

Set db = CurrentDb
db.CreateQueryDef MSA_ARRAY(Iteration).Name & " Query", SQL
Path = txtPath.Value
DoCmd.TransferSpreadsheet acExport, , MSA_ARRAY(Iteration).Name & " Query", "" & Path & "", True

Loop

MsgBox "All Branches are saved as " & Path, vbInformation

End Sub

 

[PHV]

Replace all instances of this:
MSA_ARRAY(Iteration).Name
to this:
MSA_ARRAY(Iteration)

You have to surround this value by single quotes in your SQL.
Are you sure that the Path variable should be the same for each iteration ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

yeah the Path is basically:
C:\Documents and Settings\Badar\Desktop\Reports.xls
I want to save all Branches Info. into the same Excel File named "Reports.xls". In that File, There will be separate Worksheets, with each Worksheet named the actual name of BranchName, which comes from Tbl1 as a column named BranchName. That worksheet will save the Query containing that BranchName only and so on for the rest of the branches.

I did try my code again with your changes and it gives me an error:
Run-Time Error '9'
Subscript Out of Range
and highlighted line was:
SQL = SQL & " and A.BranchName='" & MSA_ARRAY(Iteration) & "'"

After running my program, It did save the three Queries for the three different Branches I specified:
("Altamont", "Castanoan", "Blossom")
But all three were empty. as in No Data in them.

 

[PHV]

And this ?
MSA_ARRAY = Array("Altamont", "Castanoan", "Blossom")
For Each MSA_Branch In MSA_ARRAY
SQL = "SELECT A.PID, A.CID, A.month, A.contractyear, "
SQL = SQL & "A.[AM/SE], A.[members], A.[Final PMPM], "
SQL = SQL & "A.BranchName, B.Report"
SQL = SQL & " FROM tbl1 AS B INNER JOIN tbl2 AS A"
SQL = SQL & " ON B.BranchName = A.BranchName"
SQL = SQL & " WHERE B.Report='" & ReportType & "'"
SQL = SQL & " And A.month>=" & cboBegMonth.Value
SQL = SQL & " And A.month<=" & cboEndMonth.Value
SQL = SQL & " And A.contractyear>=" & cboBegYear.Value
SQL = SQL & " And A.contractyear<=" & cboEndYear.Value
SQL = SQL & " and A.BranchName='" & MSA_Branch & "'"
SQL = SQL & " Order by A.PID"
CurrentDB.CreateQueryDef MSA_Branch & " Query", SQL
Path = txtPath.Value
DoCmd.TransferSpreadsheet acExport, , MSA_Branch & " Query", "" & Path & "", True
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

What's MSA_Branch
Do I have to Actually type in Each Branch Name to do this??

Also, just wanted to mention, earlier Code works to the point that it saves the Query alright with all the fields of the table that I wanted, its just empty.
Also, the excel file is also populated with different BranchNames as the names of the Worksheets. Only problem is that there is no data in them.

 

[13badar]

OK PHV, it is working now. All of the Branches saved. One problem:
It is saving all of the Branches, even the ones that aren't in the cboReport.Value.
cboReport.Value have three REGIONS:
Emory, Large, Woodside.
There is more than one branch that lies within these regions. The Tbl1 I used in Query above has two columns:
"BranchName" has all the Branches written in it as records.
"Report" has all three regions in it. Then I joined Tbl1 and Tbl2 to generate reports for each Branch by their respective Region.
When I run the Query, I choose Region: "Emory". and it creates all the Queries for all the branches even the ones that arent included in the "Emory" Region.
What should I do to make sure that only Branches in the region that is selected by cboReport.Value shows.
Thanks a lot for all your help.

 

[PHV]

Something like this ?
If DCount("*", "[" & MSA_Branch & " Query]") > 0 Then
DoCmd.TransferSpreadsheet acExport, , MSA_Branch & " Query", "" & Path & "", True
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[13badar]

Thanx, It does return only the Queries that have Data in them. However, I wanted to return all the Branches in the Region ("Report"). Not just the Filled Ones.
If I am selecting Region "Emory" from the Report Combo Box, I want to return all the Branches that are present in that Region, "Data or No Data". I dont want to return all the Branches that aren't in that Region.
I think I might have to change My JOIN type (INNER JOIN currently) in my SQL Statement, Any thoughts ??