VBA to search for a window then save is no longer working.

[anichols]

Hello all,

I have a code that will search to see if a window pops up (In this example a Save As Window), once the window pops up, my copied cell will be pasted and entered into the box. It seemed to work sometime ago, but now I can't figure out why it isn't. Any help with tweaking this code would be greatly appreciated.

Sub ShowMeTheFile1()

    Const MAX_WAIT_SECS As Long = 25
    Const WAIT_SECS As Long = 5

    Dim hWnd As Long
    Dim ans As VbMsgBoxResult
    Dim endTime As Single
    Sheets("DAILY02").Select
    Range("G41").Select
    Selection.Copy
    endTime = Timer + MAX_WAIT_SECS
    Do
        hWnd = FindWindow(vbNullString, "Save As")
        If hWnd > 0 Then
            SetForegroundWindow hWnd
            Application.SendKeys ("%v")
            Application.SendKeys ("~")
            Exit Do
        End If
        If Timer > endTime Then
            ans = MsgBox("Window not found, try again?", vbQuestion + vbYesNo)
            If ans = vbYes Then
                endTime = Timer + MAX_WAIT_SECS
            Else
                Exit Do
            End If
        Else
            PauseMacro WAIT_SECS
        End If
    Loop

End Sub

 

[Dan_W]

I note that @Domenic previously helped on this, and that your code above is a revised version of the solution he provided. The difference appears to be the SendKeys addition. Is that right? If you're planning on pasting something from the clipboard, you're likely wanting CTRL v whereas your code above (%v) is ALT v.

Try:

Application.SendKeys ("^v")

You may also want to allow for some time between each of the sendkeys commands, so I'd recommend using the PauseMacro routine for maybe 1 or 2 seconds.
Let me know how it goes.

 

[Dan_W]

Sorry, I'm just rereading your code - what are you trying to do with this? Are you using it to save Excel workbooks? If so, there is a far easier way than this to do save workbooks. I previously responded to one of your queries about saving what you had referred to as corrupted workbooks. Is this related to that at all?

Alternatively, if you're sourcing the filenames from the workbook and saving them through the Save As dialog box of another program, again, you don't need copy the data from the worksheet and use Sendkeys to paste the filename. The following code should take the guesswork out of part of the process at least.

Sub ShowMeTheFile1()

    Const MAX_WAIT_SECS As Long = 25
    Const WAIT_SECS As Long = 5

    Dim hWnd As Long
    Dim ans As VbMsgBoxResult
    Dim endTime As Single

    Dim FileName As String
    FileName = Sheets("DAILY02").Range("G41").Value

    endTime = Timer + MAX_WAIT_SECS
    Do
        hWnd = FindWindow(vbNullString, "Save As")
        If hWnd > 0 Then
            SetForegroundWindow hWnd
            Application.SendKeys FileName
            Application.SendKeys ("~")
            Exit Do
        End If
        If Timer > endTime Then
            ans = MsgBox("Window not found, try again?", vbQuestion + vbYesNo)
            If ans = vbYes Then
                endTime = Timer + MAX_WAIT_SECS
            Else
                Exit Do
            End If
        Else
            PauseMacro WAIT_SECS
        End If
    Loop

End Sub

 

[anichols]

I note that @Domenic previously helped on this, and that your code above is a revised version of the solution he provided. The difference appears to be the SendKeys addition. Is that right? If you're planning on pasting something from the clipboard, you're likely wanting CTRL v whereas your code above (%v) is ALT v.

Try:

Application.SendKeys ("^v")

You may also want to allow for some time between each of the sendkeys commands, so I'd recommend using the PauseMacro routine for maybe 1 or 2 seconds.
Let me know how it goes.

Yes, I ended discovering that it was ^v that was my issue. This is actually waiting for another program to generate a save as box to save a report as an excel file. But it does work now!

 

[Dan_W]

Ok - thank you for the explanation (and the confirmation re: ^v!). That makes sense. As I mentioned above, the copy-paste method can cause errors sometimes when VBA runs a little too fast for excel/the clipboard to keep up, and putting sendkeys in there as well might cause problems. The code above puts the filename into a variable, and the sendkeys puts the filename into the box - bypassing the temperamental clipboard. But at the end of the day, whatever gets the task done is the only method you need!