VBA batch script - refresh folder

Question

Currently I use a script to download a pdf-file, change it to a txt-file, extract info from the txt-file to rename the pdf-file and save it to a certain folder. Besides VBA I use a couple of batch scripts to achieve this. A temporary pdf-file is at first saved to the folder and later on deleted from the folder.

If I go to the folder I need to refresh it manually, then I will not see the file anymore. I looked on the web to see if there was a batch script or vba to refresh or update a folder, but I could not find anything nearly usefull. Is there someone who knows how this can be accomplished through VBA or a batch script?

Sincerely, Richard

Edited from here at 22:18 @Rojo and others: I am looking for code which will refresh a folder in the background. The code below will go to the folder if it is open, refresh it and go back to Outlook. Do not use it out of the VBA Editor in Outlook, because it will go into an annoying loop, which you can just stop by clicking on Outlook. But use a button or some else to execute the code.

Sub ActivateOutlook()
   On Error Resume Next
   Set objOutlook = GetObject(, "Outlook.Application")
   If err.Number = 429 Then 
      MsgBox "Outlook is not running"
   Else
      AppActivate objOutlook.ActiveExplorer.Caption
   End If
End Sub

Sub RefreshSavedFiles()
Dim oShellObject
Set oShellObject = CreateObject("Wscript.Shell")
strFolder = "C:\Users\User\Documents\PDF files saved"
oShellObject.AppActivate strFolder
oShellObject.SendKeys "{F5}"
ActivateOutlook
End Sub

Answer 1

You just want to F5 a window, basically, right? That's easy. Just focus it using WshShell.AppActivate and send F5 with WshShell.SendKeys.

Returning the focus to the console window can be a little tricky, though. The easiest way to do this is to set the console window's title, then refocus based on title after you've performed the refresh.

@if (@CodeSection == @Batch) @then

:: begin batch portion

@echo off
setlocal

set "focustitle=foldername"

:: use JScript hybrid code to perform focus / sendkeys wizardry
title Refreshing
cscript /nologo /e:Jscript "%~f0" "%focustitle%"
title Command Prompt

:: Refresh complete.  Put the rest of your script here.

goto :EOF
:: end batch portion / begin JScript chimera
@end

var oShell = WSH.CreateObject('Wscript.Shell');

oShell.AppActivate(WSH.Arguments(0));   // focus window by title
WSH.Sleep(100);
oShell.SendKeys('{F5}');    // F5 = refresh
WSH.Sleep(100);
oShell.AppActivate('Refreshing');   // re-focus console window

---

If you'd rather not mess with your console window title, you could focus by PID instead. This isn't terribly easy, though. If you have multiple console windows open, how do you determine which PID belongs to the active console?

This example .bat script that demonstrates spawning a child process, then walk up the ParentProcessID line until the root cmd process is found and use that PID. It takes a couple of seconds since WMI queries to Win32_Process are slow, but it is what it is. *shrug*

With the cmd window's PID known, the rest is easy. Activate your window you wish to refresh, send F5, then re-activate PID.

@if (@CodeSection == @Batch) @then

:: based on http://stackoverflow.com/a/27514649/1683264
:: begin batch portion

@echo off
setlocal

set "focustitle=code"

:: use JScript hybrid code to perform focus / sendkeys wizardry
cscript /nologo /e:Jscript "%~f0" "%focustitle%"

:: Refresh complete.  Put the rest of your script here.

goto :EOF
:: end batch portion / begin JScript chimera
@end

var oShell = WSH.CreateObject('wscript.shell'),
    johnConnor = oShell.Exec('%comspec% /k @echo;');

// returns PID of the direct child of explorer.exe
function getTopPID(PID, child) {
    var proc = GetObject("winmgmts:Win32_Process=" + PID);
    return (proc.name == 'explorer.exe') ? child : getTopPID(proc.ParentProcessID, PID);
}

var PID = getTopPID(johnConnor.ProcessID);
johnConnor.Terminate();

oShell.AppActivate(WSH.Arguments(0));   // focus window by title
WSH.Sleep(100);
oShell.SendKeys('{F5}');    // F5 = refresh
WSH.Sleep(100);
oShell.AppActivate(PID);   // re-focus console window

Sincerely, Not Richard

Answer 2

Looking at the answers from Rojo and also David Ruhmann and not being able to find anything useful on the web on this issue, also not on renowned websites like Stack Overflow, the code below will be the best alternative to my question.

The code below will refresh a folder almost invisibly, I say almost because sometimes a message will show that the folder is not open, it just happened two times when I tested it. Edit on 23.02.2015: I added a Do.. Loop While to prevent the message from coming up and so far I did not get the message again that the folder is not open. So the code has improved to my liking and decided my own answer is the answer to my question.

What does the code do? From Outlook it will open an Explorer window in a predefined directory without showing it, refresh it and close the window again within a second and go back to Outlook. I put together and altered code I found here and there to make it useful for me. Maybe it can also be of use for you.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Const WM_CLOSE = &H10

Private Sub CloseExplorer()
Dim GetOpenWindow As Long
Dim OpenFolder As String
Dim WindowSuccesfullyClosed As Boolean
OpenFolder = "C:\Users\User\Documents\PDF files saved"
GetOpenWindow = FindWindow(vbNullString, OpenFolder)
Do
WindowSuccesfullyClosed = False
Counter = Counter + 1
Sleep (5) 'milliseconds
If GetOpenWindow <> 0 Then
PostMessage GetOpenWindow, WM_CLOSE, 0&, 0&
WindowSuccesfullyClosed = True
Exit Do
End If
Loop While Not GetOpenWindow <> 0 And Counter < 100
If WindowSuccesfullyClosed = False Then
MsgBox OpenFolder & " is not open."
Else
End If
End Sub

Sub ActivateOutlook()
   On Error Resume Next
   Set objOutlook = GetObject(, "Outlook.Application")
   If err.Number = 429 Then 
      MsgBox "Outlook is not running"
   Else
      AppActivate objOutlook.ActiveExplorer.Caption
   End If
End Sub

Sub RefreshSavedFiles()
Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 0 'windowStyle = 1 when visible
    Dim OpenFolder As String
OpenFolder = "C:\Users\User\Documents\PDF files saved"
wsh.Run "%windir%\explorer.exe /n," & OpenFolder, windowStyle, waitOnReturn
wsh.AppActivate OpenFolder
wsh.SendKeys "{F5}"
CloseExplorer
ActivateOutlook
Set wsh = Nothing
End Sub