[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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       
0
drezner7
Asked:
drezner7
  • 3
  • 2
1 Solution
 
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
 
prashanthdCommented:
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
 
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

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now