vba to browse and save full filename

[ellenr]

I have a button allowing the user to browse and save a file location for an email attachment as part of the setup screen for a cdo send email routine. It is code I downloaded many years ago and has worked until recently (last documented use was 9/28/2019). Now, instead of grabbing the full path/filename it only puts "C:\Users\" into the text box AttachmentLocation. Any help would be appreciated. I have attached code snippets.

 

[June7]

Should post code in thread between code tags.

 

[isladogs]

Have you recently changed to 64-bit Access?
When I tried this around 2014 or so, I never managed to get GetOpenFileName to work in 64-bit even though I converted the APIs correctly.
I changed all such code to use FileDialog instead. The code is far simpler and works in both 32-bit & 64-bit
Suggest you do the same

 

[The_Doc_Man]

Well, for one thing you have two Private Sub declarations nested inside one another, or else some other bracketing is missing from what you presented. See "Command40_Click" in your BrowseButton_Click declaration.

Also, just to be pedantic about it, you use .Value when you don't have to. For all controls that HAVE a value, .Value is the default so can be omitted. (And for all controls that DON'T have a value, using .Value would raise the error of "Unknown property").

 

[vba_php]

(And for all controls that DON'T have a value, using .Value would raise the error of "Unknown property").

well now Richard, *THAT* is useful information! thanks so much, sir. =)

ellen,

per this statement of yours"

I have a button allowing the user to browse and save a file location for an email attachment as part of the setup screen for a cdo send email routine.

you have quite a bit of code just to do a simple task like that! Why can't you just use File Dialog ??

That object has a property called type and that property has a feature in it called the Folder Picker, of which the purpose is to do exactly what you want. That would literally reduce all of your code down to 10 lines or so. maybe less.

<edit>
maybe you are already using this object? if so, I'm sorry for this post. If that's the case, then maybe I can say something else useful: I have never had an issue using the folder picker, and I'm currently running version 2016 office-365. is that what you're using?

 

[ellenr]

Wow! Thank you vba_php! Love this forum and do so thank you for your post. Works like a charm. No, I wasn't already using Folder Picker, and yes, I am using Office 365.

Private Sub Command40_Click()
On Error GoTo Err_Command40_Click
  Dim Myfile As FileDialog
  Set Myfile = Application.FileDialog(msoFileDialogFilePicker)
  Dim FileAddress As String

  With Myfile
    .Filters.Clear
    .AllowMultiSelect = False
    .Show
    FileAddress = .SelectedItems(1)
    End With

    Me.AttachmentLocation = FileAddress

If Me.Dirty Then Me.Dirty = False
Me.Refresh
Exit_Command40_Click:
    Exit Sub

Err_Command40_Click:
    MsgBox err.Description
    Resume Exit_Command40_Click   

End Sub

 

[vba_php]

Wow! Thank you vba_php! Love this forum

that's because this place is the best place on the internet, worldwide, to hang out. Just ask our leader, Jon. He knows it too. =) I'm happy for you. good luck with it!