By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Create Excel Tabs

P: 2
I have the following code :
Expand|Select|Wrap|Line Numbers
  1. Private Sub ProbReport()
  2. Dim dbs As Database
  3. Dim rst As Recordset
  4. Dim SQLProb As String
  5. Dim strBranch As String
  6. Dim strReportName As String
  7.  
  8. strReportName = "zProbReport"
  9. DoCmd.SetWarnings False
  10. Set dbs = CurrentDb
  11. Set rst = dbs.OpenRecordset("Select * from zTempProb_BranchList")
  12. With rst
  13. .MoveFirst
  14. Do While Not rst.EOF
  15. SQLProb = "Select * into " & strReportName & _
  16. " From ZTempProbXls " & _
  17. "Where Branch = " & rst!Branch
  18. DoCmd.RunSQL (SQLProb)
  19. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strReportName, "C:\ams\LCLSProbs.xls", True, rst!Branch
  20. rst.MoveNext
  21. Loop
  22. End With
  23. DoCmd.SetWarnings True
  24. 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?
May 5 '08 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
  1. strReportName needs to be enclosed in single-quotes (') on line #15.
    Expand|Select|Wrap|Line Numbers
    1. SQLProb = "Select * into '" & strReportName & _
    2. "' 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 :
    Expand|Select|Wrap|Line Numbers
    1. 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 (').
    Expand|Select|Wrap|Line Numbers
    1. "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.
May 5 '08 #2

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

  1. strReportName needs to be enclosed in single-quotes (') on line #15.
    Expand|Select|Wrap|Line Numbers
    1. SQLProb = "Select * into '" & strReportName & _
    2. "' 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 :
    Expand|Select|Wrap|Line Numbers
    1. 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 (').
    Expand|Select|Wrap|Line Numbers
    1. "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.
May 9 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
Very pleased to hear it :)
May 9 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.