|
VBA
for Excel Index and Search Tool (Click
on the buttons below) |
VBA Part 22 of 25: Working with API's in VBA for ExcelAPI 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"
_ 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()
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"
_ "DeleteFileA" (ByVal lpFileName As String) As Long ' Declaration Sub proCopyFile() Dim
varCopy As Long ActiveWorkbook. Save As "C:\" & varCreatedWorkbook varCopy
= CopyFile("C:\"
& varCreatedWorkbook,
"E:\"
& varCreatedWorkbook,
1) 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) |