CODEWITHSUNDEEP
vbaexcel
sony1118
sony1118

Reputation: 1

Parsing a Number

Here is my problem: I am parsing a xml using excel VBA. I open the XML file as regular excel file, Then I go to column which is of my interest.

Cells in that column are 16 character.

So I am parsing each of those characters using Left and Right functions and saving them in a new file.

Mostly those 16 characters are Numeric and sometimes Alpha Character.

example 1111000011110000 or 1111YYYY00001111

when it parses 1111YYYY00001111 -- I get correct output,

like in new file Cell(1,1) =1 Cell(1,1) =1 Cell(1,2) =1 Cell(1,3) =1 Cell(1,4) =1 Cell(1,5) =Y and so on

But When I parse 1111000011110000 -- I dont get the correct output

I get Cell(1,1) =1 Cell(1,2) =. Cell(1,3) =1 Cell(1,4) =1 Cell(1,5) =1 Cell(1,6) =0

I tried to put NumberFormat="0" for column which I was parsing, but still i get the same output.

I also tried to put the debug variables and stored them as strings, Double Integer but I could not get pass it.

Here is my code:

Workbooks.Add
strip_concat = ActiveWorkbook.Name
Workbooks(strip_concat).Sheets(1).Cells(1, 1) = "Subid"
Workbooks(strip_concat).Sheets(1).Cells(1, 2) = "Strip#"
Workbooks(strip_concat).Sheets(1).Cells(1, 3) = "X"
Workbooks(strip_concat).Sheets(1).Cells(1, 4) = "Y"
Workbooks(strip_concat).Sheets(1).Cells(1, 5) = "Reject Code"
Workbooks(strip_concat).Sheets(1).Cells(1, 6) = "X-Y"
Workbooks.OpenXML Filename:=xml_file 
stripfile = ActiveWorkbook.Name
 For C = 3 To strip_col - 1
     r = 1
       Do
         Y = r
         x = C - 2
         Workbooks(strip_concat).Sheets(1).Cells(A, 3) = x
         Workbooks(strip_concat).Sheets(1).Cells(A, 4) = Y
         Workbooks(strip_concat).Sheets(1).Cells(A, 6).NumberFormat = "@"
         Workbooks(strip_concat).Sheets(1).Cells(A, 6) = x & "-" & Y
         Workbooks(strip_concat).Sheets(1).Cells(A, 1) =     Workbooks(stripfile).Sheets(1).Cells(C, Amkor_id_col)
         Workbooks(strip_concat).Sheets(1).Cells(A, 2) = ExtractElement(Workbooks(stripfile).Sheets(1).Cells(C, Strip_num_col), 2, ".")
         Workbooks(strip_concat).Sheets(1).Cells(A, 5) = Right(Left(Workbooks(stripfile).Sheets(1).Cells(C, Defect_data_col + i), r), 1)
         r = r + 1
         A = A + 1

         Loop Until (r > 16)

         If ((x Mod 13) = 0) Then i = i + 1
     Next C

from xml file 1131111111111111

Upvotes: 0

Views: 131

Answers (1)

sony1118
sony1118

Reputation: 1

I got it working.. This is what I did

Dim x as variant
x = (Workbooks(stripfile).Sheets(1).Cells(C, Defect_data_col + i).Text
Workbooks(strip_concat).Sheets(1).Cells(A, 5) = Right(Left(x,r),1

Upvotes: 0

Related Questions