Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save Prompts | Excel Worksheet Functions | |||
Saving Prompts | Excel Worksheet Functions | |||
Prompts | Excel Programming | |||
Prompts | Excel Discussion (Misc queries) | |||
Save without prompts | Excel Programming |