Sleeper: Macro Code for Save but no Dialog Boxes

Hi all. I am new here

I am hoping to have a piece of Excel Macro Code solved here please.


The current Code is:


The Cut Action changes the log file opened by Excel.

I need to Change the altered Log File when Save and these Dialog Boxes keep apprearing.

The Problem is here:

Code:

---

Windows("Test_script.log").Activate
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges:=False
'CLOSES TXT FILE ***_WHITHOUT SAVING_***
' and NO DIALOG BOXES.
'The Problem Ends here.
I need to change:
ActiveWorkbook.Close SaveChanges:=False
to :
ActiveWorkbook.Close SaveChanges:=True
' but the dialog boxes show.

---

Complete Recorded Macro Sequence for a "bigger picture" is here:

Code:

---

Sub MainTest1()
'opening Log FILE
ChDir "C:\Program Files\Program\Logs"
Workbooks.OpenText Filename:= _
"C:\Program Files\Program\Logs\Test.log", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, _
Comma:=True, Space:=True, Other:=True, OtherChar:="-", FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1))
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Columns.AutoFit
Range("A1").Select
Cells.Select
Selection.Copy
'transfering at this point-
Windows("TestWorkBook1").Activate
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
'transfer done.
Windows("Test.log").Activate
'Changes to code need to be done here.
'old
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Close SaveChanges:=False
'closes file but not to Save. Ok for testing.
'new,
'Need to Save/ Close file and NO dialog boxes
End Sub

---

There is a new log file being updated from every 30 second at times to every 1 minute and at random intervals to.

This makes it very difficult to have a normal life when logging goes on for about 10 hours per day,7 Days per week and these dialog boxes keep appearing.

Quote:

---

Originally Posted by BakersDozen

Hi all. I am new here and I have a sense of humour !

---

You need one with a cricket team like yours :devil:

Quote:

---

Originally Posted by BakersDozen

I need to Change the altered Log File when Save and these Dialog Boxes keep apprearing.

---

It is not obvious to me what dialog boxes you mean but try

Code:

---

Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True

---

Hi BakersDozen,

Welcome to VBAX. I hope you don't mind but I've edited your post to include VBA tags (click on my sig to see how). This helps makes your code a bit more readable for others.

Regards,
John :hi:

I Re-Recorded a new Macro and this time by clicking
"Yes" to "Save Changes" it added
the File Path when closing in the Recorded Macro Code.
There are now 2 mouse clicks.

Code:

---

'Macro Continued,
Windows("DummyLogSmall.txt").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
'-- OK to here.
Application.DisplayAlerts = False                    'added, not recorded.
ActiveWorkbook.SaveAs Filename:= _
"C:\Program Files\Program\Logs\DummyLog.txt", FileFormat:=xlText, _
CreateBackup:=False
Application.DisplayAlerts = False                'added, not recorded.
ActiveWorkbook.Close SaveChanges:=True  'added, not recorded.
Application.DisplayAlerts = False                  'added, not recorded.
End Sub

---

If anyone want me to send a copy of Workbook with the macro and copy of log file to test themselves let me know.

Just 2 mouse clicks to eliminate !

Thanks

Code:

---

Private Sub CommandButton1_Click()
' Runs Macro to save the Target Sheet.
ActiveWorkbook.SaveAs Filename:=Range("N2"), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

---

I am no VBA expert but this might do the trick for you. This is what I am using in the project that I am working on now. The user clicks a button on the page and the above macro runs. N2 contains the following formula:

Code:

---

=CONCATENATE("G:\RD\FAB\CMP\Target Sheets\", E2, " - ", E3, ".xls")

---

This is working for me, the user doesn't have to do anything but press the button.

I have uploaded a reivised test file for anyone to try and solve this
minor glitch with a backup incase anyone messes up.

It's set up to work from C:\

The code uses a string reference to Save/Close the log.txt file, the same method when activating the macro via an external program to Open the log.txt file.

Still does not seem to work yet. The section of code will high-light in yellow.
Much appreciiated if this could be solved.
Thanks in advance.