Solved

Microsoft Access file manager in forms?

Posted on 2009-04-03
10
755 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

631 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