Announcement

Collapse
No announcement yet.

Reading Excel Spreadsheets

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

  • 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


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

  • #2
    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
    hellobasic

    Comment


    • #3
      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

      ------------------
      "There are two novels that can change a bookish fourteen-year old's life: The Lord of the Rings and Atlas Shrugged. One is a childish fantasy that often engenders a lifelong obsession with its unbelievable heroes, leading to an emotionally stunted, socially crippled adulthood, unable to deal with the real world. The other, of course, involves orcs." - John Rogers

      Comment


      • #4
        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

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

        Comment


        • #5
          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
          <b>George W. Bleck</b>
          <img src='http://www.blecktech.com/myemail.gif'>

          Comment


          • #6
            Wish I could. Data comes from a 3rd party.

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


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

            Comment


            • #7
              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).]

              Comment


              • #8
                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
                hellobasic

                Comment


                • #9
                  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>
                  "Not my circus, not my monkeys."

                  Comment


                  • #10
                    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



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

                    Comment


                    • #11
                      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).]
                      Michael Mattias
                      Tal Systems (retired)
                      Port Washington WI USA
                      [email protected]
                      http://www.talsystems.com

                      Comment


                      • #12
                        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


                        Comment


                        • #13
                          >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...
                          Michael Mattias
                          Tal Systems (retired)
                          Port Washington WI USA
                          [email protected]
                          http://www.talsystems.com

                          Comment


                          • #14
                            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

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

                            Comment


                            • #15
                              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

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

                              Comment


                              • #16
                                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

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

                                Comment


                                • #17
                                  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>
                                  "Not my circus, not my monkeys."

                                  Comment


                                  • #18
                                    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).]

                                    Comment

                                    Working...
                                    X