VBscript to change excel cell values

I need to open an excel spreadsheet and with a vbscript take the value
in column 1 and parse it into column 2 and 3.

Right now the spreadsheet only has this column
10.1.1.2/24
10.2.2.2/24


I need to run a script that will convert the spreadsheet into this
format with 3 columns.
10.1.1.2/24 10.1.12 24
10.2.2.2/24 10.2.2.2 24


Would I use a script something like this:


Option Explicit
Dim strSheet, intRow
Dim objExcel, objSpread
Dim strComputer


strSheet = "c:\test.xls"


' Open the Excel spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objSpread = objExcel.Workbooks.Open(strSheet)
intRow = 2 'Row 1 often containes headings


' Here is the loop that cycles through the cells
Do Until objExcel.Cells(intRow, 1).Value = ""
strComputer = objExcel.Cells(intRow, 1).Value

intRow = intRow + 1
Loop
objExcel.Quit


WScript.Quit


What do I place in the loop to accomplish the copy paste and parse?


Thanks for your help.

Suresh
Thu Aug 10 21:30:15 CDT 2006

Unless you indeed need to use vbscript for some reason, you can
accomplish the parsing using only excel formulas.

e.g.

Cell A1
10.1.1.2/24

Cell A2
=LEFT(A1,FIND("/",A1)-1)

Cell A3
=MID(A1,FIND("/",A1)+1,999)


I hope this helps.

Suresh


mcalcote wrote:
> I need to open an excel spreadsheet and with a vbscript take the value
> in column 1 and parse it into column 2 and 3.
>
> Right now the spreadsheet only has this column
> 10.1.1.2/24
> 10.2.2.2/24
>
>
> I need to run a script that will convert the spreadsheet into this
> format with 3 columns.
> 10.1.1.2/24 10.1.12 24
> 10.2.2.2/24 10.2.2.2 24
>
>
> Would I use a script something like this:
>
>
> Option Explicit
> Dim strSheet, intRow
> Dim objExcel, objSpread
> Dim strComputer
>
>
> strSheet = "c:\test.xls"
>
>
> ' Open the Excel spreadsheet
> Set objExcel = CreateObject("Excel.Application")
> Set objSpread = objExcel.Workbooks.Open(strSheet)
> intRow = 2 'Row 1 often containes headings
>
>
> ' Here is the loop that cycles through the cells
> Do Until objExcel.Cells(intRow, 1).Value = ""
> strComputer = objExcel.Cells(intRow, 1).Value
>
> intRow = intRow + 1
> Loop
> objExcel.Quit
>
>
> WScript.Quit
>
>
> What do I place in the loop to accomplish the copy paste and parse?
>
>
> Thanks for your help.

ekkehard
Fri Aug 11 02:14:21 CDT 2006

mcalcote wrote:

> I need to open an excel spreadsheet and with a vbscript take the value
> in column 1 and parse it into column 2 and 3.
>
> Right now the spreadsheet only has this column
> 10.1.1.2/24
> 10.2.2.2/24
>
>
> I need to run a script that will convert the spreadsheet into this
> format with 3 columns.
> 10.1.1.2/24 10.1.12 24
> 10.2.2.2/24 10.2.2.2 24
>
>
> Would I use a script something like this:
>
>
> Option Explicit
> Dim strSheet, intRow
> Dim objExcel, objSpread
> Dim strComputer
>
>
> strSheet = "c:\test.xls"
>
>
> ' Open the Excel spreadsheet
> Set objExcel = CreateObject("Excel.Application")
> Set objSpread = objExcel.Workbooks.Open(strSheet)
> intRow = 2 'Row 1 often containes headings
>
>
> ' Here is the loop that cycles through the cells
> Do Until objExcel.Cells(intRow, 1).Value = ""
> strComputer = objExcel.Cells(intRow, 1).Value
>
> intRow = intRow + 1
> Loop
> objExcel.Quit
>
>
> WScript.Quit
>
>
> What do I place in the loop to accomplish the copy paste and parse?
>
>
> Thanks for your help.
>
Hope this helps:

Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
Dim sFSpec : sFSpec = oFS.GetAbsolutePathName( ".\netmask.xls" )
Dim oExcel : Set oExcel = CreateObject( "Excel.Application" )

Dim oWBook, oSheet, nRow, aParts

' open oWBook, get first sheet
Set oWBook = oExcel.Workbooks.Open( sFSpec )
Set oSheet = oWBook.Sheets( 1 )

nRow = 2
Do While "" <> Trim( oSheet.Cells( nRow, 1 ) )
aParts = Split( oSheet.Cells( nRow, 1 ), "/" )
If 1 = UBound( aParts ) Then
oSheet.Cells( nRow, 2 ) = aParts( 0 )
oSheet.Cells( nRow, 3 ) = aParts( 1 )
Else
oSheet.Cells( nRow, 2 ) = "???"
End If
nRow = nRow + 1
Loop

oWBook.Save
oWBook.Close
oExcel.Quit