Strange problem with spreadsheet
 

Hello,

I have a VB6 application that parses each of 4 sheets an Excel workbook
looking for an "x" in column 1.
If an "x" is found the row is copied to the corresponding sheet in another
workbook, and the row is deleted.
The data in the sheets is added from a set of 4 text files every day. An
operator looks at the sheets and marks the rows which has been processed
with an "x"
This operation is run every morning by a Windows Scheduled Task.
This has been running now for over two years without any problems, until
last week when the application seems to not find any "x"es in the first
column in the last sheet.
The other three sheets are still processed correctly.
I have tried a lot of things, like cleaning out the offending sheet and
copying and pasting data from the lates copy of the workbook, no luck.
I got hold of a copy of the workbook from before the problem occurred,
cleaned it out and copied the curent data over, still no luck.
The only thing that works is to clean the workbook and start with no rows.
New rows added from text files and marked with "x"es are processed
correctly.
Does anyone have an idea what could be the cause of this strange behavior?
Any help would be appreciated.

Ragnar

Strange problem with spreadsheet
 

Hi Ragnar,

The only thing that works is to clean the workbook and start with no rows.
New rows added from text files and marked with "x"es are processed
correctly.
Does anyone have an idea what could be the cause of this strange behavior?
Any help would be appreciated.

What does the find code look like?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

Strange problem with spreadsheet
 

Hi Jan Karel,

This is a snippet of the code that checks for an "x" in column one, the
colun that has "Processed" as column header.

Dim oExcel As Excel.Application
Dim oArchiveExcelWorkBook As Excel.Workbook
Dim oCommonExcelWorkbook As Excel.Workbook
Dim oCurrentSheet As Excel.Worksheet
Dim oCurrentArchiveSheet As Excel.Worksheet
Dim oTemplSheet As Excel.Worksheet
Dim oFSO As Scripting.FileSystemObject
Dim tsExtractFile As Scripting.TextStream
Dim sLogFile As String
Dim sLogMsg As String
Dim vTargetRow As Variant
Dim lnTargetRow As Long
Dim lnRowIndex As Long
Dim sTargetRow As String
Dim lnColumnCount As Long
Dim lnMaxRows As Long
Dim lnMaxSheets As Long
Dim lnArchiveMaxRows As Long
Dim lnSourceRow As Long
Dim lnLastSourceRow As Long
Dim lnColumnIndex As Long
Dim sRecord As String

For lnRowIndex = 1 To lnLastSourceRow
If (oCurrentSheet.Cells(lnRowIndex, 1) = "Processed") Then
Else
If ((oCurrentSheet.Cells(lnRowIndex, 1) = "x") _
Or (oCurrentSheet.Cells(lnRowIndex, 1) = "X")) Then
lnSourceRow = lnRowIndex
lnTargetRow = lnTargetRow + 1
sTargetRow = Trim(Str(lnTargetRow))
' --- Copy and paste a row from the current Excel book and paste
it --------
' --- into the archive
--------
oCurrentSheet.Activate ''' No need to activate if explicit
qualifier is used?
oCurrentSheet.Range(Cells(lnSourceRow, 1), Cells(lnSourceRow,
lnColumnCount + 1)).Copy
oCurrentArchiveSheet.Activate
' Start with column B, to allow for the new date column in the
archive sheet.
oCurrentArchiveSheet.Range("B" & sTargetRow).Select
oCurrentArchiveSheet.Range("B" & sTargetRow).Activate
oCurrentArchiveSheet.Paste
' Insert today's date in the first cell of target row.
oCurrentArchiveSheet.Cells(CLng(sTargetRow), 1).Value = CStr(Date)
sLogMsg = CStr(Now) & " | ProcessExtractFiles function | " _
& " Copied row " & Str(lnRowIndex) & " to archive sheet"
tsLogFile.WriteLine (sLogMsg)
oArchiveExcelWorkBook.Save
oCurrentSheet.Cells(lnRowIndex, 1) = "r"
End If
End If
Next lnRowIndex

Ragnar

Strange problem with spreadsheet
 

Never mind, the weird problem was due to an entry error.
The "x"es were followed by one or more blanks, in spite of instructions to
not enter any blanks.
I have updated the code to ignore blanks, which is what I should have done
to begin with (:-)).

Ragnar

"Ragnar Midtskogen" wrote in message
...
Hi Jan Karel,

This is a snippet of the code that checks for an "x" in column one, the
colun that has "Processed" as column header.

Dim oExcel As Excel.Application
Dim oArchiveExcelWorkBook As Excel.Workbook
Dim oCommonExcelWorkbook As Excel.Workbook
Dim oCurrentSheet As Excel.Worksheet
Dim oCurrentArchiveSheet As Excel.Worksheet
Dim oTemplSheet As Excel.Worksheet
Dim oFSO As Scripting.FileSystemObject
Dim tsExtractFile As Scripting.TextStream
Dim sLogFile As String
Dim sLogMsg As String
Dim vTargetRow As Variant
Dim lnTargetRow As Long
Dim lnRowIndex As Long
Dim sTargetRow As String
Dim lnColumnCount As Long
Dim lnMaxRows As Long
Dim lnMaxSheets As Long
Dim lnArchiveMaxRows As Long
Dim lnSourceRow As Long
Dim lnLastSourceRow As Long
Dim lnColumnIndex As Long
Dim sRecord As String

For lnRowIndex = 1 To lnLastSourceRow
If (oCurrentSheet.Cells(lnRowIndex, 1) = "Processed") Then
Else
If ((oCurrentSheet.Cells(lnRowIndex, 1) = "x") _
Or (oCurrentSheet.Cells(lnRowIndex, 1) = "X")) Then
lnSourceRow = lnRowIndex
lnTargetRow = lnTargetRow + 1
sTargetRow = Trim(Str(lnTargetRow))
' --- Copy and paste a row from the current Excel book and paste
it --------
' --- into the archive --------
oCurrentSheet.Activate ''' No need to activate if explicit
qualifier is used?
oCurrentSheet.Range(Cells(lnSourceRow, 1), Cells(lnSourceRow,
lnColumnCount + 1)).Copy
oCurrentArchiveSheet.Activate
' Start with column B, to allow for the new date column in the
archive sheet.
oCurrentArchiveSheet.Range("B" & sTargetRow).Select
oCurrentArchiveSheet.Range("B" & sTargetRow).Activate
oCurrentArchiveSheet.Paste
' Insert today's date in the first cell of target row.
oCurrentArchiveSheet.Cells(CLng(sTargetRow), 1).Value = CStr(Date)
sLogMsg = CStr(Now) & " | ProcessExtractFiles function | " _
& " Copied row " & Str(lnRowIndex) & " to archive sheet"
tsLogFile.WriteLine (sLogMsg)
oArchiveExcelWorkBook.Save
oCurrentSheet.Cells(lnRowIndex, 1) = "r"
End If
End If
Next lnRowIndex

Ragnar

Strange problem with spreadsheet
 

Hi Ragnar,

Never mind, the weird problem was due to an entry error.
The "x"es were followed by one or more blanks, in spite of instructions to
not enter any blanks.
I have updated the code to ignore blanks, which is what I should have done
to begin with (:-)).

Thanks for letting us know!

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com