Reading Excel Spreadsheet

does it come up with any errors?

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?

I believe it is failing when it begins to read the excel spreadsheet..

yes post the code on here

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

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       

Here is the test data
Test-Data.xlsx

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

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

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

djlinker: Thank you ..... thank you works great....

Thank you so much... i really appreciate it

Glad I could help.