Making Excel respond to keystokes using VBA

[supanoods]

I am trying to save a file in excel using the SaveAs function, due to the need of making the file an Excel 5/95 & 97 compatible.

Everything works fine, however when the current file is overwritten, excel wants the user to select yes to the prompt asking if the user wants to overwrite.

I have tried to use the following after the code that saves the file:
SendKeys("%y&quot

The macro when ran, produces the prompt after the code that saves the file, but before the above line of code!

Can anybody Help me????

Thanks in advance

Phasid "If something is hard to do - then it isn't worth doing" - Homer Simpson

 

[Guest_imported]

Application.displayalerts = false will make the application go with the default and not display the dialog. true turns it back on again.

 

[bryanbayfield]

Very important point that, actaully. If you don't set display alerts back to true at the end of the macro, it stays in effect for the rest of the session.

So remember to turn it back on!

 

[pe]

I need to search 5000 rows for "Q1. " ... "Q71. " and delete the question and numeral part. Then i need to make the font underlined in the cell and "proper case", it's now all caps.

Something like...
for i=1 to 5000
search for Q.##
delete Q.##
activecell.select
Selection.Font.Underline = xlUnderlineStyleSingle
make propercase
next i

Does anyone have a function to make ALL CAPS into All Caps. Also, I don't know how to delete Q.1 to Q.71 and keep the rest of the question. For instance, Q.1 HOW DO YOU FEEL TODAY?

It needs to read How Do You Feel Today? (underlined).

any help is greatly appreciated. thank you

 

[supanoods]

Thanks for the help - however there is a problem!

The prompt that appears has three options Yes, No , & Cancel

I wand to select yes - but the default is No

Any ideas??? "If something is hard to do - then it isn't worth doing" - Homer Simpson

 

[JustinEzequiel]

only have Excel 97 here but...

I guess you could check first if the file exists then delete it before save as...

If Dir(&quot;c:\booktest.xls&quot;) <> &quot;&quot; Then
  Kill &quot;c:\booktest.xls&quot;
End If
ThisWorkbook.SaveAs &quot;c:\booktest.xls&quot;

you will get an error however if the said file is open

 

[AirZebra]

Hi,

I may be out of line here, but shouldn't the sendkeys command be before the line of code which saves the file?

Regards,

Alan

 

[supanoods]

To all,

I managed to get it to work buy coding in the following order:

Application.DisplayAlerts = False
ActiveWorkbook.saveas FileName:="bat34.xls",
FileFormat:=xlExcel9795,
Password:="",
WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
SendKeys ("%y&quot
Application.DisplayAlerts = True

Thanks to all for the help it is most valuable

PHASID "If something is hard to do - then it isn't worth doing" - Homer Simpson