Save with Pop-up window when exporting a XML file with VBA

[Tea]

Hello, I would like to export a XML file, on click event via button. When it exports I would like to show a pop up window, where to save the file to. Same when you want to save a file a picture online, it's shows you the file explorer. Is it possible with the ExportXML method?

Here is how my code looks like

Dim contacts As AdditionalData
Dim filter As String

Set contacts = Application.CreateAdditionalData

contacts .Add "tbl_xm_ct"

filter = Me.ID

Application.ExportXML ObjectType:=acExportTable, DataSource:="tbl_xm", _
DataTarget:="C:\Users\...\test.xml", _
WhereCondition:="ID=" & filter, _
AdditionalData:=contacts

It just saves to DataTarget when it's defined, but I don't want to set where it should save to, but so can the user choose himself where to save it.

Thank you!!

 

[bastanu]

Use the built in FileDialog to do that:

VBA Save File Dialog, FileDialog(msoFileDialogSaveAs) - VBA and VB.Net Tutorials, Education and Programming Services

There may be times when you need to ask the user to select a location to save a file. This can be done using the save file dialog. Keep in mind that the save file dialogs doesn’t actually save anything. It only returns the full

software-solutions-online.com

Cheers,

 

[Pat Hartman]

Don't use a popup dialog to prompt for a target location. Add a control to the form so you can do what @bastanu recommended and use the Windows dialog. If you insist on a popup, make your own form so you can use the Windows dialog.

 

[Tea]

Use the built in FileDialog to do that:

VBA Save File Dialog, FileDialog(msoFileDialogSaveAs) - VBA and VB.Net Tutorials, Education and Programming Services

There may be times when you need to ask the user to select a location to save a file. This can be done using the save file dialog. Keep in mind that the save file dialogs doesn’t actually save anything. It only returns the full

software-solutions-online.com

Cheers,

The msoFileDialogOpen and msoFileDialogSaveAs constants are not supported in Microsoft Access.

Application.FileDialog property (Access)

Office VBA reference topic

docs.microsoft.com

Is there some built in like this for access ? Thank you!

 

[Gasman]

Here, I have Google on my PC

MsoFileDialogType enumeration (Office)

Office VBA reference topic

docs.microsoft.com

 

[bastanu]

If you look in the same thread where you found that (https://www.access-programmers.co.uk/forums/threads/save-as-dialog-box.270344/) and check post #3 you will find the solution:

Sub TestFileDialog()
Dim strFilename As String
 With Application.FileDialog(msoFileDialogSaveAs)
   If .Show Then
   strFilename = .SelectedItems(1)
  Else
    MsgBox "No filename specified!", vbExclamation
Exit Sub
End If
End With
' Do something dreadful with strFilename
...
End Sub

 

[Sharkbyte]

Sub TestFileDialog()
Dim strFilename As String
With Application.FileDialog(msoFileDialogSaveAs)
   If .Show Then
   strFilename = .SelectedItems(1)
  Else
    MsgBox "No filename specified!", vbExclamation
Exit Sub
End If
End With
' Do something dreadful with strFilename
...
End Sub

I get a Method 'FileDialog' of object '_Application' failed message. Any ideas?

Thanks

 

[ebs17]

You need a reference to Microsoft Office XX.0 Object Library.
msoFileDialogSaveAs is a constant from this library.

Alternatively, you can use late binding: Late Binding the FileDialog

 

[Sharkbyte]

You need a reference to Microsoft Office XX.0 Object Library.
msoFileDialogSaveAs is a constant from this library.

Thanks. The reference appeared to be in place, but it still showed as not included... Added, and we're up-and-running. Again, appreciate the assist.