Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-13: Other Objects Other Code in VBA for Excel
(Levels: Advanced)

API stands for Application Programming Interface and consists of a collection of functions that provide programmatic access to the features of the operating system (Windows). When you use API's within VBA for Excel not only do you control Excel but all other parts of your version of Windows.

Here is how it works if you want to copy a file from a directory to the other. You must first declare the API function that you want to use in your code (Private Declare....CopyFile...). Then you call it from you procedure (Sub proCopyFile...).

Notice that you must ALWAYS use a variable to receive the result of an API function (varCopy = CopyFile("C:\amy.xls" , "E:\SCI\amy.xls" , 1))

Option Explicit

Private Declare Function CopyFile Lib  kernel32.dll" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long

Sub proCopyFile() 

Dim varCopy  As Long

        varCopy = CopyFile("C:\amy.xls" , "E:\SCI\amy.xls" , 1)

        If varcopy< >   0 Then  MsgBox "Copy succeeded."

End Sub

Here is an example of code to delete a file in a certain directory. Remember that when you use this API the file that   you delete doesn't go into the Recycling Bin and is not retrievable.

Option Explicit

Private Declare Function DeleteFile Lib "kernel32.dll" Alias _
"DeleteFileA" (ByVal lpFileName As String) As Long

Sub proDeleteFile()
        Dim varDelete As Long ' return value
      varDelete = DeleteFile("C:\amy.xls")
        If varDelete = 1 Then MsgBox "File deleted."
End Sub

If you work on a network and the saving time of a file is too long you can use both functions. I have created an application that needed to save hundreds of files to a network and I created this procedure. The time saving was very significant. In this example the active workbooks file names are stored in a variable called "varCreatedWorkbook".

Option Explicit

Private Declare Function CopyFile Lib "kernel32.dll" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long

Private Declare Function DeleteFile Lib "kernel32.dll" Alias _
"DeleteFileA" (ByVal lpFileName As String) As Long

Sub proCopyFile() 

Dim varCopy  As Long
Dim varCreatedWorkbook as String

        ActiveWorkbook. Save As "C:\" & varCreatedWorkbook

        varCopy = CopyFile("C:\" & varCreatedWorkbook, "E:\" & varCreatedWorkbook, 1)
        varDelete = DeleteFile("C:\" & varCreatedWorkbook)

End Sub

To delete the file you could also use the Kill statement that is not an API.

Kill "C:\" & varCreatedWorkbook

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple