As the title implies, I wanted to share a small utility script I created to meet my own needs, in the hopes that it might help others who also need to process and optimize all the images in a folder in one go.
My goal was to quickly and efficiently resize images in a folder based on a specified maximum width and height, then output them in multiple formats to achieve the smallest possible file size—typically WebP, but not always.
Continue reading
I recently came across a forum question in which the user needed a way to break a huge CSV file into multiple smaller files while retaining the header row in each of the smaller files. In this instance the question was revolving around JavaScript, but it got me thinking as to how it could be approached in VB/VBA/VBScript.
If you’re not up on the news, Microsoft has decided to deprecate and remove VBScript!
I’m always amazed at the level of pure stupidity that Microsoft exudes at times!
I know, we all have our moments in the sun, myself included, but Microsoft just seems to manage to outdo themselves.
I’m well aware that in an ideal world, Microsoft wants us all running MS365 and thus we would have all migrated all of our existing Web Browser controls to use the ‘new’ Modern Web Browser control, but that isn’t reality, not even close! The fact of the matter, the legacy Web Browser control is still fully supported and thus why fix something that isn’t broken. Beyond which, many people are simply not running MS365 so they don’t even have access to the Modern Web Browser control at all.
All of this to say, that many a database are still using the ‘Legacy’ Web Browser control, thus IE (Internet Explorer)!
As an FYI for those of you curious, I’ve been trying to ascertain the true impact of the VBScript deprecation.
Sadly, the official announcement provides 0 details whatsoever as to the impacted libraries/components. Feel free to check for yourself by using the 2 links provided below:
Have you ever wanted to run a PowerShell command from within VBA and get back a value?
Before getting into returning a response, let first look at simply executing a command. There are many instances in which we simply want to run a PowerShell command and don’t need to get any response back.
It turns out that it is very easy to do and very similar to the approach taken in VBA – Get Host IP Address.
PowerShell can simply be called/executed from a standard command prompt using the command
powershell -command
Armed with this information when can simple use a WScript Shell object to execute the command, thus giving us a simple function along the lines of
Because of the work I do, I am often copying sensitive information and/or username and passwords, and I simply don’t like that type of information residing in the clipboard (windows memory) for extended periods of time. With all the spyware, viruses, malware, websites that try to harvest data, … it simply isn’t smart, this day in age, to leave such sensitive information available for bad actors.
Previously, I would open Excel, click on the dropdown around for the clipboard and use the Clear All command, but this become tedious to have to do over and over and over … so I created a simply VBA/VBScript file that resides on my desktop and I simply double-click to clear the clipboard. 2 Lines of code are all that are needed:
Set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.Run "cmd.exe /c echo off | clip", 0, True
It’s as easy as that! Should you wish to integrate such functionality within an application (Access, Excel, …) the code uses Late Binding so is fully portable!
Have you ever needed to perform some action that would close Access and then would like to restart it automatically? Perhaps something along the lines of performing a Compact, or some update process.
As part of my last post regarding automating the Compact of the Front-End component of a split database I was faced with this very dilemma. What I came up with was a very straightforward VBScript.
The script, provided below, simply loops until no lock file is present (so the current instance shutdown) and then re-launches the database again. To work, it expects 2 input arguments: (1) the full path and filename of the database, (2) the full path and filename of the lock file.
The following goes to support my previous article MS Access – Automatic Backup of an Access Database, specifically the section regarding using a VBScript in conjunction with Windows Scheduled Task. I had a very basic VBScript, VBScript – Backup a File and add a Date Time Stamp, which illustrated how to add a Date/Time stamp to a file when you copy it and this made a great starting point for a backup script.
Several people have mentioned that they need more of a helping hand to transform that basic script into a more complete backup solution.
So I decided to try and help everyone out some more and below is a pretty comprehensive backup script that you can use.
I was recently doing some work in an old database trying to convert a very simple function which used Wscript.shell Specialfolders into a more versatile function that would accept a single input variable instead of a hardcoded value.
My original function was
Function m() As String
Set WshShell = CreateObject("WScript.Shell")
m = WshShell.SpecialFolders("Desktop")
End Function
Running it would return the path to the current user’s Desktop folder accurately.
C:\Users\MyUserName\Desktop
A very slight variation of the same function
Function mm() As String
Set WshShell = CreateObject("WScript.Shell")
sFldr = "Desktop"
m = WshShell.SpecialFolders(sFldr)
End Function
would also return the path to the current user’s Desktop folder accurately.
C:\Users\MyUserName\Desktop
However, where things got weird was when I made another modification to use sFldr as an input variable and created the following function
Function n(sFldr As String) As String
Set WshShell = CreateObject("WScript.Shell")
n = WshShell.SpecialFolders(sFldr)
End Function
and ran it by calling by using the command n(“Desktop”) it would not return the current user’s Desktop folder, but rather the public desktop path???
C:\Users\Public\Desktop
So I started to do a little digging and came to find I was not the first person to encounter this issue. For instance, see:
Unexpected behavior of WScript.Shell SpecialFolders function
So I did a little more playing around with code and switched the input variable sFldr from String to Long
Function n(sFldr As Long) As String
Set WSHShell = CreateObject("WScript.Shell")
n = WSHShell.SpecialFolders(sFldr)
End Function
and performed a few iterations (1 to 16) and determined the following
1->ProgramData\Microsoft\Windows\Start Menu
2->ProgramData\Microsoft\Windows\Start Menu\Programs
3->ProgramData\Microsoft\Windows\Start Menu\Programs\Startup
4->User Desktop
5->User AppData\Roaming
6->User AppData\Roaming\Microsoft\Windows\Printer Shortcuts
7->User AppData\Roaming\Microsoft\Windows\Templates
8->Windows\Fonts
9->User AppData\Roaming\Microsoft\Windows\Network Shortcuts
10->User Desktop
11->User AppData\Roaming\Microsoft\Windows\Start Menu
12->User AppData\Roaming\Microsoft\Windows\SendTo
13->User AppData\Roaming\Microsoft\Windows\Recent
14->User AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup
15->User Favorites
16->User Documents
17->User AppData\Roaming\Microsoft\Windows\Start Menu\Programs
which is all odd to me. In the hardcoded version it expects a string value, but the last version takes a numeric value to get proper paths returned. The above (last function) has, by far, not been tested in any manner to ensure it is reliable, it is simply at this point an interesting observational fact.
What is a fact is that Wscript.shell Specialfolders does not appear to be reliable when the objWshSpecialFolders variable is not a hardcoded value! As such, I’d recommend using an alternate method to determine the paths of Windows Special folders like: Enumerating Special Folders which uses the CreateObject(“Shell.Application”).Application.Namespace() to get the values.
Too stubborn for my own good, I couldn’t let this one go. So I kept playing around and testing… eventually I managed to get it to work. Below is the reusable function.
'---------------------------------------------------------------------------------------
' Procedure : GetWindowsSpecialFldrPath
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return the full path for the specified Windows Special folder
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' vFldrName : The name of the folder to return the path of
' Values can be: AllUsersDesktop, AllUsersStartMenu, AllUsersPrograms,
' AllUsersStartup, Desktop, Favorites, Fonts, MyDocuments,
' NetHood, PrintHood, Programs, Recent, SendTo, StartMenu,
' Startup, Templates
'
' Usage:
' ~~~~~~
' sPath = GetWindowsSpecialFldrPath("Desktop")
' sPath = GetWindowsSpecialFldrPath("MyDocuments")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' ********************************************************************************
******
' 1 2016-09-28 Initial Release
'---------------------------------------------------------------------------------------
Function GetWindowsSpecialFldrPath(vFldrName As Variant) As String
'***Special Note: If vFldrName this is dimmed as a string it returns the wrong value ***
10 On Error GoTo Error_Handler
Dim WshShell As Object
20 If IsNull(vFldrName) = True Then GoTo Error_Handler_Exit
30 Set WshShell = CreateObject("WScript.Shell")
40 GetWindowsSpecialFldrPath = WshShell.SpecialFolders(vFldrName)
Error_Handler_Exit:
50 On Error Resume Next
60 If Not WshShell Is Nothing Then Set WshShell = Nothing
70 Exit Function
Error_Handler:
80 MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: " & sModName & "\GetWindowsSpecialFldrPath" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
90 Resume Error_Handler_Exit
End Function
The entire issue is the way the input variable, vFldrName, is dimmed. If it is dimmed as a string it acts weird. Dim it as a variant and it works as expected. Since the documentations that I found simply mentions “The name of the special folder.” and does not specify the type to use and the examples provided were .SpecialFolders(“Desktop”) one would think it is a string, but apparently not.
User daolix on UtterAccess recently answered my post on this subject and provided an interesting answer/solution:
If you want to use a string variable so you have to force a ByVal call at the “Special Folders”.
Your function n is just to expand only to a clip.Function n(sFldr As String) As String Set WshShell = CreateObject("WScript.Shell") n = WshShell.SpecialFolders((sFldr)) End FunctionIf you pass on a “ByRef” variant, a variable which does not correspond to the data type “Variant”, a variant is created at the interface of the function, which contains a pointer to the passed variable as a value. A true “ByRef” transfer does not take place here, this is only “simulated” with the help of the variant, and corresponds rather to a “ByVal” transfer.
If you pass a string variable to this function, the system must dereference twice to get the content, because a string itself is only a pointer. And here probably the function “SpecialFolders” failed. Error from Microsoft? So wanted? No idea.
I tested his answer and it does work.