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

hello
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  
Next


pramod_studiesAsked:
Who is Participating?
 
Patrick MatthewsCommented:
   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
   
    Do
        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
    Loop
   
    With DestWs
        .Columns.AutoFit
        .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"
0
 
Patrick MatthewsCommented:
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
http://www.ee-stuff.com.  Please be advised that you
will have to zip your file(s) before uploading if you use that
service.
0
 
pramod_studiesAuthor Commented:
Hi
First of all thank you for your reply. I have uploaded the file
https://filedb.experts-exchange.com/incoming/ee-stuff/4978-sourcefile.zip

0
 
pramod_studiesAuthor Commented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.