API Examples

The following sections provide more examples of useful API declarations you can use in your Excel programs. Each example starts with a short description of what the example can do, followed by the actual declaration(s), and an example of its use.

GetComputerName

This API function returns the computer name. This is the name of the computer found under MyComputer, Network Identification:

Private Declare Function GetComputerName Lib "kernel32" Alias _

"GetComputerNameA" (ByVal lpBuffer As String, ByRef nSize As Long) As Long

Private Function ComputerName() As String

Dim stBuff As String * 255, lAPIResult As Long Dim lBuffLen As Long lBuffLen = 255

lAPIResult = GetComputerName(stBuff, lBuffLen) If lBuffLen > 0 Then ComputerName = Left(stBuff, lBuffLen)

End Function

Sub ComputerCheck() Dim CompName As String

CompName = ComputerName

If CompName <> "BillJelenPC" Then MsgBox _

"This application does not have the right to run on this computer." ActiveWorkbook.Close SaveChanges:=False

End If End Sub

The ComputerCheck macro uses an API call to get the name of the computer. In Figure 22.2, the program refuses to run for any computer except the hard-coded computer name of the owner.

Figure 22.2

Use the computer name to verify that an application has the rights to run on the installed computer.

Figure 22.2

Use the computer name to verify that an application has the rights to run on the installed computer.

FilelsOpen

You can check whether you have a file open in Excel by trying to set the workbook to an object. If the object is Nothing (empty), then you know the file isn't opened. But what if you wanted to see whether someone else on a network has the file opened? The following API function returns that information:

Private Declare Function lOpen Lib "kernel32" Alias "_lopen" _

(ByVal lpPathName As String, ByVal iReadWrite As Long) As Long

Private Declare Function lClose Lib "kernel32" _ Alias "_lclose" (ByVal hFile As Long) As Long

Private Const OF_SHARE_EXCLUSIVE = &H10

Private Function FileIsOpen(strFullPath_FileName As String) As Boolean Dim hdlFile As Long Dim lastErr As Long hdlFile = -1

hdlFile = lOpen(strFullPath_FileName, OF_SHARE_EXCLUSIVE)

If hdlFile = -1 Then lastErr = Err.LastDllError

Else lClose (hdlFile) End If

End Function

Sub CheckFileOpen()

If FileIsOpen("\\John\tracy's keepers\XYZ Corp.xls") Then MsgBox "File is open"

Else

MsgBox "File is not open" End If

End Sub

Calling the FilelsOpen function with a particular path and filename as the parameter will tell you whether someone has the file open. Figure 22.3 shows the message indicating the XYZ Corp.xls is open by someone on the network.

Figure 22.3

Verify whether another user on the network has the file open.

Figure 22.3

Verify whether another user on the network has the file open.

0 0

Post a comment