Export Table as delimited text to user defined location (1 Viewer)

[Thisismyprofile]

Hello All, I have a simple problem that should be an easy fix hopefully.

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.

Basic background:
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:

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_Main\MyFolderName_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_Main\MyFolderName\MyTextFileName.txt

Dim MSg As String
Dim SelectedDir As String
Dim SelectedDirFinal As String
Dim SelectedDirName As String
Dim StrFolder As String
Dim StrDirTemp As String
 Dim StrPName As String
 Dim StrDIAUnFormatted As String
 
 StrPName = Me.ParentName.caption
 StrDIAUnFormatted = Me.DaysCalculated.ControlSource
'MsgBox "Here Is the Dir Result:" & StrDirTemp
MSg = "Select the folder to store your file."
SelectedDir = GetFolder("D:\1_Main\", MSg)
'MsgBox "" & SelectedDir
SelectedDirName = SelectedDir & StrPName
 
DoCmd.TransferText acExportDelim, "MySpecificationName", "MyTableToExport", StrDirTemp & "input_" & StrPName & "NameCode" & StrDIAUnFormatted & "d" & ".txt", False
End Sub
 
 
 
*****BEGINNING OF FUNCTION******
 
 
Function GetFolder(strPath As String, strMsg As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = strMsg
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

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 treid wildcard methods as well:

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

But keep throwing up blanks.

Any help, guidance, or recommendations would be appreciated.

Thanks.

 

[JHB]

So in the end it will look like this:

D:\1_Main\MyFolderName_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_Main\MyFolderName\MyTextFileName.txt

Then insert a "\" where you gather the path and file name.

 

[Thisismyprofile]

Then insert a "\" where you gather the path and file name.

Thanks, yeah I realized that pretty soony after posting it. I couldn't believe how simple it was. :banghead::banghead:
I now have it tweaked to exactly the way I had envisioned it working.

Thanks for the reply and answer as well. Sometimes you just overthink thigns, when you need to take a step back and see the picture.