Solved

Reading Excel Spreadsheet

Posted on 2011-09-08
14
282 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
  • 7
  • 5
  • 2
14 Comments
 
LVL 14

Expert Comment

by:brendanmeyer
Comment Utility
does it come up with any errors?
0
 

Author Comment

by:drezner7
Comment Utility
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
Comment Utility
I believe it is failing when it begins to read the excel spreadsheet..
0
 
LVL 14

Expert Comment

by:brendanmeyer
Comment Utility
yes post the code on here
0
 
LVL 4

Expert Comment

by:David Linker
Comment Utility
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
Comment Utility
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
Comment Utility
Here is the test data
Test-Data.xlsx
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:David Linker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
djlinker: Thank you ..... thank you works great....
0
 

Author Closing Comment

by:drezner7
Comment Utility
Thank you so much... i really appreciate it
0
 
LVL 4

Expert Comment

by:David Linker
Comment Utility
Glad I could help.  
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now