Solved

Reading Excel Spreadsheet

Posted on 2011-09-08
14
292 Views
Last Modified: 2012-05-12
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
Comment
Question by:drezner7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 14

Expert Comment

by:brendanmeyer
ID: 36508002
does it come up with any errors?
0
 

Author Comment

by:drezner7
ID: 36508083
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
 

Author Comment

by:drezner7
ID: 36508098
I believe it is failing when it begins to read the excel spreadsheet..
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:brendanmeyer
ID: 36508116
yes post the code on here
0
 
LVL 4

Expert Comment

by:David Linker
ID: 36508121
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
 

Author Comment

by:drezner7
ID: 36508124
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
 

Author Comment

by:drezner7
ID: 36508165
Here is the test data
Test-Data.xlsx
0
 
LVL 4

Expert Comment

by:David Linker
ID: 36508181
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
 

Author Comment

by:drezner7
ID: 36508194
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
 
LVL 4

Accepted Solution

by:
David Linker earned 500 total points
ID: 36508201
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
 
LVL 4

Expert Comment

by:David Linker
ID: 36508209
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
 

Author Comment

by:drezner7
ID: 36508228
djlinker: Thank you ..... thank you works great....
0
 

Author Closing Comment

by:drezner7
ID: 36508230
Thank you so much... i really appreciate it
0
 
LVL 4

Expert Comment

by:David Linker
ID: 36508239
Glad I could help.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question