Excel VBA Online Consulting		Downloads
Tips on Excel		Tips on VBA for Excel

VBA for Excel Index and Search Tool (Click on the buttons below)

VBA Part 22 of 25: Working with API's in VBA for Excel

API stands for Application Programming Interface and consists of a collecton of functions that provide programmatic access to the features of the operatimg 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 unretreivable.

Option Explicit

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

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 ' Declaration

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

VBA for Excel Index and Search Tool (Click on the buttons below)

Comments: Click Here		Excel VBA Online Consulting
Tips on Excel	Downloads	Tips on VBA for Excel