Accessing the stored valyue of FileDialog

[LB79]

Hello,

I'm using FileDialog to save an Excel file from Access query and I want to refer to the file location/name that the user selects – I wondered if anyone could tell me how to do this as I cant seem to see where the final path/file name values are stored.
My procedure works by making a copy of an Excel template then writing data to it from queries. Thing is, once the user has selected their own file name, the VBA cant continue writing the data as it can no longer find the file (once the template has been copied, the reference in VBA needs to be dynamic).
Below is a snipet of the code im using… Can anyone help me with retrieving the stored path/filename?

Thanks

[SIZE=3][COLOR=#000080][FONT=Arial]Dim fDialog As Office.FileDialog[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Dim varFile As Variant[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Set fDialog = Application.FileDialog(msoFileDialogSaveAs)[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]With fDialog[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial].InitialFileName = FileName[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial].Title = "Please select a save location"[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]If .Show = True Then[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]For Each varFile In .SelectedItems[/FONT][/COLOR][/SIZE]
[SIZE=3][COLOR=#000080][FONT=Arial]Next[/FONT][/COLOR][/SIZE]

 

[vbaInet]

Hello LB79. It's like an array collection but the index starts from 1. So the first selected file is .SelectedItems(1)

Hope that helps.

 

[LB79]

Hi vbaInet and thanks for that... im not really sure how to use that within my procedure though. How do I refer to the .SelectedItems(1)? For example, if I wanted a MsgBox to pop the value of .SelectedItems(1).

thanks again

 

[vbaInet]

Did you write that code yourself by the way?