Re: extracting data from Excel

Discussion in 'XML' started by GIMME, Aug 2, 2003.

  1. GIMME

    GIMME Guest

    Cheryl,

    You probably aren't familiar with using Visual Basic from inside
    Excel....

    Anyway, you can iterate through the cells in a worksheet(or all the
    worksheets
    in a workbook for that matter) and you can do whatever you want ...

    You could reformat the data in the workbook and write it as XML. Then
    if ChartFX can read XML go with that. (Whether XML is a solution
    depends
    entirely on ChartFX.)

    OR, you could use some database package (you didn't say what database
    you
    are used to using) and then you could write the data in the worksheet
    into a database and go with that. (If you are using Oracle I recommend
    Oracle Objects for OLE, also known as OO4O.)

    In either case some coordination is required. You need to have the XL
    people always store the data starting from the same row and column and
    you must be able to find the last row and the last column from VB.

    For example, this VB macro creates an xml file named myfile.xml and
    it writes the data in a VB worksheet into it. Each row is a new XML
    element.

    One last thing, if you want to *read* XML there you must install
    MSXML (either version 3 or 4 - download it from the big evil
    web site ) and select the MSXML package from some menu
    so Excel can find it.


    Sub Macro1()
    Open "C:\code\myfile.xml" For Output As #1

    Sheets("Sheet1").Select
    Range("A1").Select


    Dim i As Integer
    Dim j As Integer
    Dim s As String


    Print #1, "<Data>"
    For i = 0 To 77
    If "1" = ActiveCell.Offset(i, 0) Then
    j = ActiveCell.Offset(i + 1, 0)
    s = "<Category>" + Chr(10)
    s = s + BuildXML(i + 1, j)
    s = s + "</Category>"
    Print #1, s
    End If
    Next i
    Print #1, "</Data>" + Chr(10)

    Close #1

    End Sub

    Function BuildXML(irow As Integer, iof As Integer) As String

    Dim s As String
    'MsgBox ActiveCell.Offset(irow, 1)
    'MsgBox iof


    s = "<PositionHeader>"
    s = s + "SomeHeader</PositionHeader>" + Chr(10)

    s = s + "<PositionTitle>"
    s = s + "<![CDATA[" + ActiveCell.Offset(irow, 1)
    s = s + "]]></PositionTitle>" + Chr(10)
    s = s + "<LOB>5</LOB>" + Chr(10)
    s = s + "<Country>CAN</Country>" + Chr(10)

    s = s + "<Labels>" + Chr(10)
    For i = 0 To iof - 1
    s = s + "<Label>"
    s = s + ActiveCell.Offset(irow + i, 2)
    s = s + "</Label>" + Chr(10)
    Next i
    s = s + "</Labels>" + Chr(10)



    s = s + "<Min2002s>" + Chr(10)
    For i = 0 To iof - 1
    s = s + "<Min2002>" + Format(ActiveCell.Offset(irow + i, 4)) +
    "</Min2002>" + Chr(10)
    Next i
    s = s + "</Min2002s>" + Chr(10)

    s = s + "<Max2002s>" + Chr(10)
    For i = 0 To iof - 1
    s = s + "<Max2002>" + Format(ActiveCell.Offset(irow + i, 5)) +
    "</Max2002>" + Chr(10)
    Next i
    s = s + "</Max2002s>" + Chr(10)


    BuildXML = s

    End Function








    (Cheryl) wrote in message news:<>...
    > I am looking for a solution to the problem described below. I'm not
    > sure if XML and its asociates are what I need. Input and guidance on
    > the best way to proceed would be greatly appreciated.
    >
    > The problem:
    > I am starting with a database that stores the names of Excel (v. 2000)
    > files that are located on the server. In the Excel files, there are
    > several worksheets containing data that needs to be extracted and used
    > in something like ChartFx. Is this a possibility with XML? I have
    > very limited knowledge of the what's possible surrounding this & would
    > like to know what avenues I should pursue.
    >
    > If I had my way, I'd make all the delinquents here at work just enter
    > information directly into a database. But, they are married to using
    > Excel spreadsheets since these can be done "offline" and uploaded.
    >
    > Thanks!
    GIMME, Aug 2, 2003
    #1
    1. Advertising

  2. GIMME

    amitcs100

    Joined:
    Dec 27, 2008
    Messages:
    1
    Extract data from excel workbook using jsp

    If there is a workbook which contain more then 10 spreadsheets then how to extract the specific data is this possible plz help me if possible.. ?:-(
    amitcs100, Dec 27, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anonieko
    Replies:
    2
    Views:
    7,312
    JackStraw73
    Feb 12, 2008
  2. Robbe Morris [C# MVP]

    asp.net extracting excel charts from an Excel file

    Robbe Morris [C# MVP], Dec 15, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    473
    Robbe Morris [C# MVP]
    Dec 15, 2006
  3. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,499
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  4. ruds
    Replies:
    4
    Views:
    847
    ttrifonov
    Jun 2, 2008
  5. aminnis
    Replies:
    1
    Views:
    139
    Brian Helterline
    Sep 16, 2004
Loading...

Share This Page