Solved

Microsoft Access file manager in forms?

Posted on 2009-04-03
10
744 Views
Last Modified: 2013-11-28
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
Comment
Question by:iamnamja
  • 4
  • 3
  • 3
10 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24061837
see the sample on this link

http://www.lebans.com/callbackbrowser.htm
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24061847
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
 

Author Comment

by:iamnamja
ID: 24062269
capricorn,

I love the link you sent me... but any way to choose the file instead of just the path?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24062420
see this sample db



dbSaveTo.mdb
0
 

Author Comment

by:iamnamja
ID: 24062493
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24062527
the codes is long.. drop me a msg and i'll send it to you see my profile..
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24062654
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
 

Author Comment

by:iamnamja
ID: 24062762
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24062809
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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
ID: 24062844
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now