Doug Steele tries to address commonly asked questions from Access developers. This month, he extends his look at how to add drag-and-drop to an Access application, as well as looking at how to simulate XP Cuing Banners.
I tried implementing your drag-and-drop code from your article in the January 2003 issue. That worked well, but I want to go one step further: Can I drag from other applications to an Access application?
As with drag-and-drop within an Access application, the answer is yes, but you have to do everything yourself. Other applications use OLE drag/drop methods and events to accomplish drag-and-drop, but Access simply doesn’t provide those events on most controls. You can, however, still use them through subclassing and API calls. However, as stated in the Microsoft KB article at
There are two approaches for doing this. The first approach is to use Microsoft Visual Basic or Microsoft Visual C++ to create an ActiveX DLL that subclasses your Microsoft Access forms, and then to reference that DLL from your Microsoft Access application. A second method is to write all the code within Microsoft Access itself without using an ActiveX DLL. Because of problems with subclassing windows after loading the Microsoft Office Visual Basic Editor, Microsoft highly recommends that you use Microsoft Visual Basic or Microsoft Visual C++ to create an ActiveX DLL, and that you then reference the DLL from your Microsoft Access application.
I’m going to take that advice to heart and create an ActiveX DLL using Visual Basic. (For those of you who don’t have Visual Basic, the accompanying Download file includes not only the source for this DLL, but also a compiled version. Using an ActiveX DLL created in Visual Basic requires that you have the Visual Basic runtime files installed. I created the DLL using Visual Basic 5.0; therefore, you must have the VB5 runtime files. If you don’t already have them, you can get them from http://support.microsoft.com/?id=180071. It also requires that the DLL be registered using RegSvr32.exe. If you’re not familiar with using RegSvr32.exe, or you can’t find that program on your hard drive, check http://support.microsoft.com/?id=161983.)
Assuming that you do have Visual Basic installed, here are the instructions to follow to create the necessary subclassing DLL. Note that these instructions will only work with “classic” Visual Basic (VB5 or VB6), not with VB.NET.
Create a new ActiveX DLL in Visual Basic, and add a module (basDragDrop) and a class module (clsDragDrop) to it.
Add the following code to the basDragDrop module:
Option Compare Text Option Explicit Public CDrag As clsDragDrop Public lpPrevWndProc As Long Public Const GWL_WNDPROC = (-4) Public Const WM_DROPFILES = &H233 Public Const GetNumOfFiles = &HFFFF Public Declare Function CallWindowProc _ Lib "user32" _ Alias "CallWindowProcA" ( _ ByVal lpPrevWndFunc As Long, _ ByVal hWnd As Long, _ ByVal Msg As Long, _ ByVal wParam As Long, _ ByVal lParam As Long _ ) As Long Public Declare Function SetWindowLong _ Lib "user32" _ Alias "SetWindowLongA" ( _ ByVal hWnd As Long, _ ByVal nIndex As Long, _ ByVal dwNewLong As Long _ ) As Long Public Declare Sub DragAcceptFiles _ Lib "shell32.dll" ( _ ByVal hWnd As Long, _ ByVal fAccept As Long _ ) Public Declare Sub DragFinish _ Lib "shell32.dll" ( _ ByVal hDrop As Long _ ) Public Declare Function DragQueryFile _ Lib "shell32.dll" _ Alias "DragQueryFileA" ( _ ByVal hDrop As Long, _ ByVal lFile As Long, _ ByVal lpFileName As String, _ ByVal cbLen As Long _ ) As Long Public Function WindowProc( _ ByVal hWnd As Long, _ ByVal Msg As Long, _ ByVal wp As Long, _ ByVal lp As Long _ ) As Long If Msg = WM_DROPFILES Then 'Files have been dropped CDrag.AcceptDroppedFiles wp Else WindowProc = CallWindowProc( _ lpPrevWndProc, hWnd, _ Msg, wp, lp) End If End Function
Add this code to the clsDragDrop class module:
Option Compare Text Option Explicit Private objForm As Object Private objTextBox As Object Public Property Set Form(FormIn As Object) Set objForm = FormIn End Property Public Property Set TextBox(TextBoxIn As Object) Set objTextBox = TextBoxIn End Property Public Sub SubClassHookForm() Call DragAcceptFiles(objForm.hWnd, 1) lpPrevWndProc = SetWindowLong(objForm.hWnd, _ GWL_WNDPROC, _ AddressOf WindowProc) Set CDrag = Me End Sub Public Sub SubClassUnHookForm() Call SetWindowLong(objForm.hWnd, _ GWL_WNDPROC, _ lpPrevWndProc) Call DragAcceptFiles(objForm.hWnd, 0) Set CDrag = Nothing End Sub Sub AcceptDroppedFiles(hDrop As Long) Dim lngNumOfFiles As Long Dim lngReturn As Long Dim strFileName As String Dim lngLoop As Long ' Get the number of dropped files lngNumOfFiles = DragQueryFile(hDrop, _ GetNumOfFiles, 0&, 0) For lngLoop = 0 To lngNumOfFiles ' Allocate buffer for the name of the file strFileName = String$(257, Chr$(0)) ' Get the name of the file lngReturn = DragQueryFile( _ hDrop, lngLoop, _ strFileName, Len(strFileName)) ' Add the file name to the list If lngReturn > 0 Then objTextBox.Text = objTextBox.Text & _ Left$(strFileName, lngReturn) & _ vbCrLf End If Next lngLoop ' Tell Windows to free memory allocated to store ' the dropped files DragFinish hDrop End Sub
Compile this code to DragDrop.dll.
I don’t intend to walk through all of the code associated with the ActiveX DLL. What it does is allow you to specify a text box on your Access form to subclass, and then uses DragAcceptFiles, DragFinish, and DragQueryFile (among others) to allow you to determine what’s being dropped on that subclassed text box.
Make sure that the ActiveX DLL has been registered. If you compile the code, the DLL will be registered. If you’re using a version from someone else, use RegSvr32.exe to register it. Once it’s been registered, you need to add a reference to it through Tools | References (see Figure 1).
Once all that has been taken care of, you’ll need to add some code to your Access form to use the DLL. After the Options, but before any modules, add a declaration for a variable that will be instantiated as an instance of the class included in the DragDrop.dll:
Option Compare Database Option Explicit Dim cDrag1 As clsDragDrop
In the Form_Load event, add code to instantiate the instance of the class, and subclass the form and text box (in the following sample, I’m using a text box control named txtTarget):
Private Sub Form_Load() Set cDrag1 = New clsDragDrop Set cDrag1.TextBox = Me.txtTarget Set cDrag1.Form = Me cDrag1.SubClassHookForm End Sub
Finally, in the Form_Unload event, add code to unsubclass the form, and clean up after yourself:
Private Sub Form_Unload(Cancel As Integer) cDrag1.SubClassUnHookForm Set cDrag1.Form = Nothing Set cDrag1.TextBox = Nothing Set cDrag1 = Nothing End Sub
That’s it. You can now drag text from any application that supports OLE drag-and-drop to your subclassed text box!
An important reminder: As stated earlier, ActiveX DLLs need to be registered on each workstation where they’re used. When you compile the DLL on your workstation, it automatically gets registered. However, if you simply copy the DLL to another workstation, you need to use RegSvr32.exe to register it. As well, don’t forget that it’s necessary to have the appropriate VB runtime files. If you’re using this in an application that you’re distributing to other users, ensure that all necessary files are included, and that you properly register the DLL before they attempt to use the application.
And, sorry, but one thing I haven’t been able to do yet is come up with a way to drag from Access to another application (or, for that matter, from one Access application to another). If anyone has a suggestion, please let me know!