Alpha Video Training
Results 1 to 14 of 14

Thread: Alpha5 and MS Excel with Multiple Worksheets

  1. #1
    Member
    Real Name
    Philippe Pham
    Join Date
    Oct 2006
    Posts
    30

    Default Alpha5 and MS Excel with Multiple Worksheets

    I wonder if the V8 can be able to export data to a single Excel file with multiple worksheets. I currently have V5, V6 and V7. Neither of them can do this.

    This is very important because our customers just want all the data in one file instead of multiple files. In order to do that now, I have to do extra work by combining the data from multiple files together.

    Anyone has the information, please help.

    thanks.

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

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    No, V8 does not natively create multiple worksheets in an excel file. You can use Alpha's ole methods to do this (all the way back to v5).

  3. #3
    Member
    Real Name
    Doug Page
    Join Date
    Jan 2002
    Location
    Vancouver, BC Canada
    Posts
    963

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    Not natively. Look up info in Alpha docs and Excel docs on OLE (Object Linking and Embedding). You will have to program this for it to work.

  4. #4
    Member
    Real Name
    Philippe Pham
    Join Date
    Oct 2006
    Posts
    30

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    Thanks, Doug and Stan.

    what language should I use to program the OLE?

  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: Alpha5 and MS Excel with Multiple Worksheets

    Quote Originally Posted by ppham View Post
    Thanks, Doug and Stan.

    what language should I use to program the OLE?
    Xbasic.

    Example attached. One form, one button. Button executes function taking parameter from variable on form. Function writes records from two tables to separate sheets of excel workbook, deletes third sheet. Saves exported file as name in form variable. (Doesn't check for name legality so that's the user's problem.)

    The example is a bit slow because I included print setup commands. Removing those lines will speed the process up greatly.

  6. #6
    Member
    Real Name
    Philippe Pham
    Join Date
    Oct 2006
    Posts
    30

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    You're so responsive, Stan. Thank you so much. I will try the sample.

  7. #7
    Member
    Real Name
    Philippe Pham
    Join Date
    Oct 2006
    Posts
    30

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    Hi Stan,
    It looks like the OLE only allows to create 3 worksheets per Excel file. I tried to create the 4th one, and I got the following messages:


    ERROR: command: xlApp.Sheets(4).Activate()
    OLE Automation Error. COM error source is unknown. COM error description is unknown.
    scode = -2147352565 (0x8002000b). Formated System Message:
    An attempt was made to load a program with an incorrect format.


    Please advise!

    Thank you.

  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: Alpha5 and MS Excel with Multiple Worksheets

    When you create a spreadsheet with the ole method the default is 3, set in Excel I think. If you need more just stick in some

    xlApp.Workbooks.Add()

    lines to create them, probably before anything after the create. That way when you start referencing them they should be in order.

  9. #9
    Member
    Real Name
    Philippe Pham
    Join Date
    Oct 2006
    Posts
    30

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    Thanks, Stan.

    But that line is already added at the begining of the function.
    if alltrim(filename) = ""
    ui_msg_box("Sorry","No filename given for saving.")
    exprt = .F.
    end
    end if

    Dim xlApp as p
    Dim myCell as c
    xlApp = ole.Create("Excel.Application")
    xlApp.Workbooks.Add() 'adds a workbook object
    xlApp.DisplayAlerts = .F.


    Do you mean adding another line somewhere in the function?

  10. #10
    Member
    Real Name
    Doug Page
    Join Date
    Jan 2002
    Location
    Vancouver, BC Canada
    Posts
    963

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    Not that I actually know, but wouldn't it be:

    Worksheets.Add()

  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: Alpha5 and MS Excel with Multiple Worksheets

    Good catch, Doug. I was posting from memory which is always risky. I'll have to test and get back but I think it would be

    xlapp.Worksheets.Add()

    and you can

    xlapp.Sheets(3).Select()'select sheet 3
    xlapp.Sheets.Add()'adds a sheet before 3, can't add after 3 this way
    xlapp.Sheets(4).Select()'select sheet 4
    xlapp.Sheets(4).Move(3)'move it after 3

    but I'll have to test that tomorrow.

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

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    Testing this morning tells me the move statements above aren't right and it is just easier to refer to the sheets by index (absolute position) and rename them.

    xlApp.Worksheets.Add()
    and
    xlApp.Sheets.Add()

    seem to be equivalent.

    Reference to sheets by index is

    xlApp.Sheets(1.Select() 'leftmost sheet
    xlApp.Sheets(2).Select() 'second to leftmost
    etc

  13. #13
    Member
    Real Name
    Philippe Pham
    Join Date
    Oct 2006
    Posts
    30

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    Stan and Doug,
    I think I found a way to add more than 3 worksheets in an excel file. I just modified your code a little bit, Stan. And I got 5 worksheet created in the excel file.

    I attach the file here for your reference.

    thanks a lot for your help.

  14. #14
    Member
    Real Name
    Philippe Pham
    Join Date
    Oct 2006
    Posts
    30

    Default Re: Alpha5 and MS Excel with Multiple Worksheets

    Here is the file. The order of the worksheets is not important as long as I get the number of worksheets I want.

    thanks again.

Similar Threads

  1. Alpha5, vers 8 & Alpha5, version 7
    By dchiass in forum Alpha Five Version 8
    Replies: 2
    Last Post: 04-27-2007, 10:16 AM
  2. Can't append excel to alpha with NEW excel?
    By Robert Rough in forum Alpha Five Version 7
    Replies: 5
    Last Post: 02-22-2007, 01:54 PM
  3. Need help Importing files (Excel)into Alpha5
    By vaxennet in forum Alpha Five Version 6
    Replies: 16
    Last Post: 02-14-2006, 11:04 AM
  4. Alpha5 for Win95 to Alpha5 ver 5
    By Bill S. in forum Alpha Five Version 5
    Replies: 6
    Last Post: 06-25-2004, 04:50 PM
  5. Run Excel Macro from Alpha5
    By crimmelcp in forum Alpha Five Version 4
    Replies: 1
    Last Post: 05-31-2000, 01:22 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
  •