VBA stops when Save As dialog box appears?

[stever99]

Hi folks,

I'm having a problem trying to automate a task in Excel because my VB formula requires a manual press of the Enter button or a click on "OK" in the Save As dialog box.

The relevant part of the code is as follows:

Application.GetSaveAsFilename( _
initialfilename:=ActiveCell.Offset(0, 1), filefilter:=sFilter)

The macro basically saves an image on the clipboard as the filename specified in the adjacent cell. This is looped so that the routine should proceed row by row:

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

However, each time the function executes, the Save As dialog box appears and the macro will not proceed to the next row until I click "OK" in the Save As dialog box. I want to just save the file as the filename specified in the adjacent cell but I can't get around the manual click on "OK" to save the file. I have tried using SendKeys and simulating a left mouse click with focus applied to the "OK" button, but the problem is that the VBA function just seems to stop and wait for user input before proceeding.

Is there any way I can have the filename functionality of the SaveAs command without the manual entry in the SaveAs dialog box?

Thanks!!

 

[stever99]

By the way, have tried using

Application.DisplayAlerts = False

But the dialog box appears anyway

 

[BrianB]

If you just want to SaveAs a coded filename without the dialog then use something like :-

ActiveWorkbook.SaveAs Filename:="F:\book1.xls"

(You could have recorded a macro to get this code)

 

[stever99]

Mmm, this doesn't work because I'm not actually saving the workbook, I'm saving an image on the clipboard as a filename specified in the adjacent cell.

It's kind of convoluted I know

What I have done as a kludgearound is write a .wsf file telling windows to send an enter keystroke 1,000 times. It's klunky but it works - I just know there's a slicker way to do it!

 

[BrianB]

I have code here that shows how to save clipboard to a file.

http://www.mrexcel.com/forum/showthread.php?t=355239