Solved

Reading Excel Spreadsheet

Posted on 2011-09-08
14
284 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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

831 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