VBS Script Syntax Help

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

VBS Script Syntax Help

Logged in as: Guest

arrSession:exec spGetSession 2,2,61433

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> VBS Script Syntax Help
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

VBS Script Syntax Help - 6/16/2008 10:19:28 AM

gi_james

Posts: 4
Score: 0
Joined: 6/14/2008
From: Misawa Japan
Status: offline

Please help on this.

I need to convert this to the correct syntax.

If column B ROWx = (a number greater than 999999) then
Cut the range(s) beneath it, and paste it at previous row starting at column H.
For example, here�s raw data (MINUS THE DASHES).
For every line that contains a confirmation number (i.e. 13483836), the line or lines beneath it needs to be moved over to the end of the line with the confirmation number. Like this:

Before:
2 - 13483836 - SUPPORT - 2 - BLDG949 - BLDG998 - 13:00
3 - 12:45 - 12:55 - 13:10   - 13:42 - 00:00 - 00:57 - 03B01912 - SMITH, JOHN C

After: (should be one line)
2 - 13483836 - SUPPORT - 2 - BLDG949 - BLDG998 - 13:00 - 3 - 12:45 - 12:55 - 13:10   - 13:42 - 00:00 - 00:57 - 03B01912 - SMITH, JOHN C

(sometimes there are more than one line beneath) (Before)
2 - 13482116 - SUPPORT - 2 - BLDG100 - BLDG200 - 13:00
3 - 12:45 - 12:55 - 13:10   - 13:42 - 00:00 - 00:57 - 03B01915 - DOE, JOHN C
3 - 12:55 - 12:55 - 13:50   - 13:52 - 00:00 - 00:55 - 03B01915 - DOE, SARA C
3 - 12:45 - 12:55 - 13:10   - 13:42 - 00:00 - 00:57 - 03B01955 - DOE, JANE C

(After)
2 - 13482116 - SUPPORT - 2 - BLDG100 - BLDG200 - 13:00 - 3 - 12:45 - 12:55 - 13:10   - 13:42 - 00:00 - 00:57 - 03B01915 - DOE, JOHN C
                                                                                                             3 - 12:55 - 12:55 - 13:50   - 13:52 - 00:00 - 00:55 - 03B01915 - DOE, SARA C
                                                                                                             3 - 12:45 - 12:55 - 13:10   - 13:42 - 00:00 - 00:57 - 03B01955 - DOE, JANE C

I would greatly appreciate any help on this. I can't seem to find what I need searching around on the net. The alternative is manually cutting and pasting cells. This take hours of time, time I'd rather be doing other things.

Thanks in advance,

_____________________________

James Strickland
Misawa Air Base, Japan
http://www.strickstuff.com

Post #: 1

RE: VBS Script Syntax Help - 6/16/2008 1:39:59 PM

dm_4ever

Posts: 2666
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

What code do you have so far?

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to gi_james)

Post #: 2

RE: VBS Script Syntax Help - 6/16/2008 2:59:43 PM

gi_james

Posts: 4
Score: 0
Joined: 6/14/2008
From: Misawa Japan
Status: offline

This is the way I did it so far. But this only works for that paticular file.

Sub OLVIMS_REPORT()
'======================================================
' CLOSED 868 REPORT AUTO FORMAT SCRIPT/MACRO
'======================================================

Columns("A:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("H1").Select
    Cells.Replace What:="END OF REPORT", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A2").Select
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Range("A2:I2").Select
    Selection.Cut Destination:=Range("H1:P1")
    Range("A4:I4").Select
    Selection.Cut Destination:=Range("H3:P3")
    Range("A6:I6").Select
    Selection.Cut Destination:=Range("H5:P5")
    Range("A8:I8").Select
    Selection.Cut Destination:=Range("H7:P7")
    Range("A10:I10").Select
    Selection.Cut Destination:=Range("H9:P9")
    Range("A12:I12").Select
    Selection.Cut Destination:=Range("H11:P11")
    Range("A14:I14").Select
    Selection.Cut Destination:=Range("H13:P13")
    Range("H13:P13").Select
    ActiveWindow.SmallScroll Down:=9
    Range("A16:I16").Select
    Selection.Cut Destination:=Range("H15:P15")
    Range("A18:I18").Select
    Selection.Cut Destination:=Range("H17:P17")
    Range("A20:I20").Select
    Selection.Cut Destination:=Range("H19:P19")
    Range("A22:I22").Select
    Selection.Cut Destination:=Range("H21:P21")
    Range("A24:I24").Select
    Selection.Cut Destination:=Range("H23:P23")
    Range("A26:I26").Select
    Selection.Cut Destination:=Range("H25:P25")
    Range("H25:P25").Select
    ActiveWindow.SmallScroll Down:=12
    Range("A28:I28").Select
    Selection.Cut Destination:=Range("H27:P27")
    Range("A30:I30").Select
    Selection.Cut Destination:=Range("H29:P29")
    Range("A32:I32").Select
    Selection.Cut Destination:=Range("H31:P31")
    Range("A34:I34").Select
    Selection.Cut Destination:=Range("H33:P33")
    Range("A36:I36").Select
    Selection.Cut Destination:=Range("H35:P35")
    Range("A38:I38").Select
    Selection.Cut Destination:=Range("H37:P37")
    Range("A40:I40").Select
    Selection.Cut Destination:=Range("H39:P39")
    Range("A42:I42").Select
    Selection.Cut Destination:=Range("H41:P41")
    Range("A44:I45").Select
    Selection.Cut Destination:=Range("H43:P44")
    Range("H43:P44").Select
    ActiveWindow.SmallScroll Down:=18
    Range("A47:I47").Select
    Selection.Cut Destination:=Range("H46:P46")
    Range("A49:I49").Select
    Selection.Cut Destination:=Range("H48:P48")
    Range("A51:I51").Select
    Selection.Cut Destination:=Range("H50:P50")
    Range("A53:I54").Select
    Selection.Cut Destination:=Range("H52:P53")
    Range("A56:I57").Select
    Selection.Cut Destination:=Range("H55:P56")
    Range("A59:I60").Select
    Selection.Cut Destination:=Range("H58:P59")
    Range("A62:I63").Select
    Selection.Cut Destination:=Range("H61:P62")
    Range("H61:P62").Select
    ActiveWindow.SmallScroll Down:=21
    Range("A65:I66").Select
    Selection.Cut Destination:=Range("H64:P65")
    Range("A68:I70").Select
    Selection.Cut Destination:=Range("H67:P69")
    Range("A72:I74").Select
    Selection.Cut Destination:=Range("H71:P73")
    Range("A76:I76").Select
    Selection.Cut Destination:=Range("H75:P75")
    Range("A78:I80").Select
    Selection.Cut Destination:=Range("H77:P79")
    Range("H77:P79").Select
    ActiveWindow.SmallScroll Down:=15
    Range("A82:I85").Select
    Selection.Cut Destination:=Range("H81:P84")
    Range("A87:I87").Select
    Selection.Cut Destination:=Range("H86:P86")
    Range("H86:P86").Select
    ActiveWindow.SmallScroll Down:=-99
    
    '
    ' Delete Blank Rows Macro
    '
    On Error Resume Next
  
    Columns("I:I").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'
    ' auto_width Macro
    '
    '
    Columns("A:Z").EntireColumn.AutoFit

'
' COLOR Macro
'
'
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual   'pre XL97 xlManual
  Dim Rng As Range, ix As Long
  Set Rng = Intersect(Range("P:P"), ActiveSheet.UsedRange)
  For ix = Rng.Count To 1 Step -1
      If Trim(Replace(Rng.Item(ix).Text, Chr(160), Chr(32))) = "" Then
        Rng.Item(ix).ClearContents
      End If
  Next
done:
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True

Columns("H:H").SpecialCells(xlCellTypeBlanks).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .COLOR = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Columns("P:P").Select
    Columns("P:P").SpecialCells(xlCellTypeBlanks).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .COLOR = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub

I did this by recording macro. But, the problem is, that the number of rows varies.

-James

_____________________________

James Strickland
Misawa Air Base, Japan
http://www.strickstuff.com

(in reply to dm_4ever)

Post #: 3

RE: VBS Script Syntax Help - 6/17/2008 7:15:43 AM

mcds99

Posts: 434
Score: 4
Joined: 2/28/2006
Status: offline

Not sure if this is what you are looking for but his show how to read and write using excel.
Everything needs to be defined with excel each column needs to be identified.
The number of rows doesn't matter it reads from beginning to end of the spreadsheet.

From a previous post 8-O

'### Read a Row and Gather the Required Column data
Set objExcel = CreateObject("Excel.Application")
  If Err.Number <> 0 Then
      On Error GoTo 0
      Wscript.Echo "Excel application not found."
      Wscript.Quit
  End If
strPathExcel = "C:\Script\Test\Xls\scopes.xls"
objExcel.Workbooks.open strPathExcel
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
'### Read a Row of data until the First Cell in a Row is Blank.
Do While objSheet.Cells(intRow, 1).Value <> ""
strCN1 = Trim(objSheet.Cells(intRow, 1).Value) 'subnet
strCN2 = Trim(objSheet.Cells(intRow, 2).Value) 'mask
strCN3 = Trim(objSheet.Cells(intRow, 3).Value) 'scope name
strCN4 = Trim(objSheet.Cells(intRow, 4).Value) 'start IP
strCN5 = Trim(objSheet.Cells(intRow, 5).Value) 'end IP
strCN6 = Trim(objSheet.Cells(intRow, 6).Value) 'gateway1
strCN7 = Trim(objSheet.Cells(intRow, 7).Value) 'gateway2
strCN8 = Trim(objSheet.Cells(intRow, 8).Value) 'primary DNS
strCN9 = Trim(objSheet.Cells(intRow, 9).Value) 'secondary DNS
strCN10 = Trim(objSheet.Cells(intRow, 10).Value) 'primary WINS
strCN11 = Trim(objSheet.Cells(intRow, 11).Value) 'secondary WINS
strCN12 = Trim(objSheet.Cells(intRow, 12).Value) 'domain
strCN13 = Trim(objSheet.Cells(intRow, 13).Value) 'lease
strCN14 = Trim(objSheet.Cells(intRow, 14).Value) 'option 150
strCN15 = Trim(objSheet.Cells(intRow, 15).Value) 'option 176
strCN16 = Trim(objSheet.Cells(intRow, 16).Value) 'option 250
'### Format the Strings.
Str1 = strCN1 & aSpace & strCN2 & aSpace & strCN3 & aSpace
Str2 = strCN4 & aSpace & strCN5 & aSpace & strCN6 & aSpace
Str3 = strCN7 & aSpace & strCN8 & aSpace & strCN9 & aSpace
Str4 = strCN10 & aSpace & strCN11 & aSpace & strCN12 & aSpace
Str5 = strCN13 & aSpace & strCN14 & aSpace & strCN15 & aSpace & strCN16
'### Connect the Strings & Display
Str7 = Str1 & Str2 & Str3 & Str4 & Str5
wScript.Echo Str7
'### Write DHCP Scope
Set objExcel2 = CreateObject("Excel.Application")
  If Err.Number <> 0 Then
      On Error GoTo 0
      Wscript.Echo "Excel application not found."
      Wscript.Quit
  End If
strPathExcel2 = "C:\Script\Test\Xls\scopes2.xls"
objExcel2.Workbooks.open strPathExcel2
Set objSheet2 = objExcel2.ActiveWorkbook.Worksheets(1)
objSheet2.Cells(intRow, 1).Value = strCN1
objSheet2.Cells(intRow, 2).Value = strCN2
objSheet2.Cells(intRow, 3).Value = strCN3
objSheet2.Cells(intRow, 4).Value = strCN4
objSheet2.Cells(intRow, 5).Value = strCN5
objSheet2.Cells(intRow, 6).Value = strCN6
objSheet2.Cells(intRow, 7).Value = strCN7
objSheet2.Cells(intRow, 8).Value = strCN8
objSheet2.Cells(intRow, 9).Value = strCN9
objSheet2.Cells(intRow, 10).Value = strCN10
objSheet2.Cells(intRow, 11).Value = strCN11
objSheet2.Cells(intRow, 12).Value = strCN12
objSheet2.Cells(intRow, 13).Value = strCN13
objSheet2.Cells(intRow, 14).Value = strCN14
objSheet2.Cells(intRow, 15).Value = strCN15
objSheet2.Cells(intRow, 16).Value = strCN16
objExcel2.ActiveWorkbook.SaveAs strPathExcel2
objExcel2.Workbooks.close
objExcel2.Application.Quit
'### Increment the Row to Read
intRow = intRow + 1
fCmd = ""
'*** wscript.echo fCmd
'### Read another row
Loop

_____________________________

Sam

Keep it Simple Make it Fun KiSMiF

(in reply to gi_james)