Manipulating Files

When you want VBA to read file names, change file names, change file extensions, count files, change file attributes such as read only then you must use an antiquated part of VBA that relies on a script command.
Here is a bas file. Either right button the bas file and select "Save Target As...." and then import the bas file into your VBA code.
Alternatively select the bas file with the left button of your mouse and open the bas file.
You can select all and copy it into your VBA code. It should be self explanatory although complex.

Here is some code that replaces all of the under-scores with spaces in all file names within a named folder on your desktop.

        Dim objFOLDER As Object
        Dim objMAIN_FOLDER As Object
        Dim objFILES_ALL As Object
        Dim objFILE As Object
        Dim intFILE_DETAILS As Integer

        Set objFOLDER = Interaction.CreateObject("Scripting.FileSystemObject")
        'Specify the folder to examine.
        'Insert your details in the locations marked with <.....>.
        'Notice that there is no back-slash at the end of the folder path.
        Set objMAIN_FOLDER = objFOLDER.GetFolder("C:\documents and settings\<YOUR_NAME>\Desktop\<YOUR_FOLDER_NAME>")
        Set objFILES_ALL = objMAIN_FOLDER.Files
        For Each objFILE In objFILES_ALL
                'Check that the file name contains "_". If it does replace all the undercore characters.
                'Note that it does not search any folders within the folder.
                If InStr(1, objFILE.Name, "_") > 0 Then
                        objFILE.Name = Replace(objFILE.Name, "_", " ")
                End If
        Next

        'Now reset the memory allocated to every variable defined earlier with the command "Set".
        objFILES_ALL = Nothing
        Set objMAIN_FOLDER = Nothing
        Set objFOLDER = Nothing




Selecting Folders or Files

VBA does not have an easy way of graphically selecting folders or files with a mouse. You need to use API commands.
I do not understand these, but standing on the shoulders of others you can use API commands.
Using a graphical user interface to select a file or folder does not open the file or folder.
Instead the code outputs the full path to the file/s or folder. You the use the path to open the file or path.

Here is some API based code, 32 bit Folders32Bit.bas and a 64 bit version Folders64Bit.bas that will display a directory structure of your computer.
You have the option to show the files as well.
They create a VBA version of Windows Explorer. You cannot unfortunately double click on a file or folder to accept.
The 32 bit version is by Jim Rech, Ken Getz and Michael Kaplan. I have modified their version to suit a 64 bit system.

Download the .bas file for your system and import the bas file into CorelDraw, Photo-Paint, Excel, Word, Access, Publisher or PowerPoint.

In your VBA code add

Dim strDESTINATION As String
strDESTINATION = <A reference to FOLDER_SELECTION>

The reference to FOLDER_SELECTION depends on the application and where the module Folders is located relative to your code calling the function FOLDER_SELECTION.

The variable strDESTINATION can be renamed to anything you like. When you run your VBA code a dialog box showing your computer's directory structure will appear.
After you have selected a folder or file your code strDESTINATION will contain the full path and name of the folder you selected.
With your own VBA code you can now examine and manipulate the contents of this folder or file.

At the end of the Folders code there is a sample program called TEST_DIALOG. Run this to see the dialog box.
There is also instructions on how to reference FOLDER_SELECTION and some options.

Here is an alternative file selector. It can select single or multiple files.
Its dialog box is the same as that used by windows. Here is a 64 bit version FileBrowser64Bit.bas



CorelDraw has a very good File Browser written in CorelScript, GetFileBox, but it still works in VBA. It is compact but you can only select single files.

       CorelScriptTools.GetFileBox(([Filter as String], _
         [Title as String], _
         [Type as Long], _
         [Default File to Select as String], _
         [Extension as String should the user not specify an extension], _
         [Default Folder as String], _
         [Button as String]) as String

The GetFileBox function by itself does not open or save a file; it only returns a string corresponding to the selected file.
It returns the selected filename and its full path, or an empty string if the user chooses Cancel.

Filter
The filter is text that alternates between text that describes the filter type and the extension filter.
Between each description and extension type is a | symbol.
If there are multiple extensions allowed with the one type of description separate the extensions with a semi-colon.

GetFileBox(File description|File type;Another file type|2nd File description|File type, Title of dialog box, Find or Save, Default file name or full path of default file if no default folder is specified or *.* for all files, , Default folder)

Title
If not specified, "Open" is displayed for an Open dialog box and "Save As" is displayed for a Save As dialog box.

Type
Numeric expression specifying the type of dialog box to display:
0 File Open dialog box (default if omitted)
1 File Save dialog box (Change the title to change to SaveAs.)
This means that this same function can be used for both Open, Save and SaveAs.
In all cases it returns the full file path that you use in
    OpenDocument(FileName As String, [CodePage As Long]) As Document
    ActiveDocument.SaveAs FileName As String, [SAVE_OPTIONS]

Default File to Select
Text to display in the File name text box of the dialog box. If not specified, the text box is empty.
If no Default Folder is specified then it can be specified here but it must also include a file even if it is just *.* or *.cdr

Extension
String expression specifying the default extension to append to a File name if the user omits the extension.

Default Folder
String expression specifying the default folder used by the dialog box.
If not specified, or the specified folder does not exist, the current folder is used.

Button
String expression specifying a button name to override the Open or Save button in the dialog box.
If not specified, the button's name remains unchanged.
I have not found this to work.

Examples
    Dim strPath As String
    ´Show all file types.
    strPath = CorelScriptTools.GetFileBox("All Files (*.*)|*.*", "Select a file", 0, "*.cdr", , "D:\")
    ´Show bitmap files of types bmp, tif gif and cpt plus text files of types txt and asc.
    strPath = CorelScriptTools.GetFileBox("Bitmap files|*.bmp;*.tif;*.gif;*.cpt|Text files|*.txt;*.asc, "Select a file", 0, "Rock.tif", , "D:\")
    ´Show CorelDraw files of types cdr plus text files of type txt.
    strPath = CorelScriptTools.GetFileBox("CorelDraw files|*.cdr|Text files|*.txt|All Files|*.*", "Select a file", 0, "Test.cdr", , "D:\")



Handling Num-Lock, Caps-Lock & Scroll-Lock

VBA does not have a way of activating Num-Lock, Caps-Lock or Scroll-Lock other than by using SendKeys.
The use of Sendkeys often turns off Num-Lock which is very annoying particularly to other people using your program. If you need to control Num-Lock use API's or use some 3rd party code.

Here is some API based code Num_Lock.bas that will activate Num-Lock. There appears to be no direct code that will activate Num-Lock every time only a means to toggle it on or off.
To overcome this, this bas file first checks that Num-Lock is off then runs code to toggle Num-Lock on. If Num-Lock is already on then it does not toggle Num-Lock and so leaves Num-Lock on.

You can import the bas file into your CorelDraw project so that it is stored within your CorelDraw file. If you want to run it when other CorelDraw files are open you must place it in each CorelDraw file.
The better alternative is to place it within CorelDraw itself so that whenever CorelDraw opens it is available to every CorelDraw file. To do this import the bas file into your "GlobalMacros" project.

Put the following code into your CorelDraw project to run the Lock_Numbers macro stored in your GlobalMacros.
'GMSManager.RunMacro "Name of gms project", "Name of module.Name of macro"
GMSManager.RunMacro "GlobalMacros", "Num_Lock.Lock_Numbers"

Alternatively if you import the bas file into your CorelDraw file simply inserting the command Lock_Numbers into your code will run the module and lock Num-Lock.

As stated earlier run Lock_Numbers some time after the last SendKey command to re-Lock Num-Lock.

If you study the bas file, I have left in the options to control Caps-Lock and Scroll-Lock.
In the code, if Const VK_LOCK = 144 then you control Num-Lock but if you want to control Caps-Lock or Scroll-Lock change the value from 144 to 20 or 145 respectively.

A Function to Control all 3 Key Locks

Here is Change_Locks.bas that allows complete control of locking and un-locking of the 3 keys; Num-Lock, Caps-Lock and Scroll-Lock.
Download the bas file then import it into either CorelDraw's GlobalMacros project or into your CorelDraw file.
At the end of the bas file is Sub TEST() so you can test the function and it contains instructions for its use.
Note that this bas file can be imported into any application that runs VBA such as Access, Excel and Word.
Within the bas file there are instructions to run the function in CorelDraw, Excel and Word.



Dealing with the Registry

CorelScriptTools.RegistryQuery

Corel has the corel script function CorelScriptTools.RegistryQuery(MainKey As Long, SubKey As String, Value As String) As Variant.

MainKey
    Specifies the main registry value key to query:
    0 = HKEY_CLASSES_ROOT
    1 = HKEY_CURRENT_USER
    2 = HKEY_LOCAL_MACHINE
    3 = HKEY_USERS
    4 = HKEY_PERFORMANCE_DATA
    5 = HKEY_CURRENT_CONFIG
    6 = HKEY_DYN_DATA

SubKey
    Specifies the sub registry value key to query. This must be a complete key path.

Value
    Specifies the registry value key to query.
    To specify a default value, use an empty string. e.g. ("").


To know the last URL typed into Internet Explorer.
    Dim RECENT_TYPED_URL As Variant
    RECENT_TYPED_URL = _
      CorelScriptTools.REGISTRYQUERY(1, "Software\Microsoft\Internet Explorer\TypedURLs", "url1")


To Determine a Font File Name
    Dim FONT_FILE_NAME As Variant
    FONT_FILE_NAME = _
      CorelScriptTools.REGISTRYQUERY(2, "Software\Microsoft\Windows NT\CurrentVersion\Fonts", "Arial (TrueType)")

Unfortunately it is not possible using this method to read each font unless the each font name is known.


RegObj.dll

This gives you the user friendliness of VBA but accesses the Windows API on you behalf.
Unfortunately it only deals with the registry not the rest of windows.
It allows you to read, modify, create and delete in the registry.

Because of the limitations of this free site. I am unable to store zip, rar or exe files.
To get around it I have removed the last "e" from the RegObj.exe file so it reads as Regobj.ex.
Please download RegObj.ex and manually add the e to the file extension so that it reads as RegObj.exe.

Run RegObj.exe to install RegObj.dll and it will install in C:\VBWebSamples\RegObj.
It will appear in the VBA References as Registration Manipulation Classes.
Unfortunately there is no help file but the document that is in the folder C:\VBWebSamples\RegObjcomes is excellent.


In this example it reads every installed Windows Font

  Dim objREGKEY As RegKey
  Dim lngFONT_COUNT As Long
  Dim strFONT_NAME As String
  Dim strFONT_FILE As String
  Dim regFONT As RegValue

  Set objREGKEY = _
    RegKeyFromString("\HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Fonts")

  'Total No of fonts.
  lngFONT_COUNT = objREGKEY.Values.Count

  'For each installed font.
  For Each regFONT In objREGKEY.Values

      'The first font's name is
      strFONT_NAME = regFONT.Name

      'The first font's file name is
      strFONT_FILE = regFONT.value

  Next

Automatic downloading from the internet is not well documented.
I have a way that pastes into PhotoPaint then saves the bitmap but here is method that does not rely on any particular application only the use of VBA and API's.

Here is an Excel file. The module and class modules also work in CorelDraw.

In the module DownloadHTTP you must personalise the destination address for your computer.
There is no need for you to alter the class modules. Run DownloadHTTP in the module RunModule to save a file.


Adding a Progress Meter

Windows 32 Bit Versions
Whilst VBA code is running it can be handy so see a moving line to indicate that the code is still progressing and it enables you to estimate when it will be completed.
Microsoft Access has a built-in progress meter but for other VBA programs you can use MSComCtl.ocx that is located in Windows\System32 if you are using a 32 bit Windows version such as XP or 7.
For 64 bit Windows versions MSComCtl.ocx does not work properly.

To use MSComCtl.ocx you will have to search for the actual file. It does not appear amongst the registered VBA files. Also by default the Reference Dialog Box only it shows *.olb, *.tlb and *.dll files.
After MSComCtl.ocx is added as a reference you can then add the the VBA ToolBox.
Insert a UserForm right button the ToolBox and select Add Additional Controls and then select Microsoft ProgressBar Control 6.0 (SP6).
Once selected a small cross will appear beside the text. Select OK and now there will be a new icon in the Toolbox circled in this picture below.



Select the icon then add the control to a form. You can alter the progress bar width, length etc.

Here is a CorelDraw X5 file that will open in either CorelDraw X5 or X6.
There is no need to reference MSComCtl.ocx or add it to the toolbar as the code does this for you.
The progressbar is started by running the procedure Run_This in the ProgressCode module.



If you have an earlier version of CorelDraw you will have to download the following 3 files by right clicking and selecting save target as ProgressCode.bas, UserForm1.frm and UserForm1.frx.
Import both the ProgressCode and the UserForm into a CorelDraw file. Note that the 2 files, .frm & .frx, will import as 1 file.
Then you must make reference to MSComCtl.ocx that is located in Windows\System32.

The code is started by the running the procedure Run_This in the ProgressCode module.

In use it is advisable to update the progress meter not every time that data is entered or calculated.
Instead update the progess meter less frequently such as every new page or every 10 or 100 loops otherwise constant updating of the ProgressMeter slows your code.