• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

Microsoft Access file manager in forms?

Hi,

I'm trying to create  a basic main page for a few macros in my access database.  There are two macros that both require it to fetch data/export data onto another excel sheet.

Everything works well assuming that the files do not change their location, but I would like to give the user the option to change the file path if they desire.

e.g. i would like to have a space with the current default file path, and if they wish to change it, then they can click a button to go to file manager and choose the file path to overwrite the default file path.  

would this be possible?  Basically, i just need a way to access the explorer and when a user click on a file, it'll save that file path as the new file to export/import data onto.

Also, if that is possible, how do i save the data so that my macro can access this "path"?  Thanks!
0
iamnamja
Asked:
iamnamja
  • 4
  • 3
  • 3
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
see the sample on this link

http://www.lebans.com/callbackbrowser.htm
0
 
rockiroadsCommented:
I have sample code that brings up the dialog here
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24284599.html?cid=236#24039640

just need to change it to specify the filter.

question is, do you want them to pick a file or a directory? if a directory I have that code as it differs from this
0
 
iamnamjaAuthor Commented:
capricorn,

I love the link you sent me... but any way to choose the file instead of just the path?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Rey Obrero (Capricorn1)Commented:
see this sample db



dbSaveTo.mdb
0
 
iamnamjaAuthor Commented:
capricorn,

i can't download anything here (dont know why)... so, would it be possible for you to post the code for me?  Thanks.
0
 
Rey Obrero (Capricorn1)Commented:
the codes is long.. drop me a msg and i'll send it to you see my profile..
0
 
rockiroadsCommented:
Did u not try out the code in the other ee thread I gave? thats pretty short.

Just paste this in a new module


Private Declare 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

Function PromptForFile(ByVal sFilter As String, lHwnd As Long) As String
   
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
   
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = lHwnd
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "C:\"
    OpenFile.lpstrTitle = "Select a document"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
    If lReturn = 0 Then
        PromptForFile = ""
    Else
        PromptForFile = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
     End If
End Function





then to call it from a form, just call the method PromptForFile passing in optional filter and form handle (me.hwnd). You can specify your own filters so just ask for .txt files or whatever


0
 
iamnamjaAuthor Commented:
rockiroads, i tried, but for some reason i couldn't get it to open up any file explorer screen.

And without any comments, i had a bit of a hard time following.  Maybe i'm doing something wrong here?
0
 
rockiroadsCommented:
what did you pass in when you called it?
did u call it from a form or module?

eg
from a form

sFile = PromptForFile("", Me.hwnd)

from a module

sFile = PromptForFile("", Application.hWndAccessApp)

this will return a filename with full path

with regards to filters, you create like this


sFilter = "Description" & chr(0) & "suffix"
eg
Description = Documents (*.txt)
suffiix = "*.txt"

There are two specified here, the 2nd allows for all files


sFilter = "Documents (*.txt)" & Chr(0) & "*.txt" & Chr(0) & _
                "All Files (*.*)" & Chr(0) & "*.*" & Chr(0)

then pass this sFilter in as first argument
0
 
rockiroadsCommented:
with comments

'Windows API to prompt for file
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

'Structure required for api
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

Function PromptForFile(ByVal sFilter As String, lHwnd As Long) As String
   
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
   
    'Need to reserve space which is size of structure
    OpenFile.lStructSize = Len(OpenFile)
   
    'Specify application handle
    OpenFile.hwndOwner = lHwnd
   
    'Specify filters
    OpenFile.lpstrFilter = sFilter
   
    'If multiple filters, specify which filter you want to start with
    OpenFile.nFilterIndex = 1
   
    'Reserve space for resulting filename
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
   
    'Titles
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
   
    'Specify initial directory
    OpenFile.lpstrInitialDir = "C:\"
   
    'Specify title
    OpenFile.lpstrTitle = "Select a document"
   
    OpenFile.flags = 0
   
    'Call api
    lReturn = GetOpenFileName(OpenFile)
   
    'If no file selected then return empty string
    If lReturn = 0 Then
        PromptForFile = ""
    Else
        'Returning file has spaces so need to remove extra spaces
        PromptForFile = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
     End If
End Function
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now