I have written a program to select an Excel workbook and strip hours and
minutes from dates in columns in worksheets.

Columns and worksheets to be processed in a given workbook are designated by
parameters read from a ".txt" contol file.

Debug statements appear to show that changes have been applied to the right
cells in the right columns in the right worksheets of a workbook.

But later inspection of the output saved to a separate workbook in another
folder do not have any of the changes which appeared during the program run.
Input is not read only.

Can Sherlock Holmes please clue this Dr. Watson in on the mystery of the
non-persisting changes?

Code below has this structure:
cmdRun 'Processes a selected workbook
Point to sheet
Activate the sheet
DoFixInSheet 'Loop inside cmdRun Processes sheets in workbook
DoFixAColumn 'Loop inside DoFixInSheet changes worksheet
columns
' designated by records read from a
control file
Point to Next Sheet
Activate that sheet
Save the workbook 'Inspection of the saved workbook shows no changes?

Here below is simplified code:
'Simplified Code for problem where cells in worksheets appear to be changed,
' but when the saved workbook is inspected, no changes have been made?
Private xSheet As Integer
Private InBook As Excel.Workbook
'Private InSheet As Excel.Worksheet

Private Sub cmdRun_Click() 'cmdRun processes one workbook selected form a
list
xSheet = 1 'xSheet points to the current worksheet to be edited
Do While ... 'loop edits one worksheet after the other in selected
workbook
DoFixInSheet (xSheet) 'DoFixInSheet edits 1 worksheet in sel
workbook
If xSheet >= InBook.Worksheets.Count Then
blnEndSheets = True
End If
xSheet = xSheet + 1
Loop
strBookOutSpec = App.Path & "\Outputs\" & InBook.Name
'Changed data saved in a different folder
InBook.SaveAs strBookOutSpec 'Inspection of saved file shows content
'unchanged?
End Sub

Private Sub DoFixInSheet(ByRef xSheet As Integer)
On Error Resume Next
InBook.Sheets(xSheet).Activate
'Activate the worksheet pointed by xSheet
strBookName = InBook.Name
'Hold the name of the current workbook
strSheetName = InBook.Sheets(xSheet).Name
'Hold the name of the activated worksheet

strColumnName = ayDesignationColumn(xDesigRow)
'A separate table contains designations of columns to be edited

Do While ...
'debug.print "Fix Book Name = " & InBook.Name & "; " & _
"; Sheet; Name = " & Inbook.sheets(xSheet).Name & ";
For ...
Debug.Print InBook.Sheets(xSheet).Cells(...)
' This shows cells are changed in the activated worksheet
Next
Loop
End Sub
Thank you for kind assistance
--
Lamont Phemister

Archived from group: microsoft>public>vb>ole>automation