613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

VBA Chapter 22 of 24: API's in VBA for Excel

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 the other parts 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