Having issue with code operating consistently...

[Cummins]

My program accesses a series of *.csv files and transfers the data of interest into an excel sheet. The data in column 4 of the csv file is used as the search reference. The data I am pulling starts in column 12. I have had some files with as many as 7 additional columns of data. I have two sections of code....one that stores each of the columns of data and another sheet where all the columns for each .csv file are summarized. I have watched the code run several times and the section with the summary code has the same problem each time. The Arrays A1() and A2() only pick up the first column of data. Array A3() picks up all of the columns.

The code below is the one I have a problem with (summarizes the data). The raw data extraction file is nearly the same. The Findata function is in another module that is accessing data that has been imported into a temporary excel file. My concern is that there is some issue with the continuous access of the file...somehow the code is getting "lost"?


'***********InputData and SUMMARIZE - This Routine put the data from the temp file into the batchrecord file.
Private Sub InputDataSum()
Dim SerchCol As String
Dim BatchDate As String
Dim BatchOpenDateTime As String
Dim BatchCloseDateTime As String
Dim SDCounts(2, 4, 4) As String
Dim SPCounts(1, 71) As String

Dim FName As String
Dim Data(1000, 255) As Double
Dim temp As Variant
Dim Buffer As String

Dim A1(100) As Integer
Dim A2(100) As Integer
Dim A3(100) As Integer
Dim A4(100, 50) As Integer
Dim A5(100, 50) As Integer
Dim a1sum As Integer
Dim a2sum As Integer
Dim a3sum As Integer
Dim a4sum(100) As Integer
Dim a5sum(100) As Integer

On Error Resume Next

'Sheets("SumData").Select
'Set printobj = ActiveWorkbook.ActiveSheet

'This section transfers multiple Sessions during one measurement into separate rows.

n = lastcol - 11

DataCol = 12
Row = RowCounts + t 'This line sets the first row to write data into.
printobjs.Cells(Row, 1).Value = FindData("sm_Sys_LotNum")
printobjs.Cells(Row, 2).Value = FindData("Record")
printobjs.Cells(Row, 3).Value = "B" + Mid(FileName, 2)
printobjs.Cells(Row, 4).Value = FindData("sm_Sys_RevisionNo")
printobjs.Cells(Row, 5).Value = FindData("sm_Sys_ProductName")
' printobjs.Cells(Row, 6).Value = FindData("sm_Sys_ContainerSize")
' printobjs.Cells(Row, 7).Value = FindData("sm_Sys_RegisterDay1")
printobjs.Cells(Row, 6).Value = Mid(FindData("sm_Sys_BatchOpenTime"), 1, 10)
printobjs.Cells(Row, 7).Value = Mid(FindData("sm_Sys_BatchCloseTime"), 1, 10)
' printobjs.Cells(Row, 10).Value = FindData("id_Data_HMSession")
' printobjs.Cells(Row, 11).Value = FindData("sm_Sys_OpenOperatorName")
' printobjs.Cells(Row, 12).Value = FindData("sm_Sys_ApproveOperatorName")
' printobjs.Cells(Row, 13).Value = FindData("sm_Sys_ApprovalTime")

'***** Spindle Count Data *****
For t = 1 To n
DataCol = 11 + t
A1(t) = CInt(FindData("im_Count_TotalSession"))
A2(t) = CInt(FindData("im_Count_AccSession"))
A3(t) = CInt(FindData("im_Count_RejSession"))

a1sum = a1sum + CInt(A1(t))
a2sum = a2sum + CInt(A2(t))
a3sum = a3sum + CInt(A3(t))

'This routine stores the spindle data into an array. There are 32 separate groups of data in each cell.
For i = 0 To 31
SPCounts(0, i) = Mid(FindData("sm_Data_EPISpindle1SD1"), 2 + i * 4, 4)
SPCounts(0, i + 32) = Mid(FindData("sm_Data_EPISpindle2SD1"), 2 + i * 4, 4)
SPCounts(0, i + 64) = Mid(FindData("sm_Data_EPISpindle3SD1"), 2 + i * 4, 4)
SPCounts(1, i) = Mid(FindData("sm_Data_EPISpindle1SD2"), 2 + i * 4, 4)
SPCounts(1, i + 32) = Mid(FindData("sm_Data_EPISpindle2SD2"), 2 + i * 4, 4)
SPCounts(1, i + 64) = Mid(FindData("sm_Data_EPISpindle3SD2"), 2 + i * 4, 4)
Next

'This routine writes the spindle def count to the file. The Hex data is converted here.

For i = 0 To 47
A4(t, i) = CInt(Val("&h" + SPCounts(0, i)))
A5(t, i) = CInt(Val("&h" + SPCounts(1, i)))


a4sum(i) = a4sum(i) + A4(t, i) 'SD1
a5sum(i) = a5sum(i) + A5(t, i) 'SD2
Next


Next

printobjs.Cells(Row, 8).Value = a1sum
printobjs.Cells(Row, 9).Value = a2sum
printobjs.Cells(Row, 10).Value = a3sum
For i = 0 To 47
printobjs.Cells(Row, 11 + i).Value = a4sum(i)
printobjs.Cells(Row, 59 + i).Value = a5sum(i)
Next
RowCounts = RowCounts + 1 'This line keeps count of the line number

Workbooks(TempBookName).Close SaveChanges:=False 'This line closes the Temp file which was opened in OpenDataFile


End Sub

 

[Cummins]

Hope someone can help with this...it is beyond bizzare. I watched this several times now. n = the number of columns of data. When n=4, as t cycles through, section A is accessing section B to pull the data out of the spreadsheet. When t = 3....A1(3) returns as zero. Not ""...zero. The value of "a" strangely is the SAME as it was when t =2. The same thing happens to A2(3) and b. HOWEVER, A3(3) and c get the correct value. Now, when I whatch Section B, the value for FindData is correct when t=3 for each pull. It is not feeding back the value...WHY? It happens the same way EVERY time I run it.





Section A:

For t = 1 To n

DataCol = 11 + t

A1(t) = CInt(FindData("im_Count_TotalSession"))

a = CInt(FindData("im_Count_TotalSession"))

A2(t) = CInt(FindData("im_Count_AccSession"))

b = CInt(FindData("im_Count_AccSession"))

A3(t) = CInt(FindData("im_Count_RejSession"))

c = CInt(FindData("im_Count_RejSession"))



a1sum = a1sum + CInt(A1(t))

a2sum = a2sum + CInt(A2(t))

a3sum = a3sum + CInt(A3(t))



Section B:

Public Function FindData(Data As String) As String

Dim RowNum As Integer

Dim Count As Integer



On Error Resume Next

Set dataobj = ActiveWorkbook.ActiveSheet

If Data <> "" Then

'Looks in this array of cells for the desired item which is stored in "Data"

dataobj.Range(Cells(1, 4), Cells(1000, 4)).Find(Data, LookIn:=xlFormulas, LookAt:=xlWhole).Activate

If Err.Number = 0 Then

FindData = dataobj.Cells(ActiveCell.Row, DataCol).Value



Else

FindData = "???"

End If

Else

FindData = ""

d = Err.Number

End If



End Function

 

[wigi]

Hope someone can help with this...

As mentioned a lot of times in other topics, PLEASE use

 tags. Then, all the code you posted will be readable to helpers and might generate a bigger attention for your topic.

A descriptive topic title is also acknowledged. Thanks.

 

[Cummins]

Sorry Wigi. I screwed up...late night on a weekend trying to solve a weird issue. I tried to update the post to include tags but couldn't see how. I re-posted by update to the first post and added tags. I hope this is acceptable.

 

[wigi]

I do not see any use of tags.

You should type:

then, paste your code

then type:

['/code]

WITHOUT the ' in front of the /

Thanks.

 

[Cummins]

OMG....Now I get it. Had to print this out so I can reference it. I am really amazed at how useful this forum is!