automate the macro to reponse to prompts

[daniel chen]

I created the following Macro to convert a workbook to a new version
workbook.
In the course of execution, I have to response to 3 prompts.
1) Do you want to save the changes you make to 'WorkbookA.xls' ? --
No
2) There is a large amount of Information on the Clipboard. Do you
want to be able to paste the information into another program later ? -- No
3) A file named 'c:\test\WorkbookA' already exists in the location. Do
you want to replace it ? -- Yes
How do I automate the macro to these prompts ?

Sub WorkbookVersionConversion()
Workbooks.Open Filename:="C:\test\NewVersionWorkbook.xls",
UpdateLinks:=0
' Copy data from 'WorkbookA.xls' and paste data to
'NewVersionWorkbook.xls'
Workbooks.Open Filename:="C:\test\WorkbookA.xls", UpdateLinks:=0
Columns("A:E").Select
Selection.Copy
Windows("NewVersionWorkbook.xls").Activate
Columns("A:E").Select
ActiveSheet.Paste
' Close 'WorkbookA.xls' without saving the changes and without saving the
data on the Clipboard.
Windows("WorkbookA.xls").Activate
ActiveWindow.Close
' Replace 'WorkbookA.xls' by saving 'NewVersionWorkbook.xls' as
'WorkbookA.xls'
Windows("NewVersionWorkbook.xls").Activate
ChDir "C:\test"
ActiveWorkbook.SaveAs Filename:="C:\test\WorkbookA.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
End Sub

 

[Bob Phillips]

Hi Daniel,

1) Workbooks("WorkbookA.xls").Close SaveChanges:=False

2 & 3) Precede code with Application.DisplayAlerts = False, and reset to
True at end

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)