Access VBA: File picker with filters - SAVE AS

[ironfelix717]

Hi,

If you've ever used the msofiledialog object in VBA, you know that is severely lacking one large component, which is the ability to add a filter to the file SAVE AS dialog, which is sad.

Some people tend to think this is possible, which it is not true and I cleared up a lot of confusion that was going around here:
MS Access - File SAVE AS dialog with custom filter

I am writing to see if anyone has solved this issue. I know there are a few ways to prompt for file dialogs in Access VBA. But mso is the easiest method. I would prefer not to have 1000 lines of API code just to open a save dialog with some filters.

Open to hearing ideas.
Thanks!

 

[theDBguy]

Hi. Just curious, which "workaround" did you end up using? If your immediate problem is specific to a particular file extension, can I ask which one is it?

 

[ironfelix717]

DBGuy,

There is no workaround which is why i've posted here. I know there is API code for this somewhere out there...which i've tried a lot of it and its uselessly outdated. I don't really understand the second question.


This has been discussed here....

Filter in msoFileDialogSaveAs

have a function that shows me the saving path choosen by user The problem is I can't set filter to *accdb. If I work with msoFileDialogFilePicker then .filter property works. What should I do to set filter *accdb in msoFileDialogSaveAs? Function Plik(TytulOkna As String, TytulPrzycisku As...

www.access-programmers.co.uk

And here....

Filters for Save as filedialog (probably revisited)

A number of postings on the web (and in this Forum) claim that Application.Filedialog(msoFileDialogSaveAs) is "problematic" or doesn't work, and point towards a Windows API : http://www.mvps.org/access/api/api0001.htm In my Access 2007, the...

www.access-programmers.co.uk

And here....

UtterAccess.com

www.utteraccess.com

And here....

VBA Excel FileDialog to set/reset filters

I have a macro that asks a user to choose multiple files for data analysis. User selects a Excel or CSV file first (XLSX, XLS, CSV), then asks for a second file but CSV only. The intent of the tool...

stackoverflow.com

Thanks

 

[theDBguy]

Hi. Thanks for the update. I asked about the file extension because, for example, if you were only concerned with saving Excel files right now, I was going to suggest using the SaveAs dialog in Excel. Just a thought...

 

[ironfelix717]

DBguy,

Can Excel's SaveAs dialog be implemented in Access?

 

[theDBguy]

DBguy,

Can Excel's SaveAs dialog be implemented in Access?

That's where my mind was going, but I didn't pursue it yet pending your answer to my previous question. I was hoping it could be. I actually don't see why you can't. I just haven't tried it yet.

 

[ironfelix717]

DBGuy,

If i recall off the top of my head, Excel file dialogs are prompted with the Application object, such as...

Application.FileDialog(type)

where type is the dialog (open/save, etc). But i'm pretty sure the same object library that i'm using in Access is being inherited by Excel's application object, so the result would be the same. Unsure on this though.

 

[theDBguy]

DBGuy,

If i recall off the top of my head, Excel file dialogs are prompted with the Application object, such as...

Application.FileDialog(type)

where type is the dialog (open/save, etc). But i'm pretty sure the same object library that i'm using in Access is being inherited by Excel's application object, so the result would be the same. Unsure on this though.

Well, I wasn't thinking about the Application FileDialog, but the SaveAs method of the (I think) Worksheet object.
Sent from phone...

 

[MajP]

But mso is the easiest method. I would prefer not to have 1000 lines of API code just to open a save dialog with some filters.

I dropped the API code into Word to count it is 112 lines of code and 986 words. 10 times less than you said. But who give a crap, it is a black box. It could be 10k lines of code. It only takes one line to implement.
You will never learn how to code, if you can not take someone elses work and use it. You do not have to build the watch, only use it to tell time.
Perfect example. I use this sometimes to build a treeview

Excel: An msforms (all VBA) treeview; How to use

jkp-ads.com: VBA treeview control How To Use

jkp-ads.com

It is legitimately thousands of lines of code. I could spend hours figuring it out how it works, but I am happy just using it. It takes me a few lines of code to use it. So who cares how complex the black box is.

 

[ironfelix717]

DBGuy:

Well, I wasn't thinking about the Application FileDialog, but the SaveAs method of the (I think) Worksheet object.

Hmmm... Thats an interesting approach. I would have to think about that. My guess is it wouldn't work, but unsure until i tried.


MajP:

The comment of "1000 lines of API Code" is a sarcastic reference--which is that I shouldn't expect to even touch the API with a comically trivial task. MS put the

.Filters.Add

method inside their msofiledialog object for the SaveAs dialog, yet it does not function properly. Its not even documented, as far as I know. I'm not opposed to API code - but rather, shouldn't need it. BTW, have you tried the API code besides putting it in Word. Does it actually... work?

You will never learn how to code, if you can not take someone elses work and use it.

Spare me the insult to my intelligence. As a matter of fact, I think i'm using someone else's work right now in a project. Can you guess who might that be?

--Regards

 

[MajP]

Does it actually... work?

If referring to this

API: Call the standard Windows File Open/Save dialog box

For years it was the only way to accomplish this. I am guessing until 2007 (maybe 2003).
This was written by Litwin and Getz, the Gods of Access so it works well. So it is littered in hundred of old databases of mine. Likely not 64 bit compatible, but I bet @isladogs has a 64 bit version. I do not know a anything about API so have to go to the experts to get updated 64 bit products. But @lsladogs has the updated color picker and folder picker in 64 bit.
@sonic8 is the man when it comes to 64 bit API conversion

Font Dialog (64 Bit compatible) or Get List of Fonts

I had code that brought up the font dialog, but have been unsuccessful for getting it to work with 64 bit. Anyone have a working version? If not anyone have code to get the list of installed fonts? I have seen some excel versions, but again unable to get it to work with Access. Thanks.

www.access-programmers.co.uk

converted the Font picker to 64 bit. I scoured the internet and everyone said it could not be done.

Spare me the insult to my intelligence. As a matter of fact, I think i'm using someone else's work right now in a project.

Sorry did not mean to sound that way. Just to say I rely on other peoples code a lot and may not understand how it is built as long as it is solid and works well. I use a lot of Isladogs "precision positioning" and "Wizhook" and "controlling the Access window" stuff a lot. It would take me a long time to figure out how it was built.

 

[theDBguy]

DBGuy:


Hmmm... Thats an interesting approach. I would have to think about that. My guess is it wouldn't work, but unsure until i tried.

Hi. Please remember I made that suggestion based on if you were trying to force the user to only specify Excel file extensions in the SaveAs dialog box. I would like to know what you discover once you get a chance to try it out. Good luck.

 

[zeroaccess]

How about this?

Paste into a new Class Module and name it BrowseForFileClass.

To use it, make a command button with the following On Click:

Private Sub cmdBrowseBackUp_Click()
    On Error GoTo Err_Handler
    
    Dim OpenDlg As New BrowseForFileClass
    Dim strFullPath As String
    
    OpenDlg.DialogTitle = "Select or Enter Back Up File"
    strFullPath = OpenDlg.GetFileSpec
    Set OpenDlg = Nothing
    
    If strFullPath <> "" Then
        Me.BackUpPath = strFullPath
    End If
    
Exit_Here:
    Exit Sub
    
Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here
End Sub

...and modify to taste.