?
Solved

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

Posted on 2007-10-10
6
Medium Priority
?
1,712 Views
Last Modified: 2016-08-29
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


0
Comment
Question by:pramod_studies
  • 2
  • 2
4 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20052033
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
 

Author Comment

by:pramod_studies
ID: 20052280
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
 

Author Comment

by:pramod_studies
ID: 20052327
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 20056909
   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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

840 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