473,324 Members | 2,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Create Excel Tabs

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
3 2063
NeoPa
32,556 Expert Mod 16PB
  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
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
32,556 Expert Mod 16PB
Very pleased to hear it :)
May 9 '08 #4

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

Similar topics

3
by: Paul | last post by:
I have written code to transfer a table to an Excel document. However, I do not know the code to add some "bells and whistles" to the document. Can someone direct me to a reference on the Internet...
1
by: barma16 | last post by:
I've hit a bit of a brick wall here, and could use some advice. I have an Access application whose output is a four-tab Excel spreadsheet where three of the four tabs are the result of database...
1
by: Dan | last post by:
I have a c# app that creates and Excel doc, I would like to know how to create new worksheets (tabs) in the excel doc using c#?
4
by: Ola Abdel Mageed | last post by:
Hi all, I'm working on a html project where i need to put na excel file(which i made it an html page)i it. The problem is that the excel file has sheets in the form of tabs and when i open this...
0
by: kennedystephen | last post by:
For the life of me, I cannot get this ... I have 1 excel document. I want to open that document and copy the first 50 rows to a new document. Then get the next 50 rows and copy those to a brand...
18
by: Landaverry | last post by:
I have an excel file that has over 20 tabs. I need to import all tabs into one table and am having trouble finding code to do this. I would liike the user to be able to press on button, navigate to...
2
by: mwilliams4 | last post by:
I have an Access 2003 table that I want to generate into tabbed Excel 2003 workbooks using a button on a form. The tables would need to overwrite everytime that the button would be pressed. ...
10
by: afromanam | last post by:
Regards, Please help What I'm trying to do is this: (and I can't use reports since I must export to Excel) I export some queries to different tabs in an excel workbook I then loop through...
7
by: rameshvummadi | last post by:
I want to create an excel file (Microsoft Excel) with tabs using perl. I need to run my perl script in the Unix environment. I can do it with Spreadsheet::WriteExcel. Can I create the excel file...
1
by: rinkkunitr | last post by:
i have one asp page from where i am calling a xml page.from this xml page i m calling two asp pages to get an excel with two different worksheets. The variable containing a string, having some values...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.