Set export destination via windows standard 'save to' or 'export to' etc

[vicissitude]

Could anybody point me in the right direction?

I want to put code behind a command button to open up windows standard 'save to' or 'export to' destination box and then save the selected path in a table so that i can reference this when i trigger DoCmd.OutputTo function
to export reports ready for email attachment.

Any help would be greatly appreciated.

 

[vbaInet]

Have a look through the arguments that come up in the vba editor when you type:

Docmd.Runcommand "[argument]"

 

[vicissitude]

All i could find was DoCmd.RunCommand acCmdSaveAs

What i had envisaged was something similar to what you see on many applications where you are able to set the destination folder in 'settings' that you want any post processed files dumped into.

Is this a bit of a tall order? I have a feeling this is not going to be simple!

I wanted to do this because some users of said database are not very computer literate and are always emailing the wrong report dates because they regularly export reports all over their pc and then cannot find them again. I am aware that you can email one report at a time but I was hoping for a better solution. I don't really want to set a permanent export location because database may be used on more than one pc. But allowing the user to choose each time where to export to is still causing problems.

 

[vbaInet]

Like a folder picker right? The control you want is a "Microsoft Dialog Control". Have a look through the ActiveX Controls list.

What you would do is code the File Dialog to open and use the path selected in the OutputTo method. The selected path is in a method of the File Dialog called SelectedItems().

If you want to use an API, do a Google search and I think you will be able to find the API code.

 

[vicissitude]

yea like a folder picker, that's it.

Thanks, I will have a search for Microsoft Dialog Control and see what i come up with.

 

[vbaInet]

I've just had a look in the vba editor help files and there's an example there.

Type this "Application.FileDialog Property" and it will explain how and tell you what reference to add. Post back if you're having difficulty.

 

[vbaInet]

Came across this site so I thought I should share:

http://www.cpearson.com/excel/BrowseFolder.aspx

 

[vicissitude]

Thanks vbaInet! that was just what i was looking for.

I put this in a module

  Function SelectExportPath()

    Dim dlgFolderPicker As FileDialog, strSelectedPath As String
    Set dlgFolderPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
    dlgFolderPicker.Title = "Choose or create Destination Folder that you wish to save reports into, then press OK"
    
        'Initialise with previously saved export path if available
        If Not IsNull(Forms![MIP-Preview/Print/Export].[SelectedExportPath].Value) Then
            strSelectedPath = Forms![MIP-Preview/Print/Export].SelectedExportPath.Value
            dlgFolderPicker.InitialFileName = strSelectedPath & "\"
        End If

        'Open folder picker and set form text box with path value if user has picked destination
        If dlgFolderPicker.Show = -1 Then
            Forms![MIP-Preview/Print/Export].SelectedExportPath = dlgFolderPicker.SelectedItems(1)
            'Refresh to store value in bound table field
            Forms![MIP-Preview/Print/Export].Refresh
        End If
    
    Set dlgFolderPicker = Nothing
    
 End Function

 

[vicissitude]

And then this to export to saved location

Function Export_Weekly_Report_as_Snapshot()
Dim strMsg, strFileName, strExpPath, strRepDate, strRepName As String

If Not IsNull([Forms]![MIP-Preview/Print/Export].[SavedWeeks].Value) Then
        strRepDate = Format(Forms![MIP-Preview/Print/Export].SavedWeeks.Value, "Medium Date")
        strRepName = "MIP-Weekly Training Hours " & strRepDate & ".snp"
        strExpPath = Forms![MIP-Preview/Print/Export].SelectedExportPath.Value
        strFileName = strExpPath & "\" & strRepName
        
            DoCmd.OutputTo acReport, "MIP-Weekly_Training_Hours", "SnapshotFormat(*.snp)", strFileName, False, "", 0
        
        strMsg = "" & strRepName & "" & vbCrLf & "" & vbCrLf & "has been exported to the following location;" _
        & vbCrLf & "" & vbCrLf & "" & strExpPath & "      "
        MsgBox strMsg, vbInformation + vbOKOnly, "File Exported"
         
Else
   strMsg = "Please select a week from the 'Saved Weeks' drop down box     "
   MsgBox strMsg, vbOKOnly + vbInformation, "Select Saved Week"
   End If

End Function

 

[vicissitude]

And Finally this to email

Function Email_Weekly_Report()

Dim strMsg, strExpPath As String, MsgBoxRes As Integer
       
     strExpPath = Forms![MIP-Preview/Print/Export].SelectedExportPath.Value
            
     strMsg = "Please remember to attach 'Weekly Training Hours Reports' to email, " & vbCrLf & "" & vbCrLf & _
      "If they have already been exported they can be found at the following location;    " & vbCrLf & "" & vbCrLf & strExpPath
            
      MsgBoxRes = MsgBox(strMsg, vbOKCancel + vbInformation, "Email Reports")
                 
        If MsgBoxRes = vbOK Then
           strMsg = "Hi Jane," & (Chr(10)) & (Chr(10)) & "Please find training hours attached, " & (Chr(10)) & (Chr(10)) & ""
           DoCmd.SendObject , "", "", "Jane", "", "", "Weekly Training Hours", strMsg, True, """"
        End If
                
End Function

All works a treat! thanks again for the help!

 

[vbaInet]

Excellent!! Glad it worked for you.

 

[vicissitude]

O just one more thing, would you happen to know if there are any methods available for the the 'outlook session' i.e. via DoCmd.SendObject to initialise its Attach File 'File Picker' so that i can point it to the path i have saved?

i have to make the dots really close together for certain persons working this database!

 

[vbaInet]

I don't understand the question? "Initialise file picker". Explain the process.

 

[vicissitude]

The Application.FileDialog(msoFileDialogFolderPicker) that you set me up with has a method .initialFileName

eg.

strSelectedPath = Forms![MIP-Preview/Print/Export].SelectedExportPath.Value
  dlgFolderPicker.InitialFileName = strSelectedPath & "\"

so you can set where the file picker initially points to, i.e. you can ititialise it to point to a certain file path each time it is called.

Just wondering if you could do the same with the outlook session.

What i had in mind was that the user sets up an Export Destination Folder, so each time the user hits export report command button the export gets dumped into this folder and then when user wants to email these reports another button serves up the outlook email session and when the user clicks the 'attach' button on email the associated file picker is also immediately pointing to the Export Destination Folder.

I suppose you maybe just do it the same way as for access with the Application.FileDialog(msoFileDialogFolderPicker)? Will look into that.

 

[vbaInet]

That's out of the Access application, so you will need to look into OUTLOOK AUTOMATION. I believe it can be done. Might be time consuming.

 

[vicissitude]

Thanks, i will do a bit of searching, see what i can find.

 

[vbaInet]

Maybe you will find this useful:

http://www.outlookcode.com/