In Access 365, how do you export a table to a delimited text file using a file extension other than .csv?

Question

In Access 365, how do you export a table to a delimited text file using a file extension other than .csv?

Anonymous

I'm using a common dialog box (Microsoft Office 11.0 Object Library) to select and existing file or create a new one. I'm outputting a table to comma-delimited format. (I'll switch to semicolons eventually.) I do so by first placing the values of the controls into a temporary table, then calling a common dialog box using "msoFileDialogSaveAs" to set its type. I want to save the file with an extension other than .csv. When I try, I get a "Not a valid file name" error. Using .csv works fine, and opening the file in Excel or Notepad gives me exactly what I expect to see, so I know the code is proper. It appears simply to object to the file extension. Here's my code for good measure.

Set fDialog = Application.FileDialog(msoFileDialogSaveAs) 

With fDialog 

   ' Don't allow user to make multiple selections in dialog box 

   .AllowMultiSelect = False 

   ' Set the title of the dialog box. 

   .Title = "Save File" 

   txtName.SetFocus 'so dumb that I have to set focus to the text box to read its data. Causes screen flickering.

   .InitialFileName = txtName.Text & ".RGW"

   cmdSave.SetFocus  'return focus to the save button for user experience.

   ' Clear out the current filters, and add our own. 

   'Filters.Add "All Files", "\*.\*" 

End With 

If fDialog.Show = True Then 

     strPath = fDialog.SelectedItems(1) 

     DoCmd.TransferText acExportDelim, , "exptExports", strPath 

End If

Is there a common dialog from a different library that won't give me these headaches? Is there a better method than TransferText for saving to a file? This seems like it shouldn't be a tall ask of MS to allow me just to change the file extension. I could programmatically change the file extension after the file has been created, but considering I can't change the filter on a common (save) dialog, there'd be little point in doing so. I can't filter out files I don't want the user to see when running the common (open) dialog box. I haven't written that functionality yet, so I imagine the file extension will give me trouble there as well.

Side note: It's really annoying to me that I have to set focus on a data control just to read its text property. If anyone has a workaround without involved code, that would also help. I assume setting global variables every time a control changes would work, but that's a lot of variables even for as small a project as this is. I also understand that tying the controls directly to the underlying table would work, which might be the way to go here, but it's not always a good solution.

Thanks in advance,

Rob

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

5 answers

Answer 1

First, I've never tried to export to other than a csv or txt. Not sure why you would. But that may be a limit on TransferText. But I don't see an issue with rename the file afterwards. Just use the NAME command:

NAME "c:\folder\filename.csv" AS "c:\folder\filename.xxx"

Where do you have the idea that you need to set focus to a control to read its value?

variable = Me.controlname

Is all you need. Or include Me.controlname in your expression.

The .Text property reads the current status of a control. Its useful in checking each keystroke. That's why it needs to have focus. I've rarely had need to use it however. Why do you think you need it?

Answer 2

The default property in a control reference is .Value. So, it does not need to be specified. But .Text references the current state of the control whether the value has been committed or not. That's why use of .Text requires the control to have focus. If you are not dealing with a situation where the user is currently editing the control, then you shouldn't be using the .Text property.

As for using the Save dialog I'm not following why you need to do so. If you know what name you want to give the file, there should be no reason to open the dialog box. The only reason to do so is if you need the user to select the folder.

One more word on file extensions. Windows uses file extensions to determine what application to open a file with. It allows you to select the file using Windows Explorer and then open it. If you are using non standard extensions that are not associated with an application, then Windows will ask you to select the application to use.

Answer 3

Thanks for your reply.

The idea is to be able to filter files by file type. I don't want people grabbing the wrong file or trying to find it if they've saved it in a folder with other types of files. That's precisely why file filters exist. However, I understand that I can't change the file filters on a save as dialog. Why? No clue. Seems ridiculous not to support that, by MS doesn't, Still, I occasionally find myself in Windows explorer sorting by file type so I can find documents quickly. Unless the extensions are different, I wouldn't easily be able to find what I'm looking for. So, even if I can't do it from the start through Access, it's still of value. The Name command seems like a good fix to at least clean up the folder on my hard drive. I can add filters to the file picker box, so at least opening them would still allow an automatic filter of files.

Every single time I try to reference a control without first enabling it (which may require making it visible before that), I get the same error (which wouldn't exist unless it were a thing):

This has been a consistent issue in all contexts.

EDIT: And now I see the difference. I just don't have to reference the property, Text. I can just say strString = txtText. That avoids the error for a combo box and text box. I imagine it avoids the error for check boxes, etc. We shall soon see. :)

EDIT2: Using Name requires a bit of a dance, though. I first have to check to make sure the file doesn't already exist. The built in functionality of the dialog box doesn't help because it's trying to save the file as filename.csv, so it doesn't look for filename.rgw and warn you. Once I verify it's there, then I can delete the existing file with KILL, then rename my file with NAME. However, I don't know the command just for finding a file on a hard drive.

EDIT3: Aaaaannnndddd it's DIR. Easy enough, but far more involved than it should be. 🤷

Answer 4

BTW, I misread your reply. I didn't see that you had referenced the Text property. The reason I use it is out of consistency. If, for example, I needed to refer to the value of a control, I referenced the value property. Seemed logical. I took the same approach with all controls, including text boxes. Hence, my headache.

Answer 5

I want to save the file with an extension other than .csv.

You might be interested in the following function:

Function ExportToText(strQuery As String, _
strExportTo As String, _
strDelim As String, _
blnQuoteText As Boolean, _
Optional blnHasFieldNames As Boolean = True)

' Accepts:
' strQuery - Text - name of query to be exported
' strExportTo - Text- path to file to export to
' strDelim - Text - delimiter character(s) to separate fields
' blnQuoteText - Boolean - True to enclose text or memo data in quotes
' blnHasFieldNames - Boolean - (optional) - True (default) to export field names as first line

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim Fld As DAO.Field
Dim n As Integer
Dim strPrintList As String
Dim strQuote As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs(strQuery)

' evaluate query's parameters, if any
For Each prm In qdf.Parameters
prm = [removed_js]
Next prm

Set rst = qdf.OpenRecordset

If Dir(strExportTo) <> "" Then
If MsgBox("Overwrite " & strExportTo & "?", vbQuestion + vbYesNo, "Export Query") = vbYes Then
Kill strExportTo
ElseIf MsgBox("Append rows to " & strExportTo & "?", vbQuestion + vbYesNo, "Export Query") = vbNo Then
Exit Function
End If
End If

With rst
If Not (.BOF And .EOF) Then
Open strExportTo For Append As #1

       If blnHasFieldNames Then  
            ' include column headings in text file  
            For n = 0 To qdf.Fields.Count - 1  
                 strPrintList = strPrintList & strDelim & qdf.Fields(n).Name  
            Next n  
            ' remove leading delimiter  
            strPrintList = Mid$(strPrintList, Len(strDelim) + 1)  
            Print #1, strPrintList  
            strPrintList = ""  
       End If  
         
       Do While Not .EOF  
           For n = 0 To qdf.Fields.Count - 1  
               Set Fld = .Fields(n)  
               strQuote = IIf(blnQuoteText And (Fld.Type = dbText Or Fld.Type = dbMemo), """", "")  
               strPrintList = strPrintList & strDelim & strQuote & \_  
               .Fields(n) & strQuote  
           Next n  
           ' remove leading delimiter  
           strPrintList = Mid$(strPrintList, Len(strDelim) + 1)  
           Print #1, strPrintList  
           strPrintList = ""  
           .MoveNext  
       Loop  
       Close #1  
   End If

End With

Exit_Here:
rst.Close

End Function

This writes the rows from a query line by line to a file, which can be given any extension you wish.

Share via

In Access 365, how do you export a table to a delimited text file using a file extension other than .csv?

5 answers