Get rid of SaveAs prompts
 

Hi, I have a code loop that is looping through and refreshing data in
some workbooks (typically around 400) saving the work book and moving
on to the next. However, just about every morning I am getting a
SaveAs prompt for one or more of the workbooks. Here is my code

Dim i As Integer
Dim MyTopPath(3) As String

MyTopPath(1) = "\\lbprds0262\invest\Equity Research\Tear Sheets
\Person1 - Pharma, Bio and MedTech"
MyTopPath(2) = "\\lbprds0262\invest\Equity Research\Coverage Lists
\Person2 - Leisure and Staples\"
MyTopPath(3) = "\\lbprds0262\invest\Equity Research\Coverage Lists
\Person1 - Pharma, Bio and MedTech\"

Application.DisplayAlerts = False
Application.ScreenUpdating = False


Workbooks.Open MyTopPath(2) & "\Some Person - Leisure Gaming
Lodging and Staples.xls"
ExecuteExcel4Macro ("FDSFORCERECALC(FALSE)")
ActiveWorkbook.Close SaveChanges:=True

'This is the search - use a with statement
On Error GoTo SheetError
With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.LookIn = MyTopPath(1)
.FileType = msoFileTypeExcelWorkbooks
.Execute

'This is the Update
For i = 1 To .FoundFiles.Count
Application.StatusBar = "Updating ticker " & i & " of
" & .FoundFiles.Count
Workbooks.Open .FoundFiles(i)
ExecuteExcel4Macro ("FDSFORCERECALC(FALSE)")
ActiveWorkbook.Close SaveChanges:=True '(It continues
to prompt me here on a couple workbooks)
Next i
End With

'This is the print out
Workbooks.Open MyTopPath(3) & "King Coverage List.xls"

Application.ActivePrinter = "\\VSPRINT503\COLOR17 on Ne06:"
ActiveWorkbook.PrintOut Copies:=1, Preview:=False,
ActivePrinter:= _
"\\VSPRINT503\COLOR17 on Ne05:", Collate:=True

'Application.ActivePrinter = "\\VSPRINT505\PR7A on Ne06:" This
code also changed
Application.ActivePrinter = "\\VSPRINT505\PR7A on Ne07:"

ActiveWorkbook.Close SaveChanges:=False

'This will quit excel
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Quit

CloseUpdate:
Exit Sub

SheetError:
Resume Next

End Sub

Get rid of SaveAs prompts
 

On Aug 14, 2:33*pm, Bongard wrote:
Hi, I have a code loop that is looping through and refreshing data in
some workbooks (typically around 400) saving the work book and moving
on to the next. However, just about every morning I am getting a
SaveAs prompt for one or more of the workbooks. Here is my code

* *Dim i As Integer
* * Dim MyTopPath(3) As String

* * MyTopPath(1) = "\\lbprds0262\invest\Equity Research\Tear Sheets
\Person1 - Pharma, Bio and MedTech"
* * MyTopPath(2) = "\\lbprds0262\invest\Equity Research\Coverage Lists
\Person2 - Leisure and Staples\"
* * MyTopPath(3) = "\\lbprds0262\invest\Equity Research\Coverage Lists
\Person1 - Pharma, Bio and MedTech\"

* * Application.DisplayAlerts = False
* * Application.ScreenUpdating = False

* * * * Workbooks.Open MyTopPath(2) & "\Some Person - Leisure Gaming
Lodging and Staples.xls"
* * * * ExecuteExcel4Macro ("FDSFORCERECALC(FALSE)")
* * * * ActiveWorkbook.Close SaveChanges:=True

'This is the search - use a with statement
* * * * On Error GoTo SheetError
* * * * With Application.FileSearch
* * * * * * .NewSearch
* * * * * * .SearchSubFolders = True
* * * * * * .LookIn = MyTopPath(1)
* * * * * * .FileType = msoFileTypeExcelWorkbooks
* * * * * * .Execute

'This is the Update
* * * * * * For i = 1 To .FoundFiles.Count
* * * * * * * * Application.StatusBar = "Updating ticker " & i & " of
" & .FoundFiles.Count
* * * * * * * * Workbooks.Open .FoundFiles(i)
* * * * * * * * ExecuteExcel4Macro ("FDSFORCERECALC(FALSE)")
* * * * * * * * ActiveWorkbook.Close SaveChanges:=True '(It continues
to prompt me here on a couple workbooks)
* * * * * * Next i
* * * * End With

'This is the print out
* * * * Workbooks.Open MyTopPath(3) & "King Coverage List.xls"

* * * * Application.ActivePrinter = "\\VSPRINT503\COLOR17 on Ne06:"
* * * * ActiveWorkbook.PrintOut Copies:=1, Preview:=False,
ActivePrinter:= _
* * * * * * "\\VSPRINT503\COLOR17 on Ne05:", Collate:=True

* * * * 'Application.ActivePrinter = "\\VSPRINT505\PR7A on Ne06:" This
code also changed
* * * * Application.ActivePrinter = "\\VSPRINT505\PR7A on Ne07:"

* * * * ActiveWorkbook.Close SaveChanges:=False

'This will quit excel
* * * * Application.DisplayAlerts = True
* * * * Application.ScreenUpdating = True
* * * * Application.Quit

CloseUpdate:
* * Exit Sub

SheetError:
* * Resume Next

End Sub

On which lines do the Save As occur? Do you have any date or time
functions in the worksheet(s)?

How about Close.SaveAs := False

Get rid of SaveAs prompts
 

It prompts me he

ActiveWorkbook.Close SaveChanges:=True '(It continues
to prompt me here on a couple workbooks)

I don't know if anyplace where I have date or time functions in the
workbooks.

Thanks,
Brian

Get rid of SaveAs prompts
 

On Aug 14, 2:51*pm, Bongard wrote:
It prompts me he

ActiveWorkbook.Close SaveChanges:=True '(It continues
to prompt me here on a couple workbooks)

I don't know if anyplace where I have date or time functions in the
workbooks.

Thanks,
Brian

Change the Save Changes to False.

If you want to save it as the same workbook and the same name use
ActiveWorkbook.Save