Alpha Video Training
Results 1 to 6 of 6

Thread: Preparing a spreadsheet to be imported

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

    Default Preparing a spreadsheet to be imported

    When I began experminenting with the ole automation samples provided with A5V5 I lamented that the "read from" capability available in V6 (see the section in the .chm "Reading an Excel Spreadsheet with OLE")
    was not available to me. I wanted to read through an excel spreadsheet, excerpting certain portions and saving the result as an Alpha import compatible excel version. I found the limitations of ole in V5 can be circumvented.

    The attached script processes a spreadsheet report that is available in my company. The original of the report contains data pertaining to three sales divisions. I was asked to take the data for one division and make it available in my in-house application. This is easily accomplished if I manually strip out the data I want, title the columns as needed to fit a predefined import operation, then save the file as Excel ver 3/4, then run the import. The problem with that scenario is that I must perform all these steps once a week. I was determined to automate the whole process.

    Using the code snippets others have posted on the board attributed to John Magno - http://www.JohnMagno.com, I came up with the attached script. It is not a finished product that you can use in your own work "as is" but it should provide a basis for creating your own process. It is fairly well commented so I will finish by pointing out that the script....

    opens an excel file of an version suported by your excel application
    reads the excel file looking for predefined values in certain cells
    deletes the sections of the file that I don't need
    renames the columns with usable values
    saves the file as excel v 4, ready for import


  2. #2
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,644

    Default RE: Preparing a spreadsheet to be imported

    I may use that one in the near future.

    Thanks Stan.

  3. #3
    Member
    Real Name
    Brian Kelley
    Join Date
    Dec 2005
    Posts
    33

    Default

    Hi, I'm interested in reading that code, but it won't download ... says not available, is it still in the code library? thanks.
    Brian

  4. #4
    "Certified" Alphaholic Lance Gurd's Avatar
    Real Name
    Lance Gurd
    Join Date
    Jun 2005
    Location
    Southampton, UK
    Posts
    1,441

    Default

    Me Too please

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

    Default Here's something similar

    Quote Originally Posted by Lance Gurd
    Me Too please
    I can't locate the original at the moment. This doesn't rename the columns but has the rest, I think.

    Code:
    FUNCTION ex_convert AS C (excelname AS C, newname AS C ) 
    Dim xlApp as p 
    dim myCell as c
    xlApp = ole.create("Excel.Application") 
    xlApp.Workbooks.Open(excelname) 
    'xlApp.Visible = .T. 'comment this line out if you don't want to see Excel
    xlApp.Rows("1:1").Select
    xlApp.Selection.Delete(-4162) '-4162 is shift remaining cells up
    xlApp.Columns("D:N").Select
    xlApp.Selection.Delete(-4159) '-4159 is shift remaining cells left
    xlApp.Columns("E:IV").Select
    xlApp.Selection.Delete(-4159)
    xlapp.DisplayAlerts = .F.
    xlApp.ActiveWorkbook.SaveAs(newname,33) '33 is a constant for Excel 4.0, dbf4 is 11
    xlApp.Workbooks.Close()
    xlapp.DisplayAlerts = .T.
    xlApp.Quit() 
    END FUNCTION 
    
    'to call the function, use the example on the next line 
    'ex_convert("c:\book1.xls","c:\book4.xls")
    Found the original attachment!
    Last edited by Stan Mathews; 03-28-2006 at 03:24 PM.

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

    Default Updated for post A5V5

    Later versions of Alpha are more strict about using excel methods as

    .select() rather than .select .

    This attachment is updated to reflect that requirement.
    Last edited by Stan Mathews; 03-28-2006 at 03:24 PM.

Similar Threads

  1. Importing Excel spreadsheet
    By Rick Hanson in forum Alpha Five Version 6
    Replies: 11
    Last Post: 09-19-2005, 02:36 PM
  2. Export to Excel spreadsheet
    By Ray Dean in forum Alpha Five Version 1
    Replies: 1
    Last Post: 07-18-2003, 11:42 AM
  3. Spreadsheet Copy & Paste
    By Keith Hubert in forum Alpha Five Version 5
    Replies: 10
    Last Post: 04-14-2003, 04:24 PM
  4. Importing 123 spreadsheet?
    By Bob Elliott in forum Alpha Five Version 4
    Replies: 1
    Last Post: 02-04-2002, 02:55 PM
  5. I require some help on spreadsheet link to Alpha 5
    By Ilesh Mistry in forum Alpha Five Version 4
    Replies: 11
    Last Post: 10-25-2001, 01:52 AM

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
  •