Hi everyone,
there is a function/macro that creates a txt file when the workbook is closed
where it is written if the file has been modified or not modified?
xam
Hi everyone,
there is a function/macro that creates a txt file when the workbook is closed
where it is written if the file has been modified or not modified?
xam
Last edited by xam99; 08-27-2019 at 02:44 PM.
Hi and welcome
could you be more specific please?
What function/macro are you talking about?
Please post code/sheet on the forum
Thanks
I don't have any prepared workbook.
I ask this:
close excel and in txt if I don't modify anything it must be "unmodified"
close excel and in txt if I change something it must be "modified"
In the web I found this but I don't know how to change it
xamPrivate Sub Workbook_BeforeClose(Cancel As Boolean) Open ThisWorkbook.Path & "\usage.log" For Append As #1 Print #1, Application.UserName, Now Close #1 End Sub
Last edited by xam99; 08-28-2019 at 04:14 AM.
The workbook
Hi there,
See if the following code does what you need:
Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sStatus As String Dim bSaved As Boolean If ThisWorkbook.Saved = False Then sStatus = "Modified" Else: sStatus = "Unmodified" End If Open ThisWorkbook.Path & "\usage.log" For Append As #1 Print #1, Application.UserName, Now, sStatus Close #1 End Sub
Hope this helps - please let me know how you get on.
Regards,
Greg M
I GregM
Your macro almost work.
this does not work:
open excel and I change something
closed excel and in the notice "do you want to save ...." i choose no
in tx/log It must be written "Unmodified", now it's always written "Modified".
I hope I explained myself
xam
Hey max_max !
Instead of creating duplicate threads, cross-posting and using multiple user ID,
it would be so smart to start a thread with a complete crystal clear explanation & the expected attachment accordingly …
You'll see how much time you'll save instead of creating again & again the same badly explained thread, trust me !
Last edited by Marc L; 08-28-2019 at 09:17 AM. Reason: typo …
Hi again,
You don't say anything about not SAVING changes! This is what Marc L is talking about when he says:
close excel and in txt if I change something it must be "modified"
a thread with a complete crystal clear explanation
Take a look at the attached workbook and see if it does what you need. It uses the following code in the "ThisWorkbook" VBA CodeModule:
Option Explicit '========================================================================================= '========================================================================================= Private Sub Workbook_Open() Open ThisWorkbook.Path & "\usage.log" For Append As #1 Print #1, Application.UserName, Now, "open" Close #1 End Sub '========================================================================================= '========================================================================================= Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim iUserResponse As Integer Dim sMessage As String Dim sStatus As String Dim bSaved As Boolean If ThisWorkbook.Saved = False Then sMessage = "Want to save your changes to" & _ vbLf & _ "'" & ThisWorkbook.Name & "'?" iUserResponse = MsgBox(sMessage, vbYesNoCancel + vbExclamation + _ vbDefaultButton1) If iUserResponse = vbYes Then ThisWorkbook.Save sStatus = "Modified" Cancel = False ElseIf iUserResponse = vbNo Then ThisWorkbook.Saved = True sStatus = "Unmodified" Cancel = False Else: Cancel = True End If Else: sStatus = "Unmodified" Cancel = False End If If Cancel = False Then Open ThisWorkbook.Path & "\usage.log" For Append As #1 Print #1, Application.UserName, Now, "closed", sStatus Print #1, "----------------------------------------------------------------------------------" Close #1 End If End Sub
Hope this helps - please let me know how you get on.
Regards,
Greg M
Last edited by Greg M; 08-28-2019 at 09:43 AM. Reason: Code changed and updated workbook attached
Significant revision of Post #8
Corrections made there instead of creating another large post here.
Greg M
Nothing arrogant to advice OP how he can avoid to waste time !
I can add the link for the other forum but for this one
as you can easily find each thread from the members list then from each account …
None of the members you suggested have posted lately according to logs
Why not add the link? Or perhaps apply as mod, so you can help us instead of wasting our time??
Wasn't lately but earlier, 4th thread for the same subject with few days, 3 here and one on another forum.
Wasn't neither to waste your time but just a friendly advice to OP (xam = max = …) to avoid to waste his time.
But if here this upsets you, you can remove all my posts from this thread …
Hi again xam99,
Many thanks for the Reputation increase - much appreciated!
Glad I was able to help.
Best regards,
Greg M
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks