Hopefully someone out there can help with a macro problem I am having in '07.
The macro opens a workbook and the workbooks that it is linked to so the form can udate. After the update a copy is saved with the new information which can then be viewed by all users and stored for archive reasons. A new file is created each time that the macro is run so this is not overwritting the same file over and over again.
The problem is that the saved workbook still contains all of the links that were in the original file. Does anyone know a way that I can save the file as values only without the links?
Application.Visible = False
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\Communication Sheet.xlsx", _
UpdateLinks:=3
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\fi ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\fi unshipped.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\retail ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\retail unshipped.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\tcs ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\tcs unshipped.xls"
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWorkbook.SaveCopyAs "\\Rpf-baltimore\shared\PRODUCTION\Production Reporting\communication sheet\CS_" & Format(Date, "mmddyy") & "_" & Format(Time, "hhmm") & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False
ActiveWindow.Close
Application.Quit
The macro opens a workbook and the workbooks that it is linked to so the form can udate. After the update a copy is saved with the new information which can then be viewed by all users and stored for archive reasons. A new file is created each time that the macro is run so this is not overwritting the same file over and over again.
The problem is that the saved workbook still contains all of the links that were in the original file. Does anyone know a way that I can save the file as values only without the links?
Application.Visible = False
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\Communication Sheet.xlsx", _
UpdateLinks:=3
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\fi ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\fi unshipped.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\retail ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\retail unshipped.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\tcs ready to sort.xls"
Workbooks.Open Filename:= _
"\\nts_nman_bal1\SIApps\Output\Update Sheet\tcs unshipped.xls"
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ActiveWindow.Close
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWorkbook.SaveCopyAs "\\Rpf-baltimore\shared\PRODUCTION\Production Reporting\communication sheet\CS_" & Format(Date, "mmddyy") & "_" & Format(Time, "hhmm") & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False
ActiveWindow.Close
Application.Quit