Modules & VBA :: Export Table As Delimited Text To User Defined Location

I would like to export a table as a text file to a user defined location.

I have it mostly working, but not exactly as I would like. I'm stuck on the user defined location.

I have a Form that contains a subform and two command buttons.

The subform contains the table I want to export as a text file.

The text file has to be comma delimited, no qualifiers.

I have the transfertext command in VBA that works perfectly:

Code:
DoCmd.TransferText acExportDelim, "My Specification Name", "MyTableToExport", StrDirTemp & "input_" & StrPName & "NameCode" & StrDIAUnFormatted & "d" & ".txt", False

What I'm stuck on is the filepath. The file path changes everytime. So I would like to have either the open dialog box (I've tried many different versions that I found on the web.) or to search by the account name for the folder and place the text file in there.

Here is one that is closely working how I want it to:

This is a function that I found, that opens a dialog box for the user to select the folder location. It works, but I can't seem to get it to work properly.

It prompts, the location, then once you select it and press ok. It will add the folder name to the full file name, and place the file in the default root path. Not the selected folder path.

So in the end it will look like this:

D:1_MainMyFolderName_MyTextFileName.txt

I'm somehow stuck on getting to seperate the file path from the file name, so you it look like this:

D:1_MainMyFolderNameMyTextFileName.txt

Code:

Dim MSg As String
Dim SelectedDir As String
Dim SelectedDirFinal As String
Dim SelectedDirName As String
Dim StrFolder As String

[Code] ....

I think it should be something very easy, that I just need a pair of fresh eyes to look.

I've tried the Fileobject, FileFolder method, but can't get the quite work properly.

I've also tried wildcard methods as well:

StrDirTemp = Dir(StrFolder & StrPName & "*", , vbNormal)

But keep throwing up blanks.