Save Results Based On Excel Data to Text File (Log Report)

[kjv1611]

This may be something simple to accomplish, but I've not tried before, to date, as far as I can remember.

I currently typed up a short macro for finding cells that have numeric values instead of formulas, when they should have formulas. The reason was that occasionally, I'll write some formulas for someone, to make things a little easier, and less error prone; then low and behold, they overwrite one or two!

So I end up, ever so often, going behind people, and looking for deleted formulas, as that seems to cause 99% of all problems in my experience with final summaries.

So, what I did in the first couple times I used it was just to Debug.Print the Cell location and Formula values in the Immediate panel of the VBA window, but I'd prefer to just save that to sort of a "log file" that I could just open, or anyone could open, for that matter, and maybe have it open up after creation (if it gets created). I just think this would be better for the particular application than a message box.

Does anyone have any suggestions/references for this sort of idea?

I likely will not be able to do anything with this for at least a week and a half, possibly 2 to 3 weeks, but I wanted to go ahead and post the question before I end up forgetting, and I guess it would not hurt to get some ideas churning in the mean time.

More than likely, I will not have any access to the internet all next week, or at least through Thursday. I probably will not even turn on a PC often if at all during that time. So, that will explain why it will take so long for me to respond to any posts here. I will eventually get back here and check on the thread, and post feedback ASAP, once I'm back to the normal grind.

Thanks in advance for any advice/references/information/examples/whatever.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

Why not PREVENT this from happening.

1. Lock the cells containing formulas and PROTECT the sheet

or

2. In the Worksheet_Change event, trap changes to those ranges and immediately replace the formula.

Skip,

_[red][/red]

 

[CautionMP]

kjv1611,
Here is a rough idea, I built it around a Class Module because it seemed like fun. Module1 demonstrates how the class would be used in your current routine.

Class1

Private intFile As Integer
Private strFilename As String
Private blnFileOpen As Boolean

Private Sub Class_Initialize()
intFile = FreeFile
End Sub

Public Property Get OutputFile() As String
OutputFile = strFilename
End Property

Public Property Let OutputFile(ByVal NewFilename As String)
strFilename = NewFilename
End Property

Public Sub Open_File()
If Len(Trim(strFilename)) > 0 Then
  If Len(Trim(Dir(strFilename))) > 0 Then
    If MsgBox("File exists, do you want to overwrite?", vbYesNo, "Confirm overwrite") = vbNo Then
      Exit Sub
    End If
  End If
  Open strFilename For Output As #intFile
  blnFileOpen = True
End If
End Sub

Public Sub Write_Line(DataToWrite As String)
If blnFileOpen Then
  Print #intFile, DataToWrite
End If
End Sub

Public Sub Close_File()
If blnFileOpen Then
  Close intFile
  blnFileOpen = False
End If
End Sub

Private Sub Class_Terminate()
On Error Resume Next
If blnFileOpen Then
  Close intFile
End If
End Sub

Module1

Sub testclass()
On Error GoTo testclass_Error
Dim OutputClass As New Class1
Dim dteCounter As Date

'Initialize the class
With OutputClass
  'Store Output.txt in the same directory as the current file
  .OutputFile = ThisWorkbook.Path & "\Output.txt"
  .Open_File
End With

'Write some junk data to the file
For dteCounter = Date To Date + 7
  'Something like this would replace your Debug.Print statement
  OutputClass.Write_Line dteCounter & " " & Format$(dteCounter, "mm/dd/yyyy")
Next dteCounter

testclass_Exit:
'Close the file
OutputClass.Close_File
Set OutputClass = Nothing
Exit Sub

testclass_Error:
Debug.Print Now, "Module1.testclass", Err.Number, Err.Description
Resume testclass_Exit
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[kjv1611]

Skip,

1. The boss don't like this one, and the boss is USUALLY the one doing the edits that end up deleting formulas!

2. This method might just work - especially with putting it in a template. This idea is a must try!

CautionMP,

I will have a look at that in detail when I get the time. Probably by next week, or Lord willing, maybe it'll be near the end of this week.

I'll be sure to post back with any progress, and what I end up going with. Thanks for the replies so far.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

You could have a hidden sheet.

In the data sheet...

in the Workbook_SelectionChange event, write the selection FORUMLAS to the hidden sheet (same rows/columns)

in the Workbook_Change event, if the value in the hidden sheet is a FORMULA, then replace it. Don't forget to set EnableEvents FALSE at the beginning of the write-back process and TRUE when you're done.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim t As Range
    For Each t In Target
       If Left(Sheet2.Cells(t.Row, t.Column).Formula, 1) = "=" Then
            Application.EnableEvents = False
            t.Formula = Sheet2.Cells(t.Row, t.Column).Formula
            Application.EnableEvents = True
       End If
    Next
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim t As Range
    For Each t In Target
        If Left(t.Formula, 1) = "=" Then
            Sheet2.Cells(t.Row, t.Column).Formula = t.Formula
        End If
    Next
End Sub

Skip,

_[red][/red]

 

[kjv1611]

Will this work correctly with Array Formulas as well? I forgot to mention that part.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Take a look at the FormulaArray property. I have not used this. Some versions require R1C1 notation.

Skip,

_[red][/red]

 

[kjv1611]

Okay, I'll have a look.

I'm using Excel 2003, by the way.

I saw the R1C1 notation mentioned in another thread. I'm going to have to look that up, as I've not the slightest to what that is.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

BTW, the HasFormula property is a cleaner test than the Left(t.Formula, 1) in my code.

Skip,

_[red][/red]