Alpha Video Training
Results 1 to 15 of 15

Thread: Export 2 Excel

  1. #1
    "Certified" Alphaholic
    Real Name
    Charlie Crimmel
    Join Date
    Apr 2000
    Location
    West Virginia
    Posts
    1,695

    Default Export 2 Excel

    (From control panel,table and sets,double click on a table and click excel button at top)

    Seems like the Alpha Export to excel is still using the Excel 4.0 (Win 98 limitations.
    Limit of 16,350 lines.
    From excel 2000 and windows 2000 the excel limit is 65,000 lines.

    Any chance of a work around or patch so that the Alpha5v7 function would allow more that 16,000 lines???

    Charlie Crimmel

  2. #2
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,952

    Default Re: Export 2 Excel

    will be easier to copy and paste the dbf, then "import" it using excel - but there is still the record limit.
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  3. #3
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,620

    Default Re: Export 2 Excel

    1. A5 Export to a csv with fieldnames......

    2. Excel open a dbf, save as a .xls
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

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

    Default Re: Export 2 Excel

    Quote Originally Posted by Al Buchholz View Post
    1. A5 Export to a csv with fieldnames......

    2. Excel open a dbf, save as a .xls
    Open excel, file menu, open, change "Files of type:" to "dBase files (*.dbf)", navigate to table, open, save as excel.

    I can also create a function to do the same if you want to automate the process.

  5. #5
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,952

    Default Re: Export 2 Excel

    stan, can you can do it using the users excel record limit (like 65000 for 2000 thru 2003) ?

    would be nice.
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  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: Export 2 Excel

    Quote Originally Posted by martinwcole View Post
    stan, can you can do it using the users excel record limit (like 65000 for 2000 thru 2003) ?

    would be nice.
    I'll have to take a look at the choices available. Relying on my, hah, memory saving as excel 4.0 you specify the save as parameter as 33 as in

    xlApp.ActiveWorkbook.SaveAs(newname,33)

    resave as a .dbf

    xlApp.ActiveWorkbook.SaveAs(newname,11)

    save as an excel 2003

    xlApp.ActiveWorkbook.SaveAs(newname,-4163) 'or similar

    I'll have to play with the available choices and see how they are accepted/implemented. I suppose the function could ask what type of excel file to save as and at least try the chosen type.

  7. #7
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,952

    Default Re: Export 2 Excel

    Stan, if its still there, I put one in there for saving and importing an excel and for exporting to excel using the old "33" or excel 4.0 version using early version 5

    haven't messed with it since. I wonder if we will be able to write to excel, in effect exporting, using the features of V8?
    Cole Custom Programming - Terrell, Texas
    972 524 8714
    martin_w_cole@msn.com

    ____________________
    "A young man who is not liberal has no heart, but an old man who is not conservative has no mind." GB Shaw

  8. #8
    Member
    Real Name
    Garry M Flanigan
    Join Date
    Jul 2004
    Location
    Melbourne, Australia
    Posts
    909

    Default Re: Export 2 Excel

    Has this problem been reported as a bug and if so has Alpha committed to fixing it? Yes there are workarounds but I think this should just work without the very small 16350 records limit.

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

    Default Re: Export 2 Excel

    Quote Originally Posted by Garry Flanigan View Post
    Has this problem been reported as a bug and if so has Alpha committed to fixing it? Yes there are workarounds but I think this should just work without the very small 16350 records limit.
    I don't think it is a bug. The export genie prettly clearly indicates you can export to excel version 3 or 4. The record limit is imposed by that excel version as that is as many rows as they permitted, unless I'm mistaken.

    To export to a later version supporting more rows would likely require Alpha to purchase some sort of license.

  10. #10
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Export 2 Excel

    At the risk of getting bogged down in semantics, the row limit on the export sounds more like an item for the Feature Wishlist forum than a bug.

  11. #11
    Member
    Real Name
    Garry M Flanigan
    Join Date
    Jul 2004
    Location
    Melbourne, Australia
    Posts
    909

    Default Re: Export 2 Excel

    Stan,
    You are right that it is not a bug, however to not support exporting to Excel formats other than version3 and 4 is either a serious oversight or if deliberate requires some explanation by Alpha. Excel 97 can have 65536 rows and is probably the oldest version used by most folks.

  12. #12
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Export 2 Excel

    Garry,

    I see only one request in the Features Wishlist forum, from several years ago, wanting to extend the export functionality to more recent versions of Excel. Doesn't sound like the natives are clamoring for it. Perhaps the explanation for not supporting more recent versions of Excel is: (a) lack of demand in the A5 community; or (b) relatively easy work arounds, using import routines in Excel. Might just be a question of priorities, if you see what I mean. While I'm speculating here there might even be a bit of database bias at work. Some would argue that sheets with more than 16,000 rows probably belong in databases, anyway.

    I suggest you post your idea in a new topic over in the Features Wishlist forum.

    -- tom

  13. #13
    Member
    Real Name
    Garry M Flanigan
    Join Date
    Jul 2004
    Location
    Melbourne, Australia
    Posts
    909

    Default Re: Export 2 Excel

    To assist with this problem I have built the function as follows:

    Code:
    FUNCTION ConvertXL AS L (SourceXLfname AS C, OutTextFname AS C, OutputType as c = "Text" )
    ' this function converts an Excel file to desired format
    ' SourceXLfname is the full input filename
    ' OutTextFname is the full output filename
    ' OutputType describes the output type
    '    "Text" is tab separated values 
    '    "CSV" is comma separated values with inverted comma's as text qualifiers
    '    "DBF4" is dbase version 4
    '    "Excel" is "Current Platform Text" whatever that means 
    
    dim nFileType as n
    
    select
        case OutputType = "Text"
    	    nFileType = 21
        case OutputType = "CSV"
    	    nFileType = 6
        case OutputType = "DBF4"
    	    nFileType = 11
        case OutputType = "Excel"
    	    nFileType = -4158
    	case else
    		nFileType = 0
    end select 
    
    if nFileType = 0
    	ui_msg_box("ConvertXL","Export type not recognised")
    	ConvertXL = .f.
    	end
    end if
     
    Dim xlApp as p 
    xlApp = ole.create("Excel.Application")
    xlApp.Workbooks.Open(SourceXLfname) 
    'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
    xlapp.DisplayAlerts = .F.
    xlApp.ActiveWorkbook.SaveAs(OutTextFname,nFileType) '33 is a constant for Excel 4.0, dbf4 is 11, tab delimited text is 21, 
    xlApp.Workbooks.Close()
    xlapp.DisplayAlerts = .T.
    xlApp.Quit()
     
    delete xlApp
    
    ConvertXL = .t.
    
    END FUNCTION
    If you do an export from Alpha as a text file, tab delimited, pass this function the text filename and the desired Excel filename. See sample usage as follows:

    ?ConvertXL("c:\temp\export.txt", "c:\temp\export.xls", "Excel" )

    This allows handling files up to 65536 rows.

    I built this function some time ago to assist with imports, that is, converting Excel files to dbf which gets away from a lot of the type issues associated with Excel.

  14. #14
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default Re: Export 2 Excel

    Thanks, Garry!

    Suggest you re-post in the Code Archive forum, since it might be of general use to others down the line. -- tom

  15. #15
    Member
    Real Name
    Garry M Flanigan
    Join Date
    Jul 2004
    Location
    Melbourne, Australia
    Posts
    909

    Default Re: Export 2 Excel

    Tom,
    I have now put ConvertXL function in the code archive with some extra comments as per your suggestion.

Similar Threads

  1. Export to Excel
    By gagsoft in forum Alpha Five Version 6
    Replies: 7
    Last Post: 04-01-2006, 07:28 PM
  2. Export to Excel
    By Bob Whitaker in forum Alpha Five Version 5
    Replies: 0
    Last Post: 07-14-2005, 12:53 AM
  3. Export to Excel XP
    By robert cook in forum Alpha Five Version 5
    Replies: 1
    Last Post: 11-12-2004, 01:09 PM
  4. Export to Excel
    By Mike Reed in forum Alpha Five Version 1
    Replies: 3
    Last Post: 07-14-2004, 02:27 PM
  5. Export to Excel
    By Craig Johnson in forum Alpha Five Version 5
    Replies: 2
    Last Post: 04-23-2003, 06:19 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
  •