Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange Problem | Excel Programming | |||
Strange Problem | Excel Discussion (Misc queries) | |||
VBA & API - Strange problem using | Excel Programming | |||
Strange problem: spreadsheet locked when macro is stopped | Excel Programming | |||
Strange problem | Excel Programming |