Extract data from one excel file into another excel file using VBSCRIPT..

Posted on 2007-10-10
Last Modified: 2016-08-29
I am new to vbscript. I am trying to write a script to find few values from one excelsheet and put into another. purpose being, my first file has raw type of data so I will search data on particular criteria and put into new file well formatted.. so far I could get the value seen via msgbox,, but nothing more.. also I want scan through entire file for first file..

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Pramod\Scripting\test1.xls")
objExcel.Visible = True
Dim ServerNameAndOS
Dim SeverityLevel
Dim Titless
Dim PortServis
Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
For Each objCell In objRange
      objRange.find ("Vulnerabilities (").Activate
            intServerOSNameROW= objExcel.ActiveCell.Row -1
            ServerNameAndOS = objExcel.Cells(intServerOSNameROW,1)
msgbox ServerNameAndOS  

Question by:pramod_studies
    LVL 92

    Expert Comment

    by:Patrick Matthews
    It would be useful to see some sample data.  Please upload
    a sample file to an accessible web site, and come back
    here to post a link to the file.

    If you do not have your own web space, you can use  Please be advised that you
    will have to zip your file(s) before uploading if you use that

    Author Comment

    First of all thank you for your reply. I have uploaded the file


    Author Comment

    When I find text "Vulnerability (", I know right above that line I have a OS/Server information, so I can get that information. then I need to find for QID, Severity, Tile keywords and get respective values,, then go to next line... and so on...
    then I need to look for another "Vulnerability (" to see if there is any other server listed. if found then again search for QUD, severity fields on next line and so on...
    LVL 92

    Accepted Solution

       Dim xlApp, SourceWb, SourceWs, DestWs, rng, LastR, DestR, FromR
        Const xlHAlignCenter = -4108
        Const xlUp = -4162
        Const xlValues = -4163
        Const xlPart = 2
        Const xlByRows = 1
        Const xlNext = 1
        Const xlEdgeBottom = 9
        Const xlEdgeTop = 8
        Const xlEdgeLeft = 7
        Const xlEdgeRight = 10
        Const xlInsideHorizontal = 12
        Const xlInsideVertical = 11
        Const xlHairline = 1

        Set xlApp = CreateObject("Excel.Application")
        Set SourceWb = xlApp.Workbooks.Open("c:\source.xls")    'change as needed
        Set SourceWs = SourceWb.Worksheets(1)
        Set DestWs = xlApp.Workbooks.Add.Worksheets(1)
        With DestWs.[a2:e2]
            .Value = Array("Server / IP / OS", "QID:", "Severity", "Title", "Port/Service")
            .Interior.ColorIndex = 36
            .HorizontalAlignment = xlHAlignCenter
            .Font.Bold = True
        End With
        DestR = 2
        FromR = 1
            With SourceWs
                LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
                Set rng = .Range(.Cells(FromR, 1), .Cells(LastR, 1)).Find("Vulnerabilities (", _
                    .Cells(LastR, 1), xlValues, xlPart, xlByRows, xlNext, False)
                If rng Is Nothing Then Exit Do
                If Left(rng, 17) = "Vulnerabilities (" Then
                    DestR = DestR + 1
                    DestWs.Cells(DestR, 1) = rng.Offset(-1, 0)
                    DestWs.Cells(DestR, 2) = rng.Offset(6, 0)
                    DestWs.Cells(DestR, 3) = rng.Offset(3, 1)
                    DestWs.Cells(DestR, 4) = rng.Offset(3, 2)
                    DestWs.Cells(DestR, 5) = rng.Offset(3, 3)
                End If
                FromR = rng.Row + 1
            End With
        With DestWs
            .UsedRange.Cells.Borders(xlEdgeBottom).LineStyle = xlHairline
            .UsedRange.Cells.Borders(xlEdgeTop).LineStyle = xlHairline
            .UsedRange.Cells.Borders(xlEdgeLeft).LineStyle = xlHairline
            .UsedRange.Cells.Borders(xlEdgeRight).LineStyle = xlHairline
            .UsedRange.Cells.Borders(xlInsideHorizontal).LineStyle = xlHairline
            .UsedRange.Cells.Borders(xlInsideVertical).LineStyle = xlHairline
        End With

        SourceWb.Close False
        Set SourceWs = Nothing
        Set SourceWb = Nothing
        xlApp.Visible = True
        Set DestWs = Nothing
        Set xlApp = Nothing

        MsgBox "Done"

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now