TransferText to export 3 queries to csv

[kwooten]

I am using Access 2016 and am having a hard time making a csv export specName (manually export the query, click ADVANCED in bottom left corner, setup your spec, save), I do not see this advanced option. I have 3 files that are similar (ResponsibleManager_1, ResponsibleManager_2 and ResponsibleManager_3) and need some code to run all 3 to CSV so I can stop exporting each individually and manually adjusting.



Can someone please help me with this DoCmd.TransferText?

[aytee111]

Right-click on your query, select Export to Text. That should give the Advanced button.

[kwooten]

Right-click on your query, select Export to Text. That should give the Advanced button.

It doesn't.. this is the screen I get when I do that

[aytee111]

Go one step further - click on OK.

[kwooten]

still nothing

[aytee111]

Never seen that before, maybe you have encoding set to yes. What happens when you click OK on that window?

[kwooten]

Access exports the file..

where would I change this setting?

[aytee111]

I get the export wizard when I click on the ok from post #3. I wonder if you don't have the wizards installed, or if this is a 2016 thing. You can also create specifications when you import a file. Try that and see if the wizard comes up.

[aytee111]

Here are some people discussing the problem. It may require an Office repair or an uninstall of a Windows update.
https://answers.microsoft.com/en-us/...7-78f5e9cfde6b

[kwooten]

not sure what happened but it is working now

[aytee111]

Great! Saved you lots of trouble.

[kwooten]

yes.. now trying to get my code to work.. "Cannot update. Database or object is read-only"

Option Compare Database

Private Sub ExportResponsibleManager()
 
    Dim i As Integer
    Dim myQueryName As String
    Dim myExportFileName As String
 
    For i = 1 To 3
        myQueryName = "ResponsibleManager_" & Format(i, "1")
        myExportFileName = "H:\Ariba\Ad_Hoc\ResponsibleManager_" & Format(i, "1") & ".csv"
        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="ExportCSV_RM" & Format(i, "1"), TableName:="ResponsibleManager_" & Format(i, "1"), FileName:="H:\Ariba\Ad_Hoc", hasfieldnames:=True
    Next i
 
End Sub

[aytee111]

More information needed. Which line is it occurring on? Is it on the first file? Did you complete post #10 and get the file written to that file in that folder?

[kwooten]

duplicate post

[aytee111]

Maybe because there is no file name in the command, only a path.