Re: extracting data from Excel

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

  1. GIMME

    GIMME Guest


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

    Anyway, you can iterate through the cells in a worksheet(or all the
    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
    entirely on ChartFX.)

    OR, you could use some database package (you didn't say what database
    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

    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


    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. Advertisements

  2. amitcs100


    Dec 27, 2008
    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
    1. Advertisements

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
    Feb 12, 2008
  2. Robbe Morris [C# MVP] extracting excel charts from an Excel file

    Robbe Morris [C# MVP], Dec 15, 2006, in forum: ASP .Net
    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
    Oct 24, 2007
  4. ruds
    Jun 2, 2008
  5. aminnis
    Brian Helterline
    Sep 16, 2004

Share This Page