Access Export to Text, VBA seems to rename the . to #

[kjv1611]

In the following code, I'm wanting to export a table to text format, but in my efforts so far, I'm getting a file name to the extent of filename#txt instead of filename.txt. Any ideas as to what is causing this?

The code resides within a form, and is run off the Button Click for the named button.

Private Sub cmdExportText_Click()
    Dim vrtSelectedItem As Variant
    Dim strSQL As String
    Dim strTableName As String, strCount As String, strTables As String
    Dim strFilePath() As String, strFileName() As String
    Dim strNewFilePath As String, strNewFileName As String
    Dim x As Integer [GREEN]'For Looping through path string array[/GREEN]
    
    txtTable.SetFocus
    strTables = txtTable.Text
    strTableName = txtTable.Text & "_Random_" & Format(Date, "yyyymmdd")
    txtSampleCount.SetFocus

    strCount = txtSampleCount.Text
    cmdExportText.SetFocus
     strSQL = "SELECT TOP " & strCount & " [" & strTables & "].* " & _
                "INTO [" & strTableName & "] " & _
                "FROM [" & strTables & "] " & _
                "ORDER BY [" & strTables & "].Random;"
    Debug.Print strSQL
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    strFilePath = Split(FileName, "\") [GREEN]'Pulling FileName from the form's recordset, pointing at the tblImports table[/GREEN]
    strFileName = Split(strFilePath(UBound(strFilePath)), ".")
    
    For x = 0 To UBound(strFilePath) - 1
        If strNewFilePath = vbNullString Then
            strNewFilePath = strFilePath(x) & "\"
        Else
            strNewFilePath = strNewFilePath & strFilePath(x) & "\"
        End If
    Next x

    Select Case strFileName(UBound(strFileName))
        Case "txt", "csv"
            [GREEN]'Don't need to add any worksheet/table name, since a text file IS the table/sheet/data set.[/GREEN]
        Case "xls", "xlsx", "xlt"
            strNewFileName = strFileName(0) & ObjectName 'Add worksheet name if applicable
        Case "mdb"
            strNewFileName = strFileName(0) & ObjectName 'Add table name if applicable
        Case Other
            [GREEN]'For now, do nothing, but going forward, perhaps can create a log file of all imported file types in case
            'Anything new pops up - 20100127[/GREEN]
    End Select
    
    strNewFileName = strFileName(0) & Chr(46) & "txt" 'adds .txt extension to file name, regardless of what original was.
    [GREEN]'Chr(46) is the ACII Decimal code for a period.
	'This use is to overcome period being changed to # - 20100518 - did not make any difference[/GREEN]
    
    strNewFilePath = strNewFilePath & strNewFileName
    Debug.Print strNewFilePath
    DoCmd.TransferText acExportDelim, "ExportTextSpec", strTableName, strNewFilePath

[GREEN]    'Might be good to open an Explorer Window showing the new file after it's been exported...
    'and maybe check to be sure the folder path isn't already open first... [/GREEN]
    
    vrtSelectedItem = Empty
    strSQL = vbNullString
    Erase strFilePath
    Erase strFileName
    
End Sub

Thanks in advance for any thoughts or suggestions.

 

[randy700]

Why not simply use this?

strNewFileName = strFileName(0) & ".txt"

Randy

 

[kjv1611]

You know, that does make sense. I mean, if it's always going to go to .txt file extention, then in context, the orig file extension doesn't matter. At least that'll weed out one place to look. However, I doubt it's causing the issue, b/c even just before I run the export command, the string value is still correct.

 

[kjv1611]

You know, I think I put that in originally to take care of file names that might have a period in the name, before the extention period. However, that may already be taken care of in earlier code. Will have to look, and be sure, b/c I have seen workbooks with periods in the file name. Of course, best I can tell at this moment, it looks like it wouldn't have done anything with such extra pieces anyway - like I started to add that, but never really finished...

 

[kjv1611]

Any thoughts on why the period is being changed to a pound sign? It doesn't happen until I run the DoCmd.TransferText command.

 

[kjv1611]

As one reference, if I can get the site to pull up while I'm at work, may be this forum mention:

http://bytes.com/topic/access/answers/207289-file-name-text-export

That's one forum that's been apparently blocked or something through the company firewall, though I can pull cached versions easily..

Anyway, I'm hoping to look there once the cached version finishes loading.

 

[kjv1611]

Okay, that site loaded... sounds familiar, b/c I dealt at least once with this in the past, but couldn't remember what solution was.... one responder said..

I doubt a Microsoft OS is going to like that kind of a name, since
periods are generally the delimiter between file name and file type...
You *might* try outputting it to a file with a "fake" name and then
using Name to rename it. At least then you can figure out what's
causing the error.

So, I'll take a look at doing that probably tomorrow..

 

[kjv1611]

Well, that didn't work either.

I found the answer, and this may be an Access 2007 vs prior versions thing... or it may just be I never created an Export Spec (which I can't seem to find a way to do so anyway)....

I simply removed the Export Spec portion from the statement, and it now works!

So, now I've got to figure out (probably just forgot) how I can create an export spec to use instead of just the defaults, though I guess for my purposes, it isn't really all that important.

 

[MasterRacker]

I could be completely wrong here but I seem to recall that in 07, unlike previous versions, you can only create the export manually. You can then call the saved export through code. I also seem to recall that there is a limitation that you can't dynamically change the output path/name.

I remember screwing around with this, but I can't find a copy of the DB I was doing it with. :-(

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[kjv1611]

Well, I can export via VBA just fine, it's just the Export Specification I couldn't find anything about yet. I did notice that when I manually export a table to text, no export spec/options given, just asks where you save it, and what to save as (name), so it may just not be an option.

And the whole thing of saving the import or export steps, to me, seems useless in most scnerios, b/c I don't find many times that I'm importing or exporting the exact same file in the exact same circumstances. Usually, there's sometime different - a different folder path and/or file name at the least.. even if it's just different b/c of adding a date code to the file.

 

[felix169]

I ran into the same issue and it did have to do with my export spec. I had redesigned the table after making the spec, but instead of Access throwing an error that made sense, it just started putting pounds signs in my filenames and then crashed.

The solution was to just recreate the spec with the new table design. (manually export table and click "Advanced" just before finishing the process to save the spec - making sure the spec name matches the name used in the code or vice versa)