• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Reading Excel Spreadsheet

Please help I am struggling with this. I am reading an excel spreadsheet but based on the what Column A has it would then print out the various columns corresponding to that row. Here is my code it is not working What am I doing wrong:

iRow = 2



Do While oSheet.Cells(iRow, 1).Value <> ""
    colA = Left((oSheet.Cells(iRow, 1).Value))
    colB = Left((oSheet.Cells(iRow, 2).Value))
    colC = Left((oSheet.Cells(iRow, 3).Value))
    colD = Left((oSheet.Cells(iRow, 4).Value))
    colE = Left((oSheet.Cells(iRow, 5).Value))
    colF = Left((oSheet.Cells(iRow, 6).Value))
    colG = Left((oSheet.Cells(iRow, 7).Value))
    colH = Left((oSheet.Cells(iRow, 8).Value))
    colI = Left((oSheet.Cells(iRow, 9).Value))
    colJ = Left((oSheet.Cells(iRow, 10).Value))
    colK = Left((oSheet.Cells(iRow, 11).Value))

If colA = "XA" Then

'If UCase(Left(colA,2))="XA" Then
        oOut.WriteLine("  <XA_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  </XA_data>")
       
End If

If UCase(Left(colA,1))="XB" Then
        oOut.WriteLine("  <XB_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  <LSACONXB>" & colC & "</LSACONXB>")
        oOut.WriteLine("  <ALTLCNXB>" & colD & "</ALTLCNXB>")
        oOut.WriteLine("  <LCNTYPXB>" & colE & "</LCNTYPXB>")
0
drezner7
Asked:
drezner7
  • 7
  • 5
  • 2
1 Solution
 
brendanmeyerCommented:
does it come up with any errors?
0
 
drezner7Author Commented:
No errors, the XML generates, but it is only pulling data prior to reading the excel spreadsheet. Would like me to send you the entire code?
0
 
drezner7Author Commented:
I believe it is failing when it begins to read the excel spreadsheet..
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
brendanmeyerCommented:
yes post the code on here
0
 
David LinkerCommented:
Can you please provide a sample of what the data in the spreadsheet looks like.

Also, did you intend to comment out the second "IF" statement:
         'If UCase(Left(colA,2))="XA" Then
0
 
drezner7Author Commented:
Here you go:

'ATLAS
'Author:
'Description:



If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /c cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

sXML = "C:\ATLAS\Output\Test.xml"

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(sXML, True)



oOut.WriteLine("<?xml version=""1.0"" encoding=""UTF-8"" ?>")
oOut.WriteLine("<GEIA-STD-0007>")
oOut.WriteLine("<EIACODXA>F135A_US</EIACODXA>")

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


iRow = 2


Do While oSheet.Cells(iRow, 1).Value <> ""
    colA = Left((oSheet.Cells(iRow, 1).Value))
    colB = Left((oSheet.Cells(iRow, 2).Value))
    colC = Left((oSheet.Cells(iRow, 3).Value))
    colD = Left((oSheet.Cells(iRow, 4).Value))
    colE = Left((oSheet.Cells(iRow, 5).Value))
    colF = Left((oSheet.Cells(iRow, 6).Value))
    colG = Left((oSheet.Cells(iRow, 7).Value))
    colH = Left((oSheet.Cells(iRow, 8).Value))
    colI = Left((oSheet.Cells(iRow, 9).Value))
    colJ = Left((oSheet.Cells(iRow, 10).Value))
    colK = Left((oSheet.Cells(iRow, 11).Value))


If UCase(Left(colA,1))= "XA" Then
        oOut.WriteLine("  <XA_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  </XA_data>")
       
End If


If UCase(Left(colA,1))= "XB" Then
        oOut.WriteLine("  <XB_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  <LSACONXB>" & colC & "</LSACONXB>")
        oOut.WriteLine("  <ALTLCNXB>" & colD & "</ALTLCNXB>")
        oOut.WriteLine("  <LCNTYPXB>" & colE & "</LCNTYPXB>")
        oOut.WriteLine("  <LCNINDXB>" & colF & "</LCNINDXB>")
        oOut.WriteLine("  <LCNAMEXB>" & colG & "</LCNAMEXB>")
        oOut.WriteLine("  <SYSIDNXB>" & colH & "</SYSIDNXB>")
        oOut.WriteLine("  </XB_data>")
End If

If UCase(Left(colA,1))= "XC" Then
        oOut.WriteLine("  <XC_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  <LSACONXB>" & colC & "</LSACONXB>")
        oOut.WriteLine("  <ALTLCNXB>" & colD & "</ALTLCNXB>")
        oOut.WriteLine("  <LCNTYPXB>" & colE & "</LCNTYPXB>")
        oOut.WriteLine("  <UOCSEIXC>" & colF & "</UOCSEIXC>")
        oOut.WriteLine("  <PCCNUMXC>" & colF & "</PCCNUMXC>")
        oOut.WriteLine("  </XC_data>")

End If

If UCase(Left(colA,1))= "XE" Then
        oOut.WriteLine("  <XE_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  <LSACONXE>" & colC & "</LSACONXE>")
        oOut.WriteLine("  <ALTLCNXE>" & colD & "</ALTLCNXE>")
        oOut.WriteLine("  <LCNTYPXE>" & colE & "</LCNTYPXE>")
        oOut.WriteLine("  <LCNSEIXE>" & colF & "</LCNSEIXE>")
        oOut.WriteLine("  <ALCSEIXE>" & colG & "</ALCSEIXE>")
        oOut.WriteLine("  <LTYSEIXE>" & colH & "</LTYSEIXE>")
        oOut.WriteLine("  <FRSNUMXE>" & colI & "</FRSNUMXE>")
        oOut.WriteLine("  <TOSNUMXE>" & colJ & "</TOSNUMXE>")
        oOut.WriteLine("  </XE_data>")

End If

oOut.WriteLine("</GEIA-STD-0007>")

iRow = iRow+1
   
Loop



ooExcel.quit
WScript.Echo "Conversion Completed Successfully"
oOut.Close       
0
 
drezner7Author Commented:
Here is the test data
Test-Data.xlsx
0
 
David LinkerCommented:
If this is the code - it will notwork as you're comparing the left "1" most character with a two character string:

If UCase(Left(colA,1))

should be

If UCase(Left(colA,2))

Also in the Do While - I've found the following more reliable:

Do While Not IsEmpty (oSheet.Cells(iRow, 1).Value)

Does it Output the final Successful message?

To check the loop is executing at least once, try adding a line just after the "While" statement as follows:
Wscript.Echo oSheet.Cells(iRow, 1).Value

This will give some clue as to where the problem is occurring
0
 
drezner7Author Commented:
It is still not working... When I add the Wscript.Echo oSheet.Cells(iRow, 1).Value it flashes to fast for me to see an output on the cmd screen
0
 
David LinkerCommented:
Here is the working code:
If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /c cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If

sXML = "C:\ATLAS\Output\Test.xml"

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(sXML, True)



oOut.WriteLine("<?xml version=""1.0"" encoding=""UTF-8"" ?>")
oOut.WriteLine("<GEIA-STD-0007>")
oOut.WriteLine("<EIACODXA>F135A_US</EIACODXA>")

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


iRow = 2


Do While oSheet.Cells(iRow, 1).Value <> ""
    colA = oSheet.Cells(iRow, 1).Value
    colB = oSheet.Cells(iRow, 2).Value
    colC = oSheet.Cells(iRow, 3).Value
    colD = oSheet.Cells(iRow, 4).Value
    colE = oSheet.Cells(iRow, 5).Value
    colF = oSheet.Cells(iRow, 6).Value
    colG = oSheet.Cells(iRow, 7).Value
    colH = oSheet.Cells(iRow, 8).Value
    colI = oSheet.Cells(iRow, 9).Value
    colJ = oSheet.Cells(iRow, 10).Value
    colK = oSheet.Cells(iRow, 11).Value


If UCase(Left(colA,2))= "XA" Then
        oOut.WriteLine("  <XA_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  </XA_data>")
       
End If


If UCase(Left(colA,2))= "XB" Then
        oOut.WriteLine("  <XB_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  <LSACONXB>" & colC & "</LSACONXB>")
        oOut.WriteLine("  <ALTLCNXB>" & colD & "</ALTLCNXB>")
        oOut.WriteLine("  <LCNTYPXB>" & colE & "</LCNTYPXB>")
        oOut.WriteLine("  <LCNINDXB>" & colF & "</LCNINDXB>")
        oOut.WriteLine("  <LCNAMEXB>" & colG & "</LCNAMEXB>")
        oOut.WriteLine("  <SYSIDNXB>" & colH & "</SYSIDNXB>")
        oOut.WriteLine("  </XB_data>")
End If

If UCase(Left(colA,2))= "XC" Then
        oOut.WriteLine("  <XC_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  <LSACONXB>" & colC & "</LSACONXB>")
        oOut.WriteLine("  <ALTLCNXB>" & colD & "</ALTLCNXB>")
        oOut.WriteLine("  <LCNTYPXB>" & colE & "</LCNTYPXB>")
        oOut.WriteLine("  <UOCSEIXC>" & colF & "</UOCSEIXC>")
        oOut.WriteLine("  <PCCNUMXC>" & colF & "</PCCNUMXC>")
        oOut.WriteLine("  </XC_data>")

End If

If UCase(Left(colA,2))= "XE" Then
        oOut.WriteLine("  <XE_data>")
        oOut.WriteLine("  <EIACODXA>" & colB & "</EIACODXA>")
        oOut.WriteLine("  <LSACONXE>" & colC & "</LSACONXE>")
        oOut.WriteLine("  <ALTLCNXE>" & colD & "</ALTLCNXE>")
        oOut.WriteLine("  <LCNTYPXE>" & colE & "</LCNTYPXE>")
        oOut.WriteLine("  <LCNSEIXE>" & colF & "</LCNSEIXE>")
        oOut.WriteLine("  <ALCSEIXE>" & colG & "</ALCSEIXE>")
        oOut.WriteLine("  <LTYSEIXE>" & colH & "</LTYSEIXE>")
        oOut.WriteLine("  <FRSNUMXE>" & colI & "</FRSNUMXE>")
        oOut.WriteLine("  <TOSNUMXE>" & colJ & "</TOSNUMXE>")
        oOut.WriteLine("  </XE_data>")

End If

oOut.WriteLine("</GEIA-STD-0007>")

iRow = iRow+1
   
Loop



oExcel.quit
WScript.Echo "Conversion Completed Successfully"
oOut.Close

Open in new window

0
 
David LinkerCommented:
Sorry - I forgot to fix one other mistake - replace:
objDialog.Filter = "Excel File|*.xlxs|All Files|*.*"

with:

objDialog.Filter = "Excel File|*.xlsx|All Files|*.*"

Note the slight error of xlXS vs xlSX
0
 
drezner7Author Commented:
djlinker: Thank you ..... thank you works great....
0
 
drezner7Author Commented:
Thank you so much... i really appreciate it
0
 
David LinkerCommented:
Glad I could help.  
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

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