Alpha Video Training
Page 2 of 2 FirstFirst 12
Results 31 to 46 of 46

Thread: Creating Multiple Excel Worksheets from one export Script

  1. #31
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Hi Stan. Tried your last suggestion. The first client appears in the cvar box but nothing ever appears in the tbl.client box. I know there are more clients that fit the query filter but it is not getting to them. Just running a regular query using the same filter criteria, there should be 9 clients.

  2. #32
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Stan, I just noticed if I put "cvar" in one box and then "A_tbl" in the next box down, as it steps through, when it gets to "A_tbl = table.open("New_Invoicing")", an error pops up stating "New_Invoicing" can't be opened because it's already being used by another process. I assume it's where we already opened it earlier in the script.

  3. #33
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Not sure what happened but it's not doing that now. Now it runs through however with "A_tbl" entered in the box, no value appears as it runs through. For whatever reason, it's not opening another instance of the New_Invoicing table.

  4. #34
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Now it runs through however with "A_tbl" entered in the box
    Not a_tbl, just tbl.client.

    If cvar acquires a value it must come from =tbl.client.

    Can you attach a sample of the table?
    There can be only one.

  5. #35
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Haven't done that before..... how do I attach the table?

  6. #36
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Create a zip of the table in Alpha.
    Start a post here, scroll down till you see Go Advanced at the lower right.
    Screen changes, scroll down till you see Manage Attachments, ......................
    There can be only one.

  7. #37
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    How do I attach the table? Just saw your answer.....

  8. #38
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    OK.... here is the table...... Will be gone until next Tuesday, but may be able to work from home this weekend. Thanks for everything.....
    Attached Files Attached Files

  9. #39
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Creating Multiple Excel Worksheets from one export Script

    My bad.

    1. We can't use batch_begin()..batch_end() because those don't allow the table to accessed by another pointer.
    2. typo in the export portion of the script was querying the original table, not the second instance, thus the end of file was hit.

    Code:
    dim tbl as P
    dim cvar as C
    'open the New_Invoicing table to get client id numbers
    tbl = table.open("New_Invoicing")
    'put table in client order so we can skip over multiple instances and export only once per client
    tbl.order("client")
    'start at the first client id found
    tbl.fetch_first()
    WHILE .NOT. tbl.Fetch_EOF()
    	'read theclient id of the current record and store that to the cvar variable
    	cvar = alltrim(tbl.client) 'added to remove spaces in client id
    	'open a second instance of the table for the exporting source
    	a_tbl = table.open("New_Invoicing")
    	'set options and query for the client id obtained above
    	query.description = ""
    	query.order = "CLIENT+DATE"
    	query.filter = "CLIENT = "+quote(cvar)+" .AND. INVOICE_AMOUNT1-CHECK1-CHECK2-CHECK3-CHECK4>0"
    	query.options = "I"
    	a_tbl.query_create() ' not tbl.query_create()
    	'the a_tbl pointer now only sees the records queried
    	export.type = 4
    	export.names = .T.
    	export.file = "c:\temp\("+cvar+").xls"
    	export.options = ""
    	export.field_sep = ""
    	export.record_sep = ""
    	export.fields = 8
    	export.field1 = "client"
    	export.field2 = "date"
    	export.field3 = "invoice_number1"
    	export.field4 = "invoice_amount1"
    	export.field5 = "date6"
    	export.field6 = "invoice_number2"
    	export.field7 = "invoice_amount2"
    	export.field8 = "check5"
    	a_tbl.export()
    	'we've exported the records so now close the exporting source instance
        a_tbl.close()
        'in the original instance of the table,skip over any more records with the same client id 
    	while tbl.client = cvar .and. .not. tbl.fetch_eof()
    	tbl.fetch_Next()
    	end while
    	'we're now at the next unique client id, when we get back to the top of this while loop
    	'the cvar = tbl.client will capture the client id and use that for the next export
    	'etcetera
    END WHILE
    tbl.close()
    Last edited by Stan Mathews; 01-30-2015 at 10:22 AM.
    There can be only one.

  10. #40
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Thanks Stan. I had no idea that the 2 "batches" would lock the table so to speak. Thank you for all your hard work on this. I was definitely at a loss.

  11. #41
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Creating Multiple Excel Worksheets from one export Script

    All Change and Enter operations in the specified table pointer (<TBL>) that occur between the <TBL>.BATCH_BEGIN()and its corresponding <TBL>.BATCH_END(), are optimized.

    Alpha Five optimizes data entry operations by either opening the table exclusively or by preventing write access to the file by other users.
    So it probably wouldn't have helped anyway. An export doesn't involve changes or enters.
    There can be only one.

  12. #42
    "Certified" Alphaholic Tom Henkel's Avatar
    Real Name
    Tom Henkel
    Join Date
    May 2002
    Location
    New Jersey, USA
    Posts
    1,858

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Stan,

    I would never accuse you of Sniping. I would never claim that my code was the cleanest, or even very good. I just know that it works for us.

    Tom

  13. #43
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Creating Multiple Excel Worksheets from one export Script

    I just know that it works for us.
    And that's what it's all about. Wanted to make sure you knew none of my comments/additions were critical.
    There can be only one.

  14. #44
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Stan. I just got back into the office today. Tried running the script and it worked flawlessly. Thank you so much for the insight, help and sharing your knowledge. This Saturday I will be coming into the office to upgrade to v11. I will be opening all scripts, forms and operations in design or edit mode and then saving them. Wish me luck....lol. Thanks again!
    Scott

  15. #45
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,623

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Quote Originally Posted by draxx2755 View Post
    Stan. I just got back into the office today. Tried running the script and it worked flawlessly. Thank you so much for the insight, help and sharing your knowledge. This Saturday I will be coming into the office to upgrade to v11. I will be opening all scripts, forms and operations in design or edit mode and then saving them. Wish me luck....lol. Thanks again!
    Scott
    Try it with a backup copy first...
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  16. #46
    Member
    Real Name
    Scott Naples
    Join Date
    Mar 2006
    Posts
    132

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Absolutely.....

Similar Threads

  1. How can I export SQL data to multiple tabs on an Excel spreadsheet?
    By Jay Talbott in forum Mobile & Browser Applications
    Replies: 6
    Last Post: 07-09-2014, 01:21 PM
  2. Problem with Importing Excel Worksheets
    By swksys in forum Application Server Version 11 - Web/Browser Applications
    Replies: 1
    Last Post: 01-18-2012, 12:34 AM
  3. Export to Excel - Export Hidden Fields not working
    By iviowa in forum Application Server Version 10 - Web/Browser Applications
    Replies: 5
    Last Post: 02-21-2011, 04:35 PM
  4. Alpha5 and MS Excel with Multiple Worksheets
    By ppham in forum Alpha Five Version 8
    Replies: 13
    Last Post: 01-15-2008, 11:12 AM
  5. Export to Excel action script fails
    By Dave Wilson in forum Alpha Five Version 6
    Replies: 1
    Last Post: 08-05-2005, 02:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •