Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
Dear Expert,
I have written some macros. Sometimes, it has "save" function. Then it would pop up a MSG windows asking you "Do you want to save changes you made to 'MMDEALS.XLS'? Macro will stop until you click yes or now. Can VB scripts be written to answer "Yes" automatically ? Also, If I use "save as", there is a pop up msgbox too. A file named 'C:\marco\Income_new.xls' already exists in this location. Do you want to replace it? Can VB scripts be written to answer "Yes" automatically ? Thanks so much Elton |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
Please refer..Cant we take off the Msgbox and Save by default...
Another way is to Save the file in all cases of vbYes/vbNo/vbCancel OR You can also try SENDKEYS() . Refer the below link http://www.developerfusion.com/code/...automatically/ Please try and let us know If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Dear Expert, I have written some macros. Sometimes, it has "save" function. Then it would pop up a MSG windows asking you "Do you want to save changes you made to 'MMDEALS.XLS'? Macro will stop until you click yes or now. Can VB scripts be written to answer "Yes" automatically ? Also, If I use "save as", there is a pop up msgbox too. A file named 'C:\marco\Income_new.xls' already exists in this location. Do you want to replace it? Can VB scripts be written to answer "Yes" automatically ? Thanks so much Elton |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
Just tell excel how to close the workbook.
Dim wkbk as workbook set wkbk = ..... ..... wkbk.close savechanges:=true 'or false to discard the changes. Elton Law wrote: Dear Expert, I have written some macros. Sometimes, it has "save" function. Then it would pop up a MSG windows asking you "Do you want to save changes you made to 'MMDEALS.XLS'? Macro will stop until you click yes or now. Can VB scripts be written to answer "Yes" automatically ? Also, If I use "save as", there is a pop up msgbox too. A file named 'C:\marco\Income_new.xls' already exists in this location. Do you want to replace it? Can VB scripts be written to answer "Yes" automatically ? Thanks so much Elton -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
Hi Dave,
Please apologize if I am stupid. I try to copy your wordings and test. I does not work It stops at "wkbk.Close Savechanges:=True" Is it due to my version ? Mine is Excel 2000 only. Thanks Elton Sub Macro1() Dim wkbk As Workbook Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False wkbk.Close Savechanges:=True End Sub "Dave Peterson" wrote: Just tell excel how to close the workbook. Dim wkbk as workbook set wkbk = ..... ..... wkbk.close savechanges:=true 'or false to discard the changes. Elton Law wrote: Dear Expert, I have written some macros. Sometimes, it has "save" function. Then it would pop up a MSG windows asking you "Do you want to save changes you made to 'MMDEALS.XLS'? Macro will stop until you click yes or now. Can VB scripts be written to answer "Yes" automatically ? Also, If I use "save as", there is a pop up msgbox too. A file named 'C:\marco\Income_new.xls' already exists in this location. Do you want to replace it? Can VB scripts be written to answer "Yes" automatically ? Thanks so much Elton -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
If you want Excel to always SAVE, why ask in the first place?!?
But, if you really want the MsgBox, you could do something like: Answer = MsgBox("Save?",vbYesNo,"Save") Answer = vbYes ....continue saving here... Elton Law wrote: Dear Expert, I have written some macros. Sometimes, it has "save" function. Then it would pop up a MSG windows asking you "Do you want to save changes you made to 'MMDEALS.XLS'? Macro will stop until you click yes or now. Can VB scripts be written to answer "Yes" automatically ? Also, If I use "save as", there is a pop up msgbox too. A file named 'C:\marco\Income_new.xls' already exists in this location. Do you want to replace it? Can VB scripts be written to answer "Yes" automatically ? Thanks so much Elton |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
Try this code
Dim wkbk As Workbook Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test.xls" wkbk.Close Savechanges:=True Application.DisplayAlerts = True End Sub -- If this post helps click Yes --------------- Jacob Skaria |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
Please note that I have only modified the last part of your code.....
If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try this code Dim wkbk As Workbook Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test.xls" wkbk.Close Savechanges:=True Application.DisplayAlerts = True End Sub -- If this post helps click Yes --------------- Jacob Skaria |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
Hi Super Jacob,
Really appreciate your help today .... I try run as what you say .... It stops at here - "wkbk.Close Savechanges:=True" Pop up a msgbox saying Run-time error '91' Object variable or with block variable not set Then what should I do ? Thanks Elton (quote) Sub Marco2() Dim wkbk As Workbook Windows("CXLFX.xls").Activate Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test111.xls" wkbk.Close Savechanges:=True Application.DisplayAlerts = True End Sub (End of Quote) "Jacob Skaria" wrote: Try this code Dim wkbk As Workbook Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test.xls" wkbk.Close Savechanges:=True Application.DisplayAlerts = True End Sub -- If this post helps click Yes --------------- Jacob Skaria |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pop up Windows Question
Oops....Replace error line with...As I mentioned earlier I havent dont much
changes to the code.... ActiveWorkbook.Close Savechanges:=True -- If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Super Jacob, Really appreciate your help today .... I try run as what you say .... It stops at here - "wkbk.Close Savechanges:=True" Pop up a msgbox saying Run-time error '91' Object variable or with block variable not set Then what should I do ? Thanks Elton (quote) Sub Marco2() Dim wkbk As Workbook Windows("CXLFX.xls").Activate Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test111.xls" wkbk.Close Savechanges:=True Application.DisplayAlerts = True End Sub (End of Quote) "Jacob Skaria" wrote: Try this code Dim wkbk As Workbook Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Select Selection.Copy ActiveCell.Offset(2, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:="H:\ActivateWindows_Test.xls" wkbk.Close Savechanges:=True Application.DisplayAlerts = True End Sub -- If this post helps click Yes --------------- Jacob Skaria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro switch between windows question | Excel Discussion (Misc queries) | |||
XP windows Registry Question | Excel Discussion (Misc queries) | |||
windows xp file name convertion question | Excel Discussion (Misc queries) | |||
Windows Explorer Question | Excel Programming | |||
AddressOf / Windows API question | Excel Programming |