Get rid of SaveAs prompts

[Bongard]

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

 

[Simon]

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

 

[Bongard]

It prompts me here:

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

 

[Simon]

It prompts me here:

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