automate the macro to reponse to prompts

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


0
danchen (87)
2/7/2004 5:23:00 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
470 Views

Similar Articles

[PageSpeed] 54

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)

"daniel chen" <danchen@worldnet.att.net> wrote in message
news:UC_Ub.64029$6O4.1906191@bgtnsc04-news.ops.worldnet.att.net...
> 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
>
>


0
bob.phillips1 (6510)
2/7/2004 8:32:17 AM
Reply:

Similar Artilces:

CRM and Fax automation question
Hi, I'm looking for a good or better fax server that can be easily and seamlessly integrate with MS CRM 3.0 or 4.0. I'm thinking of RightFAX or GFIFax... or even if I install these fax server in my environment, do I still need to do programming and customizations, such as post callout when a new Fax activity is created? Please advise :) You immediate response is appreciated. "Benjamin Fallar III" <fallar@pldtdsl.net> wrote in message news:3484C69D-B193-4B48-86A4-A4529D9DE4C6@microsoft.com... > I'm thinking of RightFAX or GFIFax... or even if I install ...

Automation
Hello Everyone, Recently our IT group installed service pack 3 on our computers. I wrote some code in word that automatically attaches it self to an outlook email and sends it out. I'de like to disable two windows that i didn't get before they installed service pack 3 there are not titles so here is a description of the boxes: 1 - A program is trying to access email addresses you have store in outlook. do you want to allow this? then it asks for "allow access for" (1,5,10 minutes) 2 - then i get a similar one that just asks you if you're sure you want to send ...

Provisioning/Automation tools for Exchange??
I have been asked to find some tools possible of automoating mailbox moves to specific databases/storage groups based on the department of a user for instance. Let's say User1 is part of HR and has transferred to Marketing. All the HR mailboxes reside on SG1 DB1 and all Marketing reside on SG1 DB2. Is there any software anyone knows of that can read the department attribute from AD and then move the mailbox according to defined rules you put in place. Any and all help appreciated! Mike In a couple of situations, organizations I have worked in have talked about having something li...

I wonder how to use the Macro of Sidewinder X4 and X6
1. About the instruction to command which I set. Such as I'd like to press botton A Then press botton B. Is it possible to set the delay of time in pressing botton A to B? 1. Such as 0.25-0.28 seconds. If it is possible to,set it, how long can I set for the delay? 2. In another case, Botton S1, I set it to Repeat. Can I set the limit time of repeat? Such as I set S1 to repeat every 30 minutes.Then the repeat programme will be repeat every 30 minutes, won't it? 3. In order to set the repeat Macro mode, do I have to presss and hold the key for awile or just press and l...

Macro Help #18
Well I think this is a macro. I have searched my excel book and i can not seem to figure out what to do. I want to write something that if this statement is true, then the font color changes. But here is the potential problem. THe location of the text will change. So i need it to search for this text and then change the color. Does that make sense? Is this possible? If this can be done it will really help me. Thanks in advance. This is exactly what i am trying to do. You type in a persons name into cell a1. you also type a name into cell a2. These names will transfer over ...

Macro to run without spreadsheet being open
Is there a way to et a macro to run without the sheet being open? Within my macro there is a timer of 00:00:01 to run he macro, but this will only work with the spreadsheet open. Is there a way to run the macro overnight without the sheet being open? See http://www.cpearson.com/excel/OnTime.aspx -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Dan Wood" <DanWood@discussions.microsoft.com> wrote in message news:E4BF08BB-6B56-46EB-A1E3-DD312AF8DB6E@microsoft.com... > Is there a way to et a macro to run without the sheet being open? Within my ...

Is it Possible to Automate a Graph?
I have a sheet that has data in weekly fashion in the first and second rows. (Row 1, week: Row 2, data.) Is it possible to have a graph populate off of the information in rows 1 and 2 that automatically includes data for new weeks as they are entered in to the sheet? For example, I want to create a graph for weeks 1 through 3 now, and want the chart to automatically update if I add data for week 4. Row 1 Week 1 2 3 Data 12.5 17 18 Thanks for the assist! -Adam Use a defined name using the offset function for your series --...

Way to automate adding pictures in a series in Excel?
I'm putting a column of pictures of people next to a column with their names. If I have the pictures together in a folder, with the photo file names numbered with the corresponding row numbers that the pictures go in, is there a way to record a macro so that (for instance) 28.jpg will go into the picture column in row #28, then 29.jpg will go into the picture column in row #29, etc. In other words, insert a whole series of pictures (filenames the same as their appropriate row numbers) in their correct places, by initiating one macro? If anyone has any idea of how that could be done, I wou...

DECLARE_SERIAL/IMPLEMENT_SERIAL macros
Hello. Can someone tell me what is the purpose of DECLARE_SERIAL and IMPLEMENT_SERIAL macros? I have a class (inherited from CObject) in which I use an override of Serialize (CArchive&) to read/write data from/to a file. Just for the fun of it I tried commenting out the macros, and serialization still worked just fine. So what are these macros supposed to do that is so special? By extension, if I want to make a template class serializable, can I simply not use those macros, since I get nothing but compiler errors when I try to use them on the template class? Thanks. Graeme Swal...

How to assign PRINT option to execute a macro
How can I assign PRINT option to execute a macro, used for printing page numbers in a cell. Macro results a proper answer I require but only on executing or playing macro. I want to use Print options of excel to execute the following mentioned macro. Sub Demo() Dim TotalPages As Long Dim pg As Long TotalPages = ExecuteExcel4Macro("Get.Document(50)") For pg = 1 To TotalPages With ActiveSheet .Range("BY3").Value = pg & " of " & TotalPages '<<< CHANGE HERE .PrintOut From:=pg, To:=pg End With Ne...

Automating transfers, is this possible?
Hi All. I am using MM 2007 Premium and I'm using Advanced Bills. In "Help" it says .... "Make electronic transfers. Set up automated transfers between any accounts you track in Money" I have a set up an "xfer" from my checking to my savings (same bank) to occur once a month but Money doesn't actually do anything unless I manually go to "Bills Summary", check it and and click "Pay Online". This isn't what I would call an automated transfer. Is there any way to actually make this happen? Thanks, Richard ----== Posted via ...

Turn Off save prompt, reminder when closing modified WORD...
Some EXCEL files display a prompt when closing the file that asks if you want to save changes even when the file was merely opened and viewed, no cells or formatting changes were made. This creates an extra step, "no" must be clicked before the file can be closed. If the setting that causes the reminder or dialog box to open could be switched off, in both WORD and Excel it would eliminate this annoyance. imagine you have a cell with the formula =NOW() just opening the file DOES change the worksheet, hence the prompt. "S. Nyman" <S. Nyman@discussions.microsoft.c...

How do I edit a macro in Outlook?
Everytime I edit a macro that I have written to make emails into tasks, the macro is no longer an option when I go back into outlook. Is this normal? Does the macro depend on a variable that's set in another procedure, for instance in the Application_Startup event? Editing a macro resets everything; you might need to run Application_Startup again. -- Best regards Michael Bauer - MVP Outlook Manage and share your categories: <http://www.vboffice.net/product.html?lang=en> Am Sun, 14 Mar 2010 19:59:01 -0700 schrieb Dina: > Everytime I edit a macro that ...

Problem writing a macro to Hide Detail in a Pivot Table
I have a pivot table with a row I want to Hide Detail for. I recorded macro by clicking on the row heading I want to hide, then right clickin and selecting Group and Outline and then selecting Hide Detail. Thi all worked during recording, but when I try to play it back I get Run-time error 1004; Unable to set the ShowDetail property of the Rang class. The recorded macro is: Sheets("Classic").Select Range("E7).Select Selection.ShowDetail = False Range E7 is the Row label of the data I want to hide. I've recorded many macros and understand a lot of the quirks abou pivot...

macro code to open most recent file.
I am currently using the following code. I am trying to find some additional code that will go to a designated folder and open the most recent excel file and run the remaining syntax. So this new code would go to the macros folder and find the most recent quote sheet1.xls and open it and run the remainder of the macro it would do the same for the remaining quote sheet files. If I had to save the quote sheet test1, test2, test3 into separate folders I can do that as well. Any help would be greatly appreciated. Sub aaa() Workbooks.Open Filename:="C:\Documents and Settings\Defaul...

Macros for Morons
So I've been given some macro texts as a way to solve various problems. However, I am a macro moron and don't even know how to use the text I've received. If it isn't too much trouble, can someone explain how to use a macro and how to enter the macro codes I've received? Thanks for any help. Hi have a look at http://www.mvps.org/dmcritchie/excel/getstarted.htm for some instructions how to use macros -- Regards Frank Kabel Frankfurt, Germany Lucas Ford wrote: > So I've been given some macro texts as a way to solve various > problems. However, I am a macro m...

macro to hide sheets
Hello there, would anyone be able to help me with this please. I have a workbook with 100+ sheets and I need to hide all but two until a password is entered, then the rest of the sheets can be viewed. All help is appreciated Thanks, Ditchy The password routine is simple and not robuts, but may suit your purpose Sub HideSheets For Each sh In Activeworkbook.Worksheets If sh.Name <> "some name" And sh.Name <> "some other name" Then sh.Visible = xlSheetHidden End If Next sh End Sub Sub ShowSheets Dim sPass sPass = Inputbox...

VBA automating Word from PowerPoint 11-22-09
I have an add-in macro that exports PPT content to a new Word document for each PPT file in the same folder as the currently open PPT file. I've noticed that when the macro is finished, and I go to Word, Word seems "paralyzed" (I can't use any menu commands or close the doc). If I Alt+Tab back to PowerPoint, and then Alt+Tab back to Word, all is well. Is there something about automating Word from PowerPoint that needs to be "cleared" when the PowerPoint macro ends - some way to release PowerPoint's control over Word? I found http://www.pptfaq.com...

automation
Hi there; I am copying specific cells from a file (actuallu use the address approach, like aa!c12) to another file. The cells address in source file are always the same, but in target file changes each time to lower rows than previous one. I have 100 source files at least, how to automate this, instead of clicking at each cell each time between source and target files. Thanks for your help. in your target file, use dir to get the first source file, copy the cell(s) desired, close this source file, use dir() to get 2nd file and repeat till finish "Darius" wrote: > Hi there; ...

End loop macro with count
Help! I want to limit the number of times my loop macro iterates to, for example, 20 times. I have attempted to use a "count" function but I don't have a working example, I can't locate one, and my attempt doesn't work. Can someone please provide a simple way of doing this. The routine has apprximately 15 sub routines. A count limit that applies to a specific sub routine will be perfect or one that applies to the whole routine will be near enough to. Thanks in advance. Jason, Try using a For loop. E.g., Dim N As Long For N = 1 To 20 ' your code here Next N ...

How To Call Automation Methods with SAFEARRAY** [out]
I have a COM component has such interface: STDMETHOD(GetErrList)(SAFEARRAY** pALarr, SAFEARRAY** pVLarr); How can I consume it from MFC ? Thanks! First, add a new class to your project by using either Class Wizard (in VC6.0) or by right-clicking on the project in the class view. Select "Add Class". In the next dialog select "From Type Library". Select your COM library file. Click OK. This will generate a new MFC class that's derived from COleDispatchDriver. It will also have a set of methods implemented, that invoke your COM object methods. Use them at your ...

Help wth column sorting macro!!!!
Help!!! I need a macro that will sort data by a column when the user clicks on the column heading (i.e., username). The macro then needs to return the user to the cell that was selected prior to sorting the data. P.S. I need this for a client within the next 1/2 hour if possible. Thanks so much!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Believe you have received and answer to this in one of the other groups where you multiposted this question. -- Regards,...

a macro to collect data on a loop every minute.
Hi I have created a query that collects new stock data every minute from a web page. I need to create a macro that collects data from sheet 1, cells F21,F22 every minute, after refresh, and copies each new data value to columns A+B on sheet2...I.E F21 copied to A2, F22 copied to B2....then after refresh F21 copied to A3,F22 copied to B3 and so on etc...until I stop the macro looping......... This is so I can plot a live stock chart from the data ranges over time......... With a bit of research and advice this is what I tried....... Sub StartTimer() RunWhen = Now + TimeSerial(0, 0, cRunInte...

Help with Macro #2
Maybe someone can help me here What I need it this, As soon as a user opens a specific file, the macro ( in vba), creates another worksheet, inserts 3 columns and makes the first column auto numbered. I am sure this isn't hard. But I am a "newbie" to this. This all has to happen automatically when you open the file. Can anyone help me? Joanne Answered in the excel.misc group. Helpful information here... http://www.cpearson.com/excel/newposte.htm -- Jim Cone San Francisco, USA "Joanne M." <joanne.e.mahoney@gmail.com> wrote in message Maybe someone can...

Automated Stock Allocation Automation
Hi I have to write a integration procedure that performs stock allocation to Back Orders on Great Plains. This can currently be done by using the eEnterprise application, but I was wondering if I can do it automatically using eConnect Thanks ...