Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Microsoft Access file manager in forms?

Posted on 2009-04-03
10
Medium Priority
?
769 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 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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

926 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