Loading files

[tjnichols]

I have a database that was created in Access 2007. I have moved it to Access 2019 with the Windows 10 update.

The issue I'm having is when I try to run the macro it should open a form to look for a file. It does nothing. I must admit VBA code is by far not my strong suit but here is what I have:

Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _ "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean Declare PtrSafe Function GetSaveFileName Lib "comdlg32.dll" Alias _ "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Boolean

 

[theDBguy]

Hi. If your API code is not working, my guess is you may be using a 64-bit Access now. If so, you can either update your API code to work with 64-bit Access or use a FileDialog object instead of API.

 

[isladogs]

I stopped using GetOpenFileName and GetSaveFileName when I first started coding for 64-bit in 2012 or so Despite converting both the API declarations and the associated Type values, I could never get either to work successfully. That may have been my inexperience at the time, but I know others who had the same problem.
I changed all my code to use FileDialog. It works in both 32 & 64-bit without needing to convert the code

 

[tjnichols]

Ok - can you post the code to replace what I need here?

 

[isladogs]

You could do a forum or web search for FileDialog. The code is widely used and easy to understand.

Here's another thread on the same topic that I answered recently
https://www.access-programmers.co.uk/forums/showthread.php?t=308879&highlight=Filedialog
It includes an example app with code you can adapt

 

[tjnichols]

I looked up the "FileDialog" but couldn't find an example that spelled it out enough for me to apply it to what I need here. Again, this is SOOOO NOT MY STRONG SUIT!! I really appreciate your help!

Here is what I came up with though: - Does it make sense?

Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Boolean
Declare PtrSafe Function GetSaveFileName Lib "comdlg32.dll" Alias _
"GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Boolean


Private Declare PtrSafe Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

 

[theDBguy]

I looked up the "FileDialog" but couldn't find an example that spelled it out enough for me to apply it to what I need here. Again, this is SOOOO NOT MY STRONG SUIT!! I really appreciate your help!

You could try replacing all of that with the code you could find here.

Here is what I came up with though: - Does it make sense?

Did you try it out? What happened?

 

[isladogs]

Sorry but that code will definitely not work
You have two slighlty different GetOpenFileName statements and there are several errors in the Type block

Remember even once I did it correctly, that code still didn't work for me.
I strongly recommend you abandon that approach & scrap all that code

This is typical FileDialog code from the link in my last post
It is used to browse for Access databases starting in the root C:\ drive.
Alter the file types and start folder as appropriate

Private Sub cmdBrowse_Click()
    
On Error GoTo Err_cmdBrowse_Click

   ' Set options for the dialog box.
    Dim F As FileDialog
    Set F = Application.FileDialog(msoFileDialogFilePicker)
    F.Title = "Locate the Access database file and click on 'Open'"
    F.AllowMultiSelect = False
    
   ' Clear out the current filters, and add our own.
      F.Filters.Clear
      F.Filters.Add "Access Databases", "*.accdb;*.mdb"

   ' Set the start folder (modify as necessary)
    F.InitialFileName = "c:\"
    
' Call the Open dialog routine.
    F.Show

' Return the path and file name.
    strFilename = F.SelectedItems(1)
    
 'populate the form control
    Me.txtFile = strFilename

Exit_cmdBrowse_Click:
    Exit Sub

Err_cmdBrowse_Click:
    'err=5, user clicked cancel
    If Err.Number = 5 Then Exit Sub
        
    MsgBox "Error " & err.Number & " in cmdBrowse_Click procedure : " & err.Description
    
    Resume Exit_cmdBrowse_Click
    
End Sub