Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 46

Thread: Creating Multiple Excel Worksheets from one export Script

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

    Default Creating Multiple Excel Worksheets from one export Script

    Is it possible to create multiple worksheets in Excel from one Export Script in A5? In A5 reports, you can break the information according to a field specified within a table. For example you might make the break on records by a client name, thereby creating a new report for each client. We need to do this on an export operation from within a script. I've created a query for the necessary information and then an export. I know I can have it loop through the export operation for the records but is it possible to tell A5 to create a new excel file for each Client in the query, basically grouping the queried info for each client in a different Excel worksheet?

    To be honest I've never tried anything like this and am at a loss. I could not find anything in the message board regarding this type of Export. Thanks for reading the post and any or all ideas are welcome.

    Scott

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Looks like were in the same boat... I was able to use a5_AlphaDAO_Export() to send data from multiple tables to an Excel worksheet. Works great, each table has its own sheet. In the genie you can set arguments to prompt for the value for the filter to use. But now I want to add more tables from a different database (tables have the same names) and it looks like the only option is to create separate xls files and then merge them into new sheets. According to Excel all the files must be in the same folder. I was hoping there was a way to edit the Xbasic the genie creates so I can use the same script and just change the file name. But the genie only creates a new file or uses an existing one. So I will add my question to yours - how do you do that using Xbasic?

    Oops, I see you are using v5 - I am on v11. Perhaps someone will be able to help us both...?
    Last edited by MoGrace; 01-22-2015 at 07:13 PM.
    Robin

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

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    We will be upgrading to V11 in 2 weeks. I had some major problems in November and had to rebuild the db. I've just been making sure it is now running smoothly with no major glitches, but will be upgrading in 2 weeks.

  4. #4
    "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

    We're using ver 10.5, and we have a function to grab data from a specified sheet in a workbook. I don't see why the reverse couldn't be done. Maybe I'm oversimplifying it, but where I assign an array variable to an excel cell, I don't see why you couldn't just reverse the equation. You would need to have a "save" for the workbook, but I think it could be done.

    Code:
    'Date Created: 17-Jul-2013 02:57:48 PM
    'Last Updated: 14-Nov-2014 10:11:10 AM
    'Created By  : Thomas Henkel
    'Updated By  : Stephen Andrews
    ' Variables passed:
    ' Filename = Name of excel workbook
    ' First row is numeric = first row to select  - usually 1 unless you have header rows
    ' First col is numeric, usually 1
    ' Data_Array is the pointer to the array from the calling script
    ' Sheet is numeric value of which sheet in the workbook to select
    Function get_excel_data as V (Filename as C, First_Row as N, First_Col as N, Data_Array as P, Sheet as N)
        dim MyObject as P
        dim row as N
        dim col as N
        dim rows as N
        dim cols as N
        dim cell as C
        dim temp as C
        cols = Data_Array.size(2)
        rows = Data_Array.size(1)
        on error goto quitting
        MyObject = ole.getobject("","Excel.Application")
    	MyObject.Workbooks.Open(Filename)
       MyObject.Worksheets(Sheet).Select()    'This was commented out.  Steve uncommented it on 11/14/14.
        for row = First_Row to  rows
            for col = First_Col to cols
                cell = get_col(col) + row
                Data_Array[row,col] = MyObject.activesheet.Cells.Range(cell)
            next col
        next row
    quitting:
    
    '  Somehow you would need to put a "save" here
    
        MyObject.Workbooks.close()
        delete MyObject
    end function
    Tom

  5. #5
    "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

    ' Somehow you would need to put a "save" here
    MYObject.ActiveWorkbook.SaveAs("drive_path_name_extension_in_quotes",-4143)
    MyObject.Workbooks.close()
    MyObject.Quit()


    -4143 is latest version in Excel 2003
    would use
    70 for Excel 97-2003 in Excel 2013, etc.
    6 is csv.

    .getobject() is Alpha V6 and later. I think V5 would be

    MyObject = ole.create("Excel.Application")
    Last edited by Stan Mathews; 01-23-2015 at 04:20 PM.
    There can be only one.

  6. #6
    "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

    Tom,

    I certainly don't mean to be sniping at your post. Just want to add that it uses a function get_col() to translate the column index (numeric) to a letter. I do something similar and in the absence of your translation function I offer.

    Code:
    FUNCTION get_col AS C (colnum AS N )
    	'returns a character column reference from its numeric index
      If Colnum > 26 Then
        get_col = Chr(Int((Colnum - 1) / 26) + 64) + Chr((mod((Colnum - 1),26) ) + 65)
      Else
        get_col = Chr(Colnum + 64)
      End If
    END FUNCTION
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Stan, just out of curiosity, do you think it would be possible to use a variable calling on the "Client" field so that on this line,"MYObject.ActiveWorkbook.SaveAs("drive_path_name_extension_in_quotes",-4143)" the variable could be used where the name would normally appear in the path? Hopefully, that way with a loop, it might possibly save each workbook to the "client" name. Just wondering if you think this may be viable or am I over simplifying my thinking?
    Thanks....Scott

  8. #8
    "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

    Do it all the time. Just had the hard-coded expression there to illustrate what is needed.

    Be careful to construct a legal filename.

    cust = "ABC Co."
    file_name = "C:\tempfiles\\"+cust+"_wk_ending_"+date()+".xls"
    ? file_name
    = "C:\tempfiles\ABC Co._wk_ending_01/26/2015.xls"


    Looks ok but forward slashes aren't legal. You'll want something like

    file_name = "C:\tempfiles\\"+cust+_"wk_ending_"+dtoc(date(),"6-")+".xls"
    ? file_name
    = "C:\tempfiles\ABC Co._wk_ending_01-26-15.xls"
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Thanks Stan.... I appreciate all your help.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    I'm having a major brain fade here. The script below works..... sort of. It will go to the first record that satisfies the filter criteria and creates the Excel file just fine. Unfortunately, it stops at the first record that satisfies the criteria. I know I'm missing something simple to tell it to run the Query and continue to the next client that satisfies the query, and so on. What am I missing?

    dim tbl as P
    dim cvar as C


    tbl = table.open("New_Invoicing")

    cvar = (New_Invoicing->Client)

    'debug(1)



    query.description = ""
    query.order = "CLIENT+DATE"
    query.filter = "CLIENT = (cvar) .AND. INVOICE_AMOUNT1-CHECK1-CHECK2-CHECK3-CHECK4>0"
    query.options = "I"
    tbl.query_create()

    WHILE .NOT. tbl.Fetch_EOF()

    tbl.batch_Begin()


    export.type = 4
    export.names = .T.
    export.file = "\\amesrv\E\("+cvar+").xls"
    export.options = ""
    export.field_sep = ""
    export.record_sep = ""
    export.fields = 8
    export.field1 = "new_invoicing->client"
    export.field2 = "new_invoicing->date"
    export.field3 = "new_invoicing->invoice_number1"
    export.field4 = "new_invoicing->invoice_amount1"
    export.field5 = "new_invoicing->date6"
    export.field6 = "new_invoicing->invoice_number2"
    export.field7 = "new_invoicing->invoice_amount2"
    export.field8 = "new_invoicing->check5"

    tbl.export()

    tbl.fetch_Next()

    END WHILE

    tbl.batch_end()

    SCRATCH THAT... Here is the correct script I'm using:

    dim tbl as P
    dim cvar as C


    tbl = table.open("New_Invoicing")

    cvar = (New_Invoicing->Client)

    'debug(1)

    WHILE .NOT. tbl.Fetch_EOF()

    tbl.batch_Begin()



    query.description = ""
    query.order = "CLIENT+DATE"
    query.filter = "CLIENT = (cvar) .AND. INVOICE_AMOUNT1-CHECK1-CHECK2-CHECK3-CHECK4>0"
    query.options = "I"
    tbl.query_create()




    export.type = 4
    export.names = .T.
    export.file = "\\amesrv\E\("+cvar+").xls"
    export.options = ""
    export.field_sep = ""
    export.record_sep = ""
    export.fields = 8
    export.field1 = "new_invoicing->client"
    export.field2 = "new_invoicing->date"
    export.field3 = "new_invoicing->invoice_number1"
    export.field4 = "new_invoicing->invoice_amount1"
    export.field5 = "new_invoicing->date6"
    export.field6 = "new_invoicing->invoice_number2"
    export.field7 = "new_invoicing->invoice_amount2"
    export.field8 = "new_invoicing->check5"

    tbl.export()

    tbl.fetch_Next()

    END WHILE

    tbl.batch_end()

  11. #11
    "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

    You're not changing the cvar contents. Probably want something like......

    Code:
    dim tbl as P
    dim cvar as C
    tbl = table.open("New_Invoicing")
    tbl.order("client")'put table in client order so we can skip over multiple instances and export only once per client
    tbl.fetch_first()
    tbl.batch_Begin()
    WHILE .NOT. tbl.Fetch_EOF()
    	cvar = tbl.client
    	query.description = ""
    	query.order = "CLIENT+DATE"
    	query.filter = "CLIENT = (cvar) .AND. INVOICE_AMOUNT1-CHECK1-CHECK2-CHECK3-CHECK4>0"
    	query.options = "I"
    	tbl.query_create()
    	export.type = 4
    	export.names = .T.
    	export.file = "\\amesrv\E\("+cvar+").xls"
    	export.options = ""
    	export.field_sep = ""
    	export.record_sep = ""
    	export.fields = 8
    	export.field1 = "new_invoicing->client"
    	export.field2 = "new_invoicing->date"
    	export.field3 = "new_invoicing->invoice_number1"
    	export.field4 = "new_invoicing->invoice_amount1"
    	export.field5 = "new_invoicing->date6"
    	export.field6 = "new_invoicing->invoice_number2"
    	export.field7 = "new_invoicing->invoice_amount2"
    	export.field8 = "new_invoicing->check5"
    	tbl.export()
    	while tbl.client = cvar .and. .not. tbl.fetch_eof()
    	tbl.fetch_Next()
    	end while
    END WHILE
    tbl.batch_end()
    tbl.close()
    Last edited by Stan Mathews; 01-26-2015 at 06:50 PM.
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Thanks Stan. Works except it is still only creating one excel file for the first record. Doesn't seem to be fetching the next record according to the "cvar" call out and creating an excel file for it or the following records. I see what you meant about not changing the "cvar" contents.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Perhaps after the query is created, that's when I should be fetching the next record instead of fetching the next record and running the query each time. What do you think?

  14. #14
    "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

    On my tablet at the moment. Will take another look shortly.
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    No prob..... Thanks and I'm just spit balling here. I thought maybe we should be fetching the next records for the Export and only run the Query once.... if that makes any sense.

  16. #16
    "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

    Don't think so. We have to

    order by client
    get the client identifier
    query for that client
    export
    skip over that client's additional records
    get the next client
    repeat
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    OK......

  18. #18
    "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

    Filter doesn't look right. Try it as

    query.filter = "CLIENT = "+quote(cvar)+" .AND. INVOICE_AMOUNT1-CHECK1-CHECK2-CHECK3-CHECK4>0"
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    OK, I'll give that a try.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Result is the same... Exports first record only. Same result with the filter before changed. I need to leave for the day but will be back on this either tomorrow or Thursday. Thanks again for all your help.

  21. #21
    "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

    This works fine in AlphaSports. It seems the same process as you're using. Exports multiple dates and creates a file for each.

    Code:
    tbl = table.open("invoice_header")
    tbl.order("date")
    tbl.fetch_first()
    
    while .not. tbl.fetch_eof()
    	dt = tbl.date
    	export_filename = "c:\temp\("+cdate(dt)+").asc"
    	a_tbl = table.open("Invoice_Header")
    	ON ERROR GOTO ERROR2601201519050578
    	
    	
    	query.order = "recno()"
    	query.filter = "cdate(Date)  = "+quote(cdate(dt))
    	query.options = "I"
    	query.Description = "Temporary Query"
    	i_indx = a_tbl.query_create()
    	
    	export.type = 0
    	export.names = .T.
    	export.file = export_filename
    	export.options = ""
    	export.field_sep = ","
    	export.record_sep = "<CR><LF>"
    	export.fields = 10
    	export.field1 = "invoice_number"
    	export.field2 = "customer_id"
    	export.field3 = "date"
    	export.field4 = "sales_rep"
    	export.field5 = "delivery_by"
    	export.field6 = "pay_method"
    	export.field7 = "sales_tax"
    	export.field8 = "discount"
    	export.field9 = "cc_number"
    	export.field10 = "cc_expiration"
    	
    	a_tbl.export()
    	
    	
    	GOTO CONTINUE2601201519050578
    	ERROR2601201519050578:
    	ON ERROR GOTO 0
    	ui_msg_box("Error","Error running Export Operation"+crlf()+error_text_get())
    	END
    	CONTINUE2601201519050578:
    	a_tbl.close()
    	while .not. tbl.fetch_eof() .and. tbl.date = dt
    		tbl.fetch_next()
    	end while
    end while
    tbl.close()
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Thanks Stan. I'll give it a try as soon as I'm back in the office.

  23. #23
    "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

    Found the problem. Since we were querying the table pointer for a single client the .fetch_eof() was satisfied.
    we need a second table instance and second pointer for the export/query.

    Code:
    dim tbl as P
    dim cvar as C
    tbl = table.open("New_Invoicing")
    tbl.order("client")'put table in client order so we can skip over multiple instances and export only once per client
    tbl.fetch_first()
    tbl.batch_Begin()
    WHILE .NOT. tbl.Fetch_EOF()
    	cvar = tbl.client
    	a_tbl = table.open("New_Invoicing")
    	query.description = ""
    	query.order = "CLIENT+DATE"
    	query.filter = "CLIENT = (cvar) .AND. INVOICE_AMOUNT1-CHECK1-CHECK2-CHECK3-CHECK4>0"
    	query.options = "I"
    	tbl.query_create()
    	export.type = 4
    	export.names = .T.
    	export.file = "\\amesrv\E\("+cvar+").xls"
    	export.options = ""
    	export.field_sep = ""
    	export.record_sep = ""
    	export.fields = 8
    	export.field1 = "new_invoicing->client"
    	export.field2 = "new_invoicing->date"
    	export.field3 = "new_invoicing->invoice_number1"
    	export.field4 = "new_invoicing->invoice_amount1"
    	export.field5 = "new_invoicing->date6"
    	export.field6 = "new_invoicing->invoice_number2"
    	export.field7 = "new_invoicing->invoice_amount2"
    	export.field8 = "new_invoicing->check5"
    	a_tbl.export()
            a_tbl.close()
    	while tbl.client = cvar .and. .not. tbl.fetch_eof()
    	tbl.fetch_Next()
    	end while
    END WHILE
    tbl.batch_end()
    tbl.close()
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Thanks Stan. That makes sense. I'll hop on it Thursday morning when I get back in the office. So the "A_table" reference is the second instance opened of the original table. I doubt if I would have caught that. Actually I didn't realize that a second instance would need to be opened. Thanks for catching that. And btw, this Saturday I'll be upgrading to V11.
    Last edited by draxx2755; 01-27-2015 at 02:32 PM.

  25. #25
    "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

    Here's a commented version of the code. I also removed the New_Invoicing-> which is not necessary. Since you're only dealing with the one table and you specify the table with a_tbl = table.open("New_Invoicing") you don't need to specify it again when naming the fields.

    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()
    tbl.batch_Begin()
    WHILE .NOT. tbl.Fetch_EOF()
    	'read theclient id of the current record and store that to the cvar variable
    	cvar = tbl.client
    	'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"
    	tbl.query_create()
    	'the a_tbl pointer now only sees the records queried
    	export.type = 4
    	export.names = .T.
    	export.file = "\\amesrv\E\("+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.batch_end()
    tbl.close()
    Last edited by Stan Mathews; 01-27-2015 at 06:55 PM.
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Thank you Stan. It's just force of habit for me to make surei reference the table with the field. I'll tuck your advice away for future reference. Thanks.....

  27. #27
    "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

    When you switch over to V11 we can tidy things up a bit.

    Code:
    dim a_tbl as P
    dim cvar as C
    'get a crlf() delimited list of one instance each of the client field in client order
    client_list = table.external_record_content_get("New_Invoicing","alltrim(client)","client",".T. .and. unique_key_value()")
    'process the list, one entry at a time
    for each entry in client_list
    	'the entry variable .value property holds the client "id"
    	cvar = entry.value
    	'open the New_Invoicing table for exporting
    	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"
    	tbl.query_create()
    	'the a_tbl pointer now only sees the records queried
    	export.type = 4
    	export.names = .T.
    	export.file = "\\amesrv\E\("+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
    	a_tbl.close()
    next
    There can be only one.

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

    Default Re: Creating Multiple Excel Worksheets from one export Script

    Hi Stan, I tried the "commented" code for v5 but it's still giving me only one export file, named with the first record that satisfies the criteria, but in that file, all records in the table are showing up, as if it's not seeing the query criteria. I put in a "debug" just prior to the running of the query to follow what it's doing and it goes through the query ok then goes through the export and through the next couple of code lines but when it gets to "tbl.fetch_next()" it goes from there to "end while", back up to "tbl.fetch_next()", back down to "end while" and then onto the final "end while" and then to "tbl.batch_end()", "tbl.close()" and then stops. It obviously has finished, but it is not fetching through the queried records over and over through all of the queried "client" results. Hope this makes sense to you as far as what it is doing.

  29. #29
    "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 understand what you're saying but not why it would be doing that.

    As you step through in the debugger, the code should execute as you outlined until it gets to the final end while. Then it should jump back to the top and hit

    'read the client id of the current record and store that to the cvar variable
    cvar = tbl.client

    and start over.

    That is unless there is only one client and the end of file is fetched.

    Run it with the debug() in place and when the debugger appears in the bottom section of the debugger window where you see the expression and value boxes, type cvar in the top expression box. Arrow down to the second expression box and type in tbl.client.

    Step through the script and you should see the variable's value appear and the value in the current tbl.client field appear as the records are processed. The first value appearing in each should be the "smallest" client and it should increase as the records process. Maybe you'll see something I'm not.
    There can be only one.

  30. #30
    "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

    If your client field is numeric you need (no quote())

    query.filter = "CLIENT = "+cvar+" .AND. INVOICE_AMOUNT1-CHECK1-CHECK2-CHECK3-CHECK4>0"
    There can be only one.

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
  •