Create Excel Tabs

[sspittell]

I have the following code :

Private Sub ProbReport()
Dim dbs As Database
Dim rst As Recordset
Dim SQLProb As String
Dim strBranch As String
Dim strReportName As String

strReportName = "zProbReport"
DoCmd.SetWarnings False
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from zTempProb_BranchList")
With rst
.MoveFirst
Do While Not rst.EOF
SQLProb = "Select * into " & strReportName & _
" From ZTempProbXls " & _
"Where Branch = " & rst!Branch
DoCmd.RunSQL (SQLProb)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strReportName, "C:\ams\LCLSProbs.xls", True, rst!Branch
rst.MoveNext
Loop
End With
DoCmd.SetWarnings True
End Sub

I am trying to create an excel worksheet that creates a tab for each "Branch". It works, except it prompts for me to put in the branch, instead of simply running through the loop.


Any ideas?

[NeoPa]

1. strReportName needs to be enclosed in single-quotes (') on line #15.
   

   SQLProb = "Select * into '" & strReportName & _
   "' From ZTempProbXls " & _

2. If you want to put separate data in each WorkSheet then you'll need to clear down the previous data between iterations of your loop. You can add the following line after line #14 to effect this :
   

   DoCmd.RunSQL ("DELETE FROM '" & strReportName & "'")

3. If your Branch field in [ZTempProbXls] is numeric, then ignore this one, but if it's text then line #17 also needs to be changed to enclosed the value in single-quotes (').
   

   "Where Branch = '" & rst!Branch & "'"

4. Lastly, and most importantly, the Range value used (rst!Branch) is not working as expected. Check it out in Help.
   

   Range Optional Variant.
   A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

   It may be a little inaccurate in that the export doesn't actually fail, but you get the idea.
   
   What determines the name of the Worksheet created is the name of the object exported (TableName). In your case you're reusing the same one every time - hence its confusion.
   
   You could introduce some code that renames the object between loop iterations but do be careful. Remember to leave it with a predictable name when finished. Remember too, that as the Branches are names from your data, there is the possibility of further confusion if you have any objects that already use a name that is assigned to one of your branches.

[sspittell]

Thank You, your tips, paid off, works like a charm.

1. strReportName needs to be enclosed in single-quotes (') on line #15.
   

   SQLProb = "Select * into '" & strReportName & _
   "' From ZTempProbXls " & _

2. If you want to put separate data in each WorkSheet then you'll need to clear down the previous data between iterations of your loop. You can add the following line after line #14 to effect this :
   

   DoCmd.RunSQL ("DELETE FROM '" & strReportName & "'")

3. If your Branch field in [ZTempProbXls] is numeric, then ignore this one, but if it's text then line #17 also needs to be changed to enclosed the value in single-quotes (').
   

   "Where Branch = '" & rst!Branch & "'"

4. Lastly, and most importantly, the Range value used (rst!Branch) is not working as expected. Check it out in Help.
   
   It may be a little inaccurate in that the export doesn't actually fail, but you get the idea.
   
   What determines the name of the Worksheet created is the name of the object exported (TableName). In your case you're reusing the same one every time - hence its confusion.
   
   You could introduce some code that renames the object between loop iterations but do be careful. Remember to leave it with a predictable name when finished. Remember too, that as the Branches are names from your data, there is the possibility of further confusion if you have any objects that already use a name that is assigned to one of your branches.

[NeoPa]

Very pleased to hear it :)