Announcement

Collapse
No announcement yet.

Reading Excel Spreadsheets

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Erik Christensen
    replied
    john,

    i know i am a little late. i think you may even consider using
    the clipboard for transferring the data as in this program:

    http://www.powerbasic.com/support/pb...ad.php?t=24697

    when you copy selected data in excel to the clipboard, they will
    be in tab-separated text format. for cells with formulas, just
    the result will be copied. the tab-separated text can very
    easily be pasted into any pb program using the routines in
    the program above.

    best regards,

    erik

    p.s. another simple approach would be to save the excel data in
    tab-separated text-format. this option is a built-in standard
    in any excel program and this standard format can be read by
    almost any other program. in the link below there is a routine
    to read such data to be used in a pb program. good luck.

    http://www.powerbasic.com/support/pb...ad.php?t=24134

    ------------------




    [this message has been edited by erik christensen (edited february 17, 2004).]

    Leave a comment:


  • Eric Pearson
    replied
    John --

    We don't have any Excel-specific examples because ODBC (and therefore SQL Tools) are supposed to be DBMS-agnostic. You are not supposed to have to worry about which DBMS you are using.

    In reality however Excel is one of the quirkier formats and we have documented a number of things to watch out for, in the SQL Tools Help File. Search for Excel and you should see a number of items, mostly in Appendix A.

    -- Eric


    ------------------
    Perfect Sync Development Tools
    Perfect Sync Web Site
    Contact Us: mailto:[email protected][email protected]</A>

    Leave a comment:


  • John Schexnaydre
    replied
    Thanks all for the many great suggestions.

    The simple SheeetToTab may be the easiest, but I do have SQL Tools,
    and the other items to try.

    Thanks again to all for the most excellent help.

    John

    ------------------

    Leave a comment:


  • Sean Roe
    replied
    Hi John,

    Have you tried to use ADO to connect to the Excel spreadsheet? I have tried
    this approach before and as long as the spreadsheet is setup fairly simple,
    (ie the first row contains column headers and the rest of the rows are data)
    then this might be a possible solution. The following is a good article on using
    ADO with Excel: http://support.microsoft.com/default...257819#Connect

    I created a JellyFish plugin that I have used to look at an Excel spreadsheet but
    have not fully tested it against an Excel datasource. I know that the Extended properties
    need to be set to be successful at getting the data from Excel using ADO.
    If you have JellyFish you can check it out on the JellyFish plugin page.

    I haven't tried to use SQL Tools against an Excel data source but I would think
    that SQL Tools would give you a good solution as well.

    Hope this helps,
    Sean

    ------------------

    Leave a comment:


  • Steve Miltiadous
    replied
    Originally posted by Michael Mattias:
    >The users cannot hold the shift key and press another key at the same time

    I guess the computer age has affected the language more than I thought.

    We used to say they couldn't "walk and chew gum" at the same time...
    Some users cannot walk, never mind the gum part.

    Steve

    ------------------

    Leave a comment:


  • Michael Mattias
    replied
    >The users cannot hold the shift key and press another key at the same time

    I guess the computer age has affected the language more than I thought.

    We used to say they couldn't "walk and chew gum" at the same time...

    Leave a comment:


  • John Hackl
    replied
    how to avoid version dependence is the last message here: http://www.powerbasic.com/support/pb...ad.php?t=23631


    "excel sheet to tab delimited file using com" source: http://www.powerbasic.com/support/pb...ad.php?t=23807

    compiled is here: www.awfullysimple.com/sheetstotab.exe

    to use:
    sheetstotab c:\temp\book1.xls


    Leave a comment:


  • Michael Mattias
    replied
    well i do own a copy of sql tools and i will see if that works. i
    don't remember seeing any samples to access excel. any available
    don't know if sql tools has some kind of 'automatic' detection and setup, but iirc to use excel as an odbc datasource you have to make the 'table' = a 'named range' of cells. i futzed around with this for several hours, but once i had the datasource set up and the named range defined in the worksheet it worked fine. (i am terrible with excel, so i'm sure it took me a lot longer than it would take someone who knows what they are doing).

    of course, for the "old far.." er, make that "ancient warriors," there's always dde...
    a simple dde client example july 02, 2001

    mcm




    [this message has been edited by michael mattias (edited february 10, 2004).]

    Leave a comment:


  • John Schexnaydre
    replied
    Well I do own a copy of SQL Tools and I will see if that works. I
    don't remember seeing any samples to access Excel. Any available?

    Having Excel installed is not an issue, it knowing how to work with
    each version.

    I once found some code to have Word print a RTF file and exit.
    If I could just send Excel a commnad to File/Save as this
    would be enough.

    Thanks All!

    John



    ------------------

    Leave a comment:


  • Eric Pearson
    replied
    Lots of people use my company's SQL Tools product to open Excel files in PB/CC and PB/Win programs. Excel itself does not even have to be present on the target machine in any form; SQL Tools can open an Excel sheet directly, as long as some version of ODBC is in place (Win 98 and above, or NT4 and above). For more information, click on one of the links below.

    -- Eric Pearson, Perfect Sync Software

    ------------------
    Perfect Sync Development Tools
    Perfect Sync Web Site
    Contact Us: mailto:[email protected][email protected]</A>

    Leave a comment:


  • Edwin Knoppert
    replied
    Is equal to use Vb or Excel VBA imo.

    For long time i used a Excel sheet having a macro to save to desired filetype.
    The only problem we had was that it wan't always poss. to bypass the macro question.
    I did this in the calling exe by modifying the registry for a short while.

    We currently use com to do so.
    O btw, i have a commercial lib on my site named XLTable.
    It will open your excel sheet but requires Excel i'm afraid..


    ------------------
    http://www.hellobasic.com
    Freeware and commercial tools for PowerBASIC
    PBSoft - Netherlands

    Leave a comment:


  • Chuck Hicks
    replied
    You might consider shelling vbscript, which has a MUCH simpler
    COM interface than PB...
    http://www.rlmueller.net/Programs/XLRead.txt


    This simple vbscript example modifies the above code to extract
    the first 10 columns of a worksheet of any number of rows, and
    dumps them to a .csv file. The first two rows are assumed to
    be headers. Run with cscript to test, shell with wscript to
    avoid opening DOS window.

    [CODE]
    Option Explicit

    Const ForReading = 1
    Const ForWriting = 2

    Dim objExcel, objSheet, fso
    Dim sExcelPath, sCsvPath, hFile
    Dim iRow, iCol, sRow, DQ

    DQ = Chr(34)

    ' Bind to Excel object.
    On Error Resume Next
    Set objExcel = CreateObject("Excel.Application")
    If Err.Number <> 0 Then
    Wscript.Quit(Err)
    End If

    On Error GoTo 0

    sExcelPath = "C:\temp\Inventory.xls"
    sCsvPath = "C:\temp\Inventory.csv"

    Set fso = CreateObject ("Scripting.FileSystemObject")
    Set hFile = fso.OpenTextFile(sCsvPath, ForWriting, True)


    ' Open specified spreadsheet and select the first worksheet.
    objExcel.WorkBooks.Open sExcelPath
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

    ' Iterate through the rows of the spreadsheet after the first, until the
    ' first blank entry in the first column.
    iRow = 1

    Do
    sRow = ""

    '-- Extract columns 1-10 of each row
    For iCol = 1 To 10
    '-- Convert to CSV string
    sRow = sRow & DQ & objSheet.Cells(iRow,iCol).Value & DQ
    If iCol < 10 Then
    sRow = sRow & ","
    End If
    Next

    '-- Write the csv string to the output file
    hFile.WriteLine(sRow)

    iRow = iRow + 1

    '-- Exit when no value in first column (after row 2)
    If iRow > 2 And objSheet.Cells(iRow,1).Value = "" Then
    Exit Do
    End If
    Loop

    ' Close workbook and quit Excel.
    objExcel.ActiveWorkbook.Close
    objExcel.Application.Quit

    ' Clean up.
    Set objExcel = Nothing
    Set objSheet = Nothing
    hFile.Close

    {/CODE]

    [This message has been edited by Chuck Hicks (edited February 10, 2004).]

    Leave a comment:


  • John Schexnaydre
    replied
    Wish I could. Data comes from a 3rd party.

    ------------------


    [This message has been edited by John Schexnaydre (edited February 10, 2004).]

    Leave a comment:


  • George Bleck
    replied
    Simple answer...

    Remove it from Excel then and right a PB app to do all the work.

    ------------------
    Every day I try to learn one thing new,
    but new things to learn are increasing exponentially.
    At this rate I’m becoming an idiot faster and faster !!!
    ------------------
    George W. Bleck
    Lead Computer Systems Engineer
    KeySpan Corporation
    My Email

    Leave a comment:


  • John Schexnaydre
    replied
    Thanks for the suggestions.

    Having the user do anything is not an option. That is the current
    method. The users cannot hold the shift key and press another
    key at the same time much less select data and export or run a macro.

    The goal is avoiding having the user to do anything.

    Thanks
    John

    ------------------

    Leave a comment:


  • Russ Srole
    replied
    John,

    I recently did a job for one of the TV networks & they wanted me to read part of an Excel file. We determined that the most cost effective way of handling it was to have the user invoke a macro that selected the data, copied it to another "sheet" and saved that sheet as a comma delimited file. Reading that file is a snap. My point to them was that if/when the format or com interface to excel changes, they don't have to come back to me for more changes and if they change the spreadsheet, then all they have to do is change the macro. Cost effectiveness won out and that's how we did it.

    Russ Srole

    ------------------

    Leave a comment:


  • Edwin Knoppert
    replied
    there are 3 approaches.

    1) having excel installed and running a doc with a macro (increases user fun).
    2) the com way also having excel installed as in the examples dir.
    3) or http://www.powerbasic.com/support/pb...ad.php?t=23919
    which only gives you access to the excel structures which are a pain to make it work.


    ------------------
    http://www.hellobasic.com
    freeware and commercial tools for powerbasic
    pbsoft - netherlands

    Leave a comment:


  • John Schexnaydre
    started a topic Reading Excel Spreadsheets

    Reading Excel Spreadsheets

    I have a need to read some very simple Excel spread sheets to extract
    data.

    I know nothing about COM and the COM Excel Samples will not work with my version
    of Office XP.

    I looked at the COM examples in these forums, but they all seem to require
    certain versions of Excel to be installed.

    Before wasting time coming up tp speed with COM, can this ever work out in the
    field when I have no idea what version of Excel will be found? Also it seems
    if the user didn't install all of Excel, it may fail as well.

    Any simple way of reading Excel not knowing what might be installed?

    Thanks
    John


    ------------------
Working...
X