Using Windows API To The Worksheet in VBA
When developing Office or Excel based applications in particular, we can get most things done by using the Office / Excel object model. Occasionally though, we need features that VBA doesn't provide. In these cases, we have to use the function calls that are available in Windows.
In the userform's code module, paste the API function declarations at the top of the module, outside of and before any Sub or Function or Property procedures.
Get Windows User Name
If you need to know who's logged into an database, use GetUserName. This API is so simple, why would you bother writing your own code? GetUserName retrieves the name of the current system or the current user logged into the network.
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function apicGetUserName() As String
'Call to apiGetUserName returns current user.
Dim lngResponse As Long
Dim strUserName As String * 32
lngResponse = GetUserName(strUserName, 32)
apicGetUserName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
End Function
BringWindowToTop
This API function brings the specified window to the top. If the window is a top-level window, the function activates it.
(ByVal lngHWnd As Long) As Long
When calling the function, pass the window handle value as a Long variable.
FindWindow
The can be a bit frustrating because it requires specialized information, and if you don't get it just right, the function won't work. Specifically, you need the window's class or name, as shown in the simple call below.
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Setting Focus To The Worksheet
Private Declare Function BringWindowToTop Lib "user32" _
(ByVal lngHWnd As Long) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (_
ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (_
ByVal HWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Private Const WM_SETFOCUS = &H7
Private Sub SetSheetFocus()
Dim HWND_XLDesk As Long
Dim HWND_XLApp As Long
Dim HWND_XLSheet As Long
HWND_XLApp = Application.HWnd
HWND_XLDesk = FindWindowEx(HWND_XLApp, 0&, "XLDESK", vbNullString)
HWND_XLSheet = FindWindowEx(HWND_XLDesk, 0&, "EXCEL7", ActiveWindow.Caption)
SendMessage HWND_XLSheet, WM_SETFOCUS, 0&, 0&
End Sub
Private Sub SetAppFocus()
Dim HWND_XLDesk As Long
Dim HWND_XLApp As Long
HWND_XLApp = Application.HWnd
SendMessage HWND_XLApp, WM_SETFOCUS, 0&, 0&
End Sub
Function apicFindWindow(strClassName As String, strWindowName As String)
'Get window handle.
Dim lngWnd As Long
lngWnd = FindWindow(strClassName, strWindowName)
End Function
GetComputerName
GetComputerName, is similar to GetUserName except it retrieves the system's name.
Private Declare Function GetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function apicGetComputerName() As String
'Call to apiGetUserName returns current user.
Dim lngResponse As Long
Dim strUserName As String * 32
lngResponse = GetComputerName(strUserName, 32)
apicGetComputerName = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
End Function
Embed MS Office in ASP.NET Program
An Easy Way to Embed Excel in a Web Page
