TransferText to export 3 queries to csv

[kwooten]

I wonder if instead of doing a loop I should just write 3 distinct DoCmd..

[kwooten]

I changed the code to below and it works fine now

Option Compare Database

Private Sub ExportResponsibleManager()
 
    Dim myQueryName As String
    Dim myExportFileName As String
 
        myQueryName = "ResponsibleManager_1"
        myExportFileName = "H:\Ariba\Ad_Hoc\ResponsibleManager_1.csv"
        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="ExportCSV_RM1", TableName:="ResponsibleManager_1", FileName:="H:\Ariba\Ad_Hoc\RM1_Upload.csv", HasFieldNames:=True
        
        myQueryName = "ResponsibleManager_2"
        myExportFileName = "H:\Ariba\Ad_Hoc\ResponsibleManager_2.csv"
        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="ExportCSV_RM2", TableName:="ResponsibleManager_2", FileName:="H:\Ariba\Ad_Hoc\RM2_Upload.csv", HasFieldNames:=True
        
        myQueryName = "ResponsibleManager_3"
        myExportFileName = "H:\Ariba\Ad_Hoc\ResponsibleManager_3.csv"
        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="ExportCSV_RM3", TableName:="ResponsibleManager_3", FileName:="H:\Ariba\Ad_Hoc\RM3_Upload.csv", HasFieldNames:=True
        
End Sub

[ssanfu]

Just a few comments......

The code in Post #12 failed because the file name was not specified..... The path is there, but not the file name:

        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="ExportCSV_RM" & Format(i, "1"),
 TableName:="ResponsibleManager_" & Format(i, "1"), FileName:="H:\Ariba\Ad_Hoc", hasfieldnames:=True

--------------------------------------------------------------
The code in Post #17, there could shortened to 3 lines

Option Compare Database
Option Explicit   '  << should ALWAYS have this line at the top of EVERY Module

Private Sub ExportResponsibleManager()
        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="ExportCSV_RM1", TableName:="ResponsibleManager_1", FileName:="H:\Ariba\Ad_Hoc\RM1_Upload.csv", HasFieldNames:=True
        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="ExportCSV_RM2", TableName:="ResponsibleManager_2", FileName:="H:\Ariba\Ad_Hoc\RM2_Upload.csv", HasFieldNames:=True
        DoCmd.TransferText transferType:=acExportDelim, SpecificationName:="ExportCSV_RM3", TableName:="ResponsibleManager_3", FileName:="H:\Ariba\Ad_Hoc\RM3_Upload.csv", HasFieldNames:=True
End Sub

because you don't use the variables.



--------------------------------------------------------------
If you wanted to use the looping method, the code would be:

Private Sub ExportResponsibleManager()

    Dim i As Integer
    Dim myQueryName As String
    Dim myExportFileName As String

    For i = 1 To 3
        myQueryName = "ResponsibleManager_" & i
        myExportFileName = "H:\Ariba\Ad_Hoc\RM" & i & "_Upload.csv"

        DoCmd.TransferText transferType:=acExportDelim, _
                           SpecificationName:="ExportCSV_RM" & i, _
                           TableName:=myQueryName, _
                           FileName:=myExportFileName, _
                           HasFieldNames:=True
    Next i
    MsgBox "Done"
End Sub

Optional arguments:
For the export file name:

        myExportFileName = "H:\Ariba\Ad_Hoc\ResponsibleManager_" & i & ".csv"

For the specification name, if all 3 specifications are the same, you could use 1 spec name:

 SpecificationName:="ExportCSV_RM"

[kwooten]

thanks for the help.. is there a way to have this run when I click the module name from Access instead of it opening in design view and then hitting the run button?

[kwooten]

just to piggyback off my post.. Why would this code work

Option Compare DatabaseOption Explicit


Private Sub Export_PR_Report()
 
DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="PR_Approval_Flow", FileName:="H:\Ariba\Ad_Hoc\SourcingManager_PR_Report.xls", HasFieldNames:=True
 
End Sub

but this code not work? I am running Office 2016 and Windows 10

Option Compare DatabaseOption Explicit


Private Sub Export_PR_Report()
 
DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="PR_Approval_Flow", FileName:="H:\Ariba\Ad_Hoc\SourcingManager_PR_Report.xlsx", HasFieldNames:=True
 
End Sub

[ssanfu]

By asking a different question in an existing thread, ie "piggybacking", you will get fewer chances of getting a solution.


Do you get any error messages?

I ran the procedure, making the necessary modifications for path and table name, and it executed fine for me.

Private Sub Export_PR_Report()

    DoCmd.TransferSpreadsheet TransferType:=acExport, _
                              SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
                              TableName:="PayApps", _
                              FileName:="C:\Forum\SourcingManager_PR_Report.xlsx", _
                              HasFieldNames:=True

End Sub

[kwooten]

By asking a different question in an existing thread, ie "piggybacking", you will get fewer chances of getting a solution.


Do you get any error messages?

I ran the procedure, making the necessary modifications for path and table name, and it executed fine for me.

Private Sub Export_PR_Report()

    DoCmd.TransferSpreadsheet TransferType:=acExport, _
                              SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
                              TableName:="PayApps", _
                              FileName:="C:\Forum\SourcingManager_PR_Report.xlsx", _
                              HasFieldNames:=True

End Sub

No error.. it acts like it runs then when I go to the file location the file is not there.

[ssanfu]

I am stumped! For me, The 2nd command (with acSpreadsheetTypeExcel12Xml) executes every time with no errors.

Since I cannot replicate your problem, I would suggest closing this thread and start a new one........