Logic
Dimension variables.
There are 2 code blocks to dimension oFile,
one of which is commented.
Late-binding, discussed below, is used
Initialize the return value to be 0 (zero)
since no files have been copied yet.
Set n= 0 (zero) where n represents the number of files actually copied.
since no files have been copied yet.
To avoid having to create a variable for a folder, use
With CreateObject("Scripting.FileSystemObject").GetFolder(psPathSource)
where psPathSource
is the path of the source files.
Count the files in that location and assign to nCount.
If there aren't any files, give the user a message that
there are no files to copy,
and exit the function.
Loop through each file in the folder.
Construct a message for the status bar with the file number being processed,
the total number of files,
and the current file name.
Show message in the Status Bar.
If the new file will have a prefix before the rest of the name,
specify that with sPathFileTo.
If there is no extra prefix, sPathFileTo is whatever was sent for psPathTo,
and the file name won't change.
Once copied, increment the file counter,
update the return value for the function,
and loop back to the next file in the folder.
When the loop is done, clear the status bar.
There is no error handling in this code.
It would be good to add it.
Refer to other VBA examples on this site for good practice.
As such, there is also no cleanup, such as clearing the status bar ...
Parameters
psPathSource
the folder to copy files FROM
psPathTo
the folder to copy files TO
if psPrefix
is specified, psPathTo MUST be terminated with a folder separator such as \
Optional Parameters
psMask
pattern for files to copy
other examples: my*.xls*, *.jpg, *.*x
default is "" (all files)
pbOverwrite
True (default) to overwrite file if it already exists.
psPrefix
what to add to the beginning of the filename, if anything.
For instance, the calling program may use something like
"copy_" & Format(Date, "yymmdd") & "_"
default is "", which means that the file name won't change.
Late vs Early Binding
One of the libraries you can reference when writing VBA
is called
Microsoft Scripting Runtime
and contains definitions for
- File
- Folder
- Drive
- FileSystemObject - the whole the file system
- Dictionary
- TextStream
The file this library is in is called
scrrun.dll,
which, for my installation, is located in C:\Windows\SysWOW64
Early Binding
Early Binding means that a reference library
is loaded whenever an application that references it is opened.
A big bonus is that early binding gives you, the developer,
the benefit of
Intellisense
while coding.
This is super-helpful!
This is efficient when running too,
since the library is pre-loaded
and instantly available whenever needed.
In this case, the library is called
Microsoft Scripting Runtime.
The disadvantage, however, is that early-binding makes the code version dependent.
If the user isn't running the same versions,
they will hit an error which could be avoided by late-binding.
Late Binding
When you deploy your application to users,
it is a good idea to consider late binding.
This means that
whatever version of the library is available
will be loaded at runtime when it is needed.
Late-binding is used in this example.
Code to early-bind is there, but commented.
