Unable to write the filename and click on Save button simultaneously...VBA code

Question

I am trying to save a PDF. With the code below, I am to change the file Name but then after the SendMessageByString hWnd, WM_SETTEXT, Len(Sample), Sample command the dialog box looses the focus and then I am unable to save the file.

The file is :

From the web. I tried downloading directly from the URL but it does not work.

The command :

  timeout = Now + TimeValue("00:00:20")
  Do
  hWnd = FindWindow(vbNullString, "Save As") 'Finding the save as window
  DoEvents
  Sleep 200
  Loop Until hWnd Or Now > timeout

am using to get the handle for dialog box. If I write the same command after the

SendMessageByString hWnd, WM_SETTEXT, Len(Sample), Sample

I am able to get the focus of the dialog box but the command does not save the file with the changed file name. It saves it as the original name of OnlineStmtResultsPremDis.Do Which looks like

How do I change the file name and save the pdf.

My Code: ****Navigating thru the webpage**** then

Do While IE.Busy Or IE.ReadyState <> 4: Loop
      Application.Wait (Now + TimeValue("00:0:03"))
      IE.Navigate "https://www..com/CWRWeb/OnlineStmtResultsPremDis.do" 'Final PDF
      Application.Wait (Now + TimeValue("00:0:18"))
      Set HTMLDoc5 = IE.document
      Application.SendKeys "+^{S}" 'Save Key ShortCut
      Application.Wait (Now + TimeValue("00:0:03"))

      'Finding the Save As Dialog Box

      timeout = Now + TimeValue("00:00:20")
      Do
      hWnd = FindWindow(vbNullString, "Save As") 'Finding the save as window
      DoEvents
      Sleep 200
      Loop Until hWnd Or Now > timeout

      If hWnd Then

        SetForegroundWindow hWnd

        'Find the child DUIViewWndClassName window

       hWnd = FindWindowEx(hWnd, 0, "DUIViewWndClassName", vbNullString)

    End If


      If hWnd Then

        'Find the child DirectUIHWND window

        hWnd = FindWindowEx(hWnd, 0, "DirectUIHWND", "")

    End If

    If hWnd Then

        'Find the child FloatNotifySink window

        hWnd = FindWindowEx(hWnd, 0, "FloatNotifySink", "")

    End If

      'If hWnd Then
      'hWnd = FindWindowEx(hWnd, 0, "ComboBox", vbNullString) 'Child Combo Box
      'End If


      If hWnd Then
      hWnd = FindWindowEx(hWnd, 0, "ComboBox", "") 'Child Combo Box

      End If

      If hWnd Then
      SetForegroundWindow (hWnd) 'Chilf Edit Window
      Sleep 600
      hWnd = FindWindowEx(hWnd, 0, "Edit", "") 'Child Combo Box

      End If


      Sample = "80287.pdf" 'Misc. need to delete

      If hWnd Then
      SetForegroundWindow (hWnd) 'changing the folder name
      Sleep 600
      SendMessageByString hWnd, WM_SETTEXT, Len(Sample), Sample
      End If


      'Do
      'hWnd = FindWindow(vbNullString, "Save As") 'Finding the save as window
      'DoEvents
      'Sleep 200
      'Loop Until hWnd Or Now > timeout

      If hWnd Then
      SetForegroundWindow (hWnd)
      hWnd = FindWindowEx(hWnd, 0, "Button", "&Save") 'Finding the Save button on the window
      End If

      If hWnd Then
      SetForegroundWindow (hWnd) 'Click on the Save As window
      Sleep 600
      SendMessage hWnd, BM_CLICK, 0, 0
      End If

      End Sub

Declaration:

Option Explicit

Public Declare PtrSafe Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long)


Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare PtrSafe Function FindWindowEx Lib "user32" _
                                  Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, _
                                  ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Public Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As LongPtr, ByVal wMsg As Long, _
                                                           ByVal wParam As LongPtr, lParam As Any) As LongPtr

Public Declare PtrSafe Function SetForegroundWindow Lib "user32" _
    (ByVal hWnd As Long) As LongPtr


Public Declare PtrSafe Function SendMessageByString Lib "user32" Alias "SendMessageA" _
    (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As LongPtr


Public Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" _
    (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As LongPtr


Public Declare PtrSafe Sub keybd_event Lib "user32" _
    (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)



    Public Const BM_CLICK = &HF5
    Public Const WM_SETTEXT = &HC
    Public Const WM_GETTEXT = &HD
    Public Const WM_GETTEXTLENGTH = &HE

    Public Const VK_KEYDOWN = &H0
    Public Const VK_KEYUP = &H2
    Public Const VK_CONTROL = &H11

I don't understand how you get to Save As window. Do you do it manually or are you calling SaveAs somewhere? — keiv.fly, Jun 24 '16 at 22:25
Application.SendKeys "+^{S}" .... using this shortkey ctrl+shift+s — Shank, Jun 24 '16 at 23:33
Maybe you should try this [How do i download a file using VBA](http://stackoverflow.com/questions/17877389/how-do-i-download-a-file-using-vba-without-internet-explorer). — BitAccesser, Jun 25 '16 at 01:31
Can you change the filename after it's been saved? Simple rename/move command? — dbmitch, Jun 25 '16 at 05:10
Why don't you use the SaveAs function instead of SendKeys? Like here: http://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line — keiv.fly, Jun 26 '16 at 19:38
@BitAccesser... the link u shared wants the url with the address of PDF. In my case the URL (https://www.abcd.com/CWRWeb/OnlineStmtResultsPremDis.do) does not have a pdf address. So when I pass the above URL in the function it creates a pdf but without any data. — Shank, Jun 27 '16 at 13:26
@dbmitch...... When I use ShortKeys to save ...I do not know where the file will be saved . The system opens a save as window with the address of the folder which was last accessed. I was thinking of typing in the entire address of the folder that I want the file to be saved in, in the edit box. e.g E:\82356.pdf — Shank, Jun 27 '16 at 13:29
@Keiv.fly... expression.SaveAs in your method the expression refers to the workbook. I want to save a pdf from webpage. I I use your method i would save a workbook in pdf format. — Shank, Jun 27 '16 at 13:31
One Observation: When I use the below code in Windows 7 and VB 7.1 then I am able to save the file with a changed name.....but not in windows 8.1they have changed the class named... If hWnd Then hWnd = FindWindowEx(hWnd, 0, "ComboBox", vbNullString) 'Child Combo Box End If If hWnd Then hWnd = FindWindowEx(hWnd, 0, "ComboBox", "") 'Child Combo Box End If If hWnd Then SetForegroundWindow (hWnd) 'Chilf Edit Window Sleep 600 hWnd = FindWindowEx(hWnd, 0, "Edit", "") 'Child Combo Box End If — Shank, Jun 27 '16 at 13:33
Are you using a plug in? I don't have a menu to save as a pdf. I read that there could be incompatibility for 64bit IE with save as pdf feature. I will try to print via Cute pdf. If I succeed I will post an answer. — keiv.fly, Jun 28 '16 at 18:50
I am not using any additional feature.(plug in) . I just have adobe acrobat free version installed on my laptop. Thnks, really appreciate your help. — Shank, Jun 29 '16 at 01:46
I am unable to get the handle for ComboBoxEx32.... I tried in Windows 7 Excel 32bit and VBA 7.1 in one of the laptop were as the same code written in a different laptop having the same configuration of the three some how returns the handle..... any idea?? — Shank, Jul 12 '16 at 21:09

score 0 · Answer 1 · answered Jul 15 '16 at 14:22

So I changed the code to add a PostMessage while the focus is on Edit Box: The code works but I am not sure how....

If hWnd Then
  'SetForegroundWindow (hWnd) 'changing the folder name
  'Sleep 200
  SendKeys "{BACKSPACE}"
  Sleep 200
  For x = 1 To Len(Sample)
  lngPM = PostMessage(hWnd, WM_SETTEXT, Asc(Mid(Sample, x, 1)), 1&)
  Sleep 200
  Next x
Sleep 200
  SendMessageByString hWnd, WM_SETTEXT, Len(Sample), Sample
  End If

  Application.Wait (Now + TimeValue("00:0:03"))

I am using 32 bit Ms excel 2016 Ms Office... windows 7 64 bit... full code:

  Do
    hWnd = FindWindow("#32770", "Save As") 'Finding the save as window
  DoEvents
  Sleep 200
  Loop Until hWnd Or Now > timeout

  If hWnd Then

    'SetForegroundWindow hWnd

    'Find the child DUIViewWndClassName window

   hWnd = FindWindowEx(hWnd, 0, "DUIViewWndClassName", vbNullString)

End If


  If hWnd Then

    'Find the child DirectUIHWND window

    hWnd = FindWindowEx(hWnd, 0, "DirectUIHWND", "")

End If

If hWnd Then

    'Find the child FloatNotifySink window

    hWnd = FindWindowEx(hWnd, 0, "FloatNotifySink", "")

End If

  'If hWnd Then
 ' hWnd = FindWindowEx(hWnd, 0, "ComboBoxEx32", vbNullString) 'Child Combo Box
  'End If


  If hWnd Then
  hWnd = FindWindowEx(hWnd, 0, "ComboBox", "") 'Child Combo Box
  End If

  If hWnd Then
  SetForegroundWindow (hWnd) 'Chilf Edit Window
  Sleep 600
  hWnd = FindWindowEx(hWnd, 0, "Edit", "") 'Child Combo Box
  End If


  Filename1 = Cells(I, 2).Value
  Filename2 = Cells(I, 3).Value
  Sample = "D:\Test\" & Filename1 & "_" & Filename & "_" & Filename2 & ".pdf" 'CHANGE : FILE NAME
  If hWnd Then
  'SetForegroundWindow (hWnd) 'changing the folder name
  'Sleep 200
  SendKeys "{BACKSPACE}"
  Sleep 200
  For x = 1 To Len(Sample)
  lngPM = PostMessage(hWnd, WM_SETTEXT, Asc(Mid(Sample, x, 1)), 1&)
  Sleep 200
  Next x
Sleep 200
  SendMessageByString hWnd, WM_SETTEXT, Len(Sample), Sample
  End If

  Application.Wait (Now + TimeValue("00:0:03"))

  Do
  hWnd = FindWindow(vbNullString, "Save As") 'Finding the save as window
  DoEvents
  Sleep 200
  Loop Until hWnd Or Now > timeout

  If hWnd Then
  'SetForegroundWindow (hWnd)
  hWnd = FindWindowEx(hWnd, 0, "Button", "&Save") 'Finding the Save button on the window
  End If

  If hWnd Then
  'SetForegroundWindow (hWnd) 'Click on the Save As window
  Sleep 600
  SendMessage hWnd, BM_CLICK, 0, 0
  End If
Label1:
      IE.Quit
      Application.Wait (Now + TimeValue("00:0:10"))
      I = I + 1
      Loop
      End Sub

Unable to write the filename and click on Save button simultaneously...VBA code

1 Answers1

Linked