Parsing out excel spreadsheet

I have a little dilema, I have created a VBScript to output the values of every column in a excel spreadsheet, but I have been asked to provide something different now. I have to create an If statement that says that if the first letter in column A is a K then output only values in Col B, C, and D.  If the first letter is Col A is C then output D, E, F ...
I do not have experience with If statements any help would most appreciated ... here is my code now:

sText = "C:\SLIC_Conversion_Tool\SLICConversion.txt"

Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Excel File|*.xlxs|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:\Scripts"
intResult = objDialog.ShowOpen
 
If intResult = 0 Then
    Wscript.Quit
Else
    Wscript.Echo objDialog.FileName
End If

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(objDialog.FileName, False, False)
oExcel.Visible = False ' Do not display excel window
Set oSheet = oBook.Sheets(1)

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oOut = oFSO.CreateTextFile(sText, True)


'Start with row 2 of spreadsheet assuming first row has column headings

iRow = 2

'Read each row of the spreadsheet until a blank value is encountered in column 1
'Set string limits according to the HA Table
Do While oSheet.Cells(iRow, 1).Value <> ""
    colA = Left((oSheet.Cells(iRow, 1).Value)& Space(4),4) 'Transaction Key
    colB = Left((oSheet.Cells(iRow, 2).Value)& Space(5),5) 'From Cage
    colC = Left((oSheet.Cells(iRow, 3).Value)& Space(32),32) 'From Reference Number
    colD = Left((oSheet.Cells(iRow, 4).Value)& Space(5),5) 'To Cage
    colE = Left((oSheet.Cells(iRow, 5).Value)& Space(32),32) 'From Reference Number
    colF = Right((oSheet.Cells(iRow, 6).Value)& Space(26),26) 'To RNVC
    colG = Left((oSheet.Cells(iRow, 7).Value)& Space(1),1) 'Additional RNCC
    colH = Left((oSheet.Cells(iRow, 8).Value)& Space(1),1) 'Additional RNVC


oOut.WriteLine colA & "" & colB & "" & colC & ""& colD & "" & colE & "" & colF & "" & colH & ""

       
iRow = iRow+1
   
Loop

oExcel.quit
oOut.Close       
drezner7Asked:
Who is Participating?
 
prashanthdConnect With a Mentor Commented:
Typo error...try the following

regards
Prashanth
sText = "C:\SLIC_Conversion_Tool\SLICConversion.txt"

Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Excel File|*.xlxs|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:\Scripts"
intResult = objDialog.ShowOpen

If intResult = 0 Then
    WScript.Quit
Else
    WScript.Echo objDialog.FileName
End If

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(objDialog.FileName, False, False)
oExcel.Visible = False ' Do not display excel window
Set oSheet = oBook.Sheets(1)

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oOut = oFSO.CreateTextFile(sText, True)


'Start with row 2 of spreadsheet assuming first row has column headings

iRow = 2

'Read each row of the spreadsheet until a blank value is encountered in column 1
'Set string limits according to the HA Table
Do While oSheet.Cells(iRow, 1).Value <> ""
    colA = Left((oSheet.Cells(iRow, 1).Value)& Space(4),4) 'Transaction Key   
    colB = Left((oSheet.Cells(iRow, 2).Value)& Space(5),5) 'From Cage
    colC = Left((oSheet.Cells(iRow, 3).Value)& Space(32),32) 'From Reference Number
    colD = Left((oSheet.Cells(iRow, 4).Value)& Space(5),5) 'To Cage
    colE = Left((oSheet.Cells(iRow, 5).Value)& Space(32),32) 'From Reference Number
    colF = Right((oSheet.Cells(iRow, 6).Value)& Space(26),26) 'To RNVC
    colG = Left((oSheet.Cells(iRow, 7).Value)& Space(1),1) 'Additional RNCC
    colH = Left((oSheet.Cells(iRow, 8).Value)& Space(1),1) 'Additional RNVC
    
    If UCase(Left(colA,1))="K" Then
        oOut.WriteLine colA & "" & colB & "" & colC & "" & colD 
    Else
        oOut.WriteLine colA & "" & colD & "" & colE & "" & colF
    End If
    
    
    iRow = iRow+1
    
Loop

oExcel.quit
oOut.Close

Open in new window

0
 
prashanthdCommented:
Try the following

regards
Prashanth
sText = "C:\SLIC_Conversion_Tool\SLICConversion.txt"

Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Excel File|*.xlxs|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:\Scripts"
intResult = objDialog.ShowOpen

If intResult = 0 Then
    WScript.Quit
Else
    WScript.Echo objDialog.FileName
End If

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(objDialog.FileName, False, False)
oExcel.Visible = False ' Do not display excel window
Set oSheet = oBook.Sheets(1)

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oOut = oFSO.CreateTextFile(sText, True)


'Start with row 2 of spreadsheet assuming first row has column headings

iRow = 2

'Read each row of the spreadsheet until a blank value is encountered in column 1
'Set string limits according to the HA Table
Do While oSheet.Cells(iRow, 1).Value <> ""
    colA = Left((oSheet.Cells(iRow, 1).Value)& Space(4),4) 'Transaction Key   
    colB = Left((oSheet.Cells(iRow, 2).Value)& Space(5),5) 'From Cage
    colC = Left((oSheet.Cells(iRow, 3).Value)& Space(32),32) 'From Reference Number
    colD = Left((oSheet.Cells(iRow, 4).Value)& Space(5),5) 'To Cage
    colE = Left((oSheet.Cells(iRow, 5).Value)& Space(32),32) 'From Reference Number
    colF = Right((oSheet.Cells(iRow, 6).Value)& Space(26),26) 'To RNVC
    colG = Left((oSheet.Cells(iRow, 7).Value)& Space(1),1) 'Additional RNCC
    colH = Left((oSheet.Cells(iRow, 8).Value)& Space(1),1) 'Additional RNVC
    
    If UCase(Left(colA,1))="K" Then
        oOut.WriteLine colA & "" & colB & "" & colC & "" & colD 
    Else
        oOut.WriteLine colA & "" & colE & "" & colF & "" & colH
    End If
    
    
    iRow = iRow+1
    
Loop

oExcel.quit
oOut.Close

Open in new window

0
 
drezner7Author Commented:
That is great, How would I add for multiple lines... would I add "And" ?  Example:

If UCase(Left(colA,1))="K" Then
        oOut.WriteLine colA & "" & colB & "" & colC & "" & colD
And
If UCase(Left(colA,1))="K" Then
        oOut.WriteLine colE & "" & colF & "" & colG & "" & colH
Else
oOut.WriteLine ("Unable to process data")
    End If


0
 
drezner7Author Commented:
prashanthd, when I run the statement by itself it works great, but when I run it with multiple statements like this:
If UCase(Left(colA,1))="K" Then
        oOut.WriteLine colA & "" & colB & "" & colC & "" & colD
Else
        oOut.WriteLine ("Statement did not process for K")
    End If
   
If UCase(Left(colA,1))="C" Then
        oOut.WriteLine colA & "" & colD & "" & colE & "" & colF & ""
    Else
        oOut.WriteLine ("Statement did not process for C")
    End If
   
If UCase(Left(colA,1))="A" Then
        oOut.WriteLine colA & "" & colD & "" & colE & "" & colB & "" & colC & "" & colG & "" & colH & ""
    Else
        oOut.WriteLine ("Statement did not process for G")
    End If
My output comes out like this: (I get the Else statement) what am I doing wrong?

KHA 1    2                               3    
Statement did not process for C
Statement did not process for G
Statement did not process for K
CHA 1    2                                                        
Statement did not process for G
Statement did not process for K
Statement did not process for C
AHB 1    2                               3    4                               56

Please help, I am trying to make this work as I have a deadline..

Thank you
0
 
drezner7Author Commented:
Thank you, I figured out my error for using multiple statements... Your suggestion worked great.
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.