Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Vba Appending Exported Query Files Via Docmd.transfertext

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have the following command in VBA to export a query to a csv file

DoCmd.TransferText acExportDelim, "SimNetRegistrationRosterExportSpecification", "z_SimNetRegistrationRoster", _
RegToolPath & RegToolPrefix & Format(Now(), "YYYY-MM-DD_HHnn") & ".csv", True

I need to append to the file that was created the in that last command the data created by the following z_SimNetOSSDRegistrationRoster query. How can I do this?


View Answers     

Similar Excel Tutorials

Basic Web Query in Excel - Import Data from the Web into Excel
Import data from the web into Excel with this macro. This is a basic web query macro that will import data from an ...
Make Your Macros Available in All Workbooks in Excel
In Excel, you can make it so all of your macros can be easily accessed by any Excel workbook. To do this we need t ...
How to import Text Files (CSV) into Excel
Text files in CSV format are one of the easiest ways to store and transfer data as it is one of the most compatible ...
Export an Excel File to a CSV File
How to export an Excel file to a CSV file (comma separated values file).  This allows you to turn any Excel spread ...

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from

Similar Topics

I use code below to export some data to the users desktop

DoCmd.TransferText acExportDelim, , "qry Export", Environ("USERPROFILE") & "\Desktop\Exportdata_" & Format(Date, "dd-mm-yyyy") & ".csv", True

Can I change this code so that after running this it will create a folder with the date as name and the reports should get the time in the filename.


I am running a module to export data

DoCmd.TransferText acExportDelim, "current Portfolio Export Specification", "Symbol List Malcolm", "Z:\My Virtual Machines data\portfolio.csv", True

The data is created from a query. I would like to exclude the filed name from the export. Is it possible to exclude the field name at the query stage, or is there a better way??

As always, thanks for any advice.


I have a line of code that takes my query and exports it into a text file. I need to have the double quotes around my data removed in the text file. What code do I need to add to accomplish this?

DoCmd.TransferText acExportDelim, , "qry_MRN_12", "G:\WLM\mrn_test.txt", False

The reason why I have to remove the double quotes is so that the data can be imported into another (unsophisticated) program that my company uses that does not allow you to remove text qualifiers.

I also don't want to export the text file manually (i.e., File, Export, Save as Text File, Text Qualifier = None, etc...) because I don't trust my endusers to remember to do this step.

Thank you for your assistance!

What would bewrong with the following line if I simply wantedto export a table to a file?

DoCmd.TransferText acExportDelim, , Mat_to_IP_tbl, "\\Usfps03\ABC Project\comm\Sil.txt"

I am trying to export a data file with the extension, i.e., "TLS". Unfortunately it appears there is a limited menu of acceptable extensions (csv, txt, etc.). Is there a way to train Access to allow this non-standard extension?

[DoCmd.TransferText acExportDelim, "current Portfolio Export Specification", "Watchlist Malcolm", "c:\current list malcolm.TLS", False

Thanks in advance.


Can you export an access query to a certain tab in an excel work book?
At present I use this code to export to a work book-

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "Query", "\\dtc06\excel work book", True
DoCmd.SetWarnings True
End Sub

Can I export to a certain tab . To overwrite the data but not the work book.
This would allow me to export several querys to the same work book.

I'm finally getting around to trying to automate some of my weekly Access work as the scope of projects have gotten pretty big recently.

I tried setting up the following TransferText routine to pull append data to a table from a .csv file and use pre-set import specifications, but I get an Error 3027 - Database or Object is Read-Only.

The text file isn't RO and it imports just fine if I do it manually (which is what I'm trying to get away from).

Any ideas where I'm going wrong (unlike Excel I do very little with Access' back-end):

Sub WeeklyImport()
    Dim ImportTablePath As String
    Dim ImportTableName As String
        '   Set Import Table Path
        ImportTablePath = "C:\Documents and Settings\csmith\My Documents\PennySaver\Access\Downloads\FLA\2007\"
        '   Set Import Table Name
        ImportTableName = InputBox("Enter Text File Name", "Import Data Warehouse Data")
        '   Import Data Warehouse Data
        DoCmd.TransferText acExportDelim, "Weekly Import Specification", "1", ImportTablePath & ImportTableName
End Sub


I'm on Access 2002 and I have referenced the Microsoft Office 10 Object Library.

I have created a user form that allows either a report to be run in access or the report query data to be exported to excel.


If frmExport = 1 Then
    DoCmd.OpenReport "rpt_HCReport3_InvSales_LP_Actual", acViewPreview

        DoCmd.TransferSpreadsheet acExport, 8, "qry_HCReport3_InvSales_LP_Actual", fncFileSelected, True, ""
        End If

I picked up the function from here (sorry a can't remember which thread...I've been in quite a few)


Function fncFileSelected()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
  fd.AllowMultiSelect = False
  fd.ButtonName = "Save"
  fd.Filters.Add "Excel Files", "*.xls"
  fd.Filters.Add "All Files", "*.*"
  fd.Title = "File Save As"
  If fd.Show = True Then
    fncFileSelected = fd.SelectedItems(1)
  End If
End Function

That's perfect if the file already exists.

I would like the user to be able to enter a file name if the file does not exist or save the query name & .xls to the user selected folder. I've tried msoFileDialogSaveAs but this does not work in Access 2002.

I am trying to import a text file into a table. Thie code below works in Access VBA but I want to run it from Excel and I am getting the error "Run-time error '424': Object required" at the DoCmd statement (the DELETE works OK). I must be missing something because debugging DoCmd shows it as Empty. Clearly I am not definining something. Can I run TransferText from cn?

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.Path & "\Student Refund Notification.mdb"
End With

cn.Execute "DELETE * FROM [Refund List for ACH]"

DoCmd.TransferText acImportDelim, "Refund List for ACH Import Specification", "Refund List for ACH", "R:\Campus Operations-101200\Test\Student Refund Notification\Refund List for ACH.txt", True

every month i import a large amt of data...using Docmd.TransferText the field header get imported as "F1", 'F2", "F3",....etc. the table name is "tbl_Top500_Imported"

I've have already build many quieries that rely on a different table "tbl_Top500_IMPORT". The field headers are "Field1", "Field2", "Field3",...etc. So what I've created is an append qry that takes the imported data from
tbl_Top500_Imported" and appends it to "tbl_Top500_IMPORT".

My issues is that the import is so large that when i add it to another table the database is over 2g. Not to mention there is no need to have the same data in there twice.....twas a quick fix that I am now attempting to clean up.

Here are my thought on how to solve:


'import the file less the IMPORT_SPEC
    DoCmd.TransferText acImportDelim, , "Top500Imported", IMPORT_FILE, False
    'delete the old table, ie other queries rely on this table
    DoCmd.DeleteObject acTable, "Top500IMPORT"
    'change the newly imported field names
    Call RenameFields
    'rename the newly imported table
    DoCmd.Rename "Top500IMPORT", acTable, "Top500Imported"



Public Function RenameFields()

Dim db As Database, tbl As TableDef, fld As Field
Dim x As Integer, y As Integer

Set db = CurrentDb

Set tbl = db.TableDefs("Top500Imported")
For Each fld In tbl.Fields

For x = 1 To 9
    fld.Name = fld.Name("Field" & x, dbText)

Next x

For y = 10 To 34
Set fld.Name = fld.Name("Field" & y, dbDouble)

Next y

Set tbl = Nothing
Set db = Nothing

End Function

I am not havign any luch on the second Sub. Is there a better way to accomplish this?


It seems like one of the most basic codes involving Excel and Access, but I've been struggling to determine how to copy access query results into excel using VBA.

I would like to write an excel macro that
1) opens an access dba file
2) runs a query
3) either copy or exports the query results
4) closes the dba

#3 is elluding me. I have the following code to open a dba called "Production" which includes a existing query called "Query" and either export or copy the results to an xls called "QueryResults." It hangs on the export line.


Sub GetData()
  Set appAccess = CreateObject("Access.Application")
  appAccess.OpenCurrentDatabase "F:\Production.mdb"
  appAccess.Visible = True
  appAccess.docmd.SetWarnings True
  docmd.transferSpreadsheet acexport, acSpreadsheetTypeExcel9, _
        "Query", "C:\QueryResults.xls", True, "Sheet1"  :confused:
  Set appAccess = Nothing
End Sub

Thank you in advance for your help and understanding!

What I would like to do is to import multiple CSV files into a table to use to create reports from selected result CSV files.

I have created the list box looking at the location and listing all files there and can get it to clear the table and import one CSV.

Just need some guidance on the best way to import more than one CSV into the table?

My code for importing one file is:

Private Sub bttnCSV_Click()
Dim mySQL
mySQL = "DELETE * FROM ArgonMonitor"
DoCmd.RunSQL mySQL
DoCmd.TransferText acImportDelim, , "ArgonMonitor", aFileList, True
End Sub
Private Sub Form_Load()
Call ListFiles("C:\Temp\ftpargon", , , Me.aFileList)
End Sub



I was wondering if you could help me. I am currently working on a process of uploading files in a certain format. The process currently entails 3 steps, please see:

-- Clicking on a .bat file Rename current files (records2.xls to records2.bak, records3.xls to records3.bak......)
-- MS Access is then launched
-- Macro is run doing the following:
-- deleting records table
-- importing records from Details.xls
-- Query records.. should now be repopulated based on the query information of each records.. using static data from tables
-- Save records2 table/query to C:\Data\records2.xls
-- Save records3 table/query to C:\Data\records3.xls
-- Save records4 table/query to C:\Data\records4.xls
-- Launch Excel - Run Macro to convert each individual file in records2.prn, records3.prn etc with a FileFormat:= _

As the files cannot be directly saved as .prn from Access the requirement is to then use another macro in Excel.

I am trying to look at a way of incorporating the 3 steps into Excel.

Please see the VBA code from access:

Function Create_records()
On Error GoTo Create_records_Err
    DoCmd.DeleteObject acTable, "records"
    ' Import Details.xls
    DoCmd.TransferSpreadsheet acImport, 8, "records", "C:\Data\Details.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "records2", "C:\Data\records2.xls", False, ""
    DoCmd.TransferSpreadsheet acExport, 8, "records3", "C:\Data\records3.xls", True, ""
    DoCmd.TransferSpreadsheet acExport, 8, "records4", "C:\Data\records4.xls", False, ""
End Function

Is there anyway of doing this?

Thanking you!

I have created a macro to export the contents of a query as a csv file, but when I open the csv file, the date is showing the date and time. I need it to only show the date as this csv file is uploaded to a parcel carrier's system that will only accept this field as dd/mm/yyyy.

The date is created in an access table as a default value in the cell. The format is set to "Short date", the input mask to "00/00/000" and the default value to "Date()".
The query field is also set the same. The date format appears correctly in both the table and the query, but when it is exported it somehow changes to date / time format.

When I checked the cell format in the csv file it shows as a custom format "dd/mm/yyyy hh:mm"
This file is overwritten every time I run the macro and in fact when I ran it for the first time, it created the csv file so it can't be due to a preformatted file problem.

Would greatly appreciate any help on this - it's prevent us from automating our despatch processes.

I am trying to use VBA Code to transfer a file into Access and I am getting an error message.

My Current Code:

Sub AddWorksplitUserTablesByDivision()

DoCmd.SetWarnings False

Dim strSrcPath As String, strSrcFile As String

Const conDefFldr As String = "C:\My Data"

'Separates the file name and the file path
strSrcPath = conDefFldr & "\"
strSrcFile = "Worksplit User Profile 1.txt"

DoCmd.OpenQuery "qdelBARTWorksplitAllUsers", acViewNormal
DoCmd.TransferText acImportDelim, "Worksplit User Profile 1", "tblWorksplitUserProfileDiv1", strSrcPath & strSrcFile & ".txt"

DoCmd.SetWarnings True

End Sub

Error message:

Run-time error '3625':

The text file specification 'Worksplit User Profile 1' does not exist. You cannot import, export, or link using the specification.

The file is in My Data named "Worksplit User Profile 1.txt". I am not sure what I am missing!

I appreciate your help


I have an append query. I want to call this append query using VBA.

I used the following syntax. But it is not working.

DoCmd.RunSQL "Qry_Append_Salary_Adjustments"

As the SQL query is very long, I can not copy the sql here in VBA.

But it is not working. Is there any other way i can call this in vba?

Hi all

I've got a piece of code I inherited that deletes some tables from a database before importing updated versions (I'm trying to get an append query to work but I'm struggling with the downloads).

At the moment it uses an on error event to just continue regardless but I'd like to make it a bit tidier.

This is the code I have:-

    On Error Resume Next
    DoCmd.DeleteObject acTable, "ext_Activities"
    DoCmd.DeleteObject acTable, "ext_HTs"
    DoCmd.DeleteObject acTable, "ext_Clients"
    DoCmd.DeleteObject acTable, "ext_Assessments"
    DoCmd.DeleteObject acTable, "ext_Contacts"
    DoCmd.DeleteObject acTable, "ext_Wellbeing"
    DoCmd.DeleteObject acTable, "ext_PHPGoals"
    DoCmd.DeleteObject acTable, "ext_PostAssessments"
    DoCmd.DeleteObject acTable, "ext_Reviews"
    DoCmd.DeleteObject acTable, "ext_Maintenance"
    DoCmd.DeleteObject acTable, "ext_Deprived"

What I would like to do is test for the existense of each table in turn before doing the delete and remove the on error event completely.

I'll admit to the fact that my Access VBA knowledge is next to nil so any help would be greatly appreciated.

I recently created a new Access database and am setting up several Excel
spreadsheets to display query results.

I created Query A, then opened a new Excel spreadsheet, then imported the
data from Query A. I adjusted the formatting to my liking, and now I just
hit "Refresh Data" when I open it and everything's fine. A similar
experience with Query B.

However, today, I created Query C, and the "Select Table" dialog (from
Import External Data) doesn't show it. I thought at first Excel might be
looking at an old version of the database or something, so I created Query D,
a simple select query that displayed the ID# of each record, and Query E, an
exact copy of Query C.

Now, I can see Queries A, B, and D, but not C or E. So, I assume there's
some property of Query C that's preventing Excel from recognizing it. What
would prevent Excel from being able to see/import an Access query?

By the way, I can export Query C into an Excel file from Access, but I'd
like to be able to update from Excel rather than having to re-export and fix
the formatting every time.

I want to export a table to a csv file using VBA. I've seen a lot of methods while searching around but some seem very complex and some look pretty simple. I have a working example that is already in use for the database I'm using that uses DoCmd.TransferText, but that seems to be just one of many options. Does anybody have an idea of the simplest way to do this in ADO vs the equivalent in DAO?

I have a VBA script:

Private Function ImportRemedyReport()

' Import the .csv file to the temp table
DoCmd.TransferText acImportDelim, , "Reportimport", "[My file location] ", True

' Copy all new records from the Remedy import to the Master table
DoCmd.OpenQuery "Qry_Import"

' Clear records in the temp table
DoCmd.OpenQuery "Qry_DeleteTempRecords"

End Function


It works fine when I run it from the VBA window (F5 or F8).

Question: How the heck to I run it from Access? A switchboard entry is ok. A button in a form is ok. What do I do now?

Help please!


Trying to export a Access query to an Excel file like I've done hundreds of times with previous versions.

Simply put, I want to export a query from Access 2007 to Excel 2007 to a .xlsm file.

When I have a query open and click [External Data]>[export to Excel] the xlsm file format is not even in the list.

PLease help

I have this code in a module in a Microsoft Access 97 database, If I run it, it exports the table "Measurements" to a text file

Option Compare Database
Option Explicit

Sub export_to_CSV()
DoCmd.TransferText acExportDelim, , "Measurements", "C:\Measurements.txt"
End Sub

I need to be able to run the code from Excel, but am not sure of the method. I have fiddled with various ways but to no avail. Can anyone assist, please?


I have a query that selects data for multiple people, let's say Alice, Bob, and Chris. I'm looking for VBA code to run the query with the name field criteria set to Alice, then export the results to an Excel file. Then run the query, but change the criteria to Bob, and export. Then Chris, etc etc.

I have code to run the query and export to a file:


Option Compare Database

Public Sub Automation()

    Dim strQryName As String, strXLFile As String, _
    oXL As Object, oExcel As Object, sFullPath As String, sPath As String
    strQryName = "qry_Automation"
    strXLFile = "File_Path\File_Name.xls"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQryName, strXLFile
        'FollowHyperlink strXLFile
    Set oXL = CreateObject("Excel.Application")
    On Error Resume Next
    oXL.UserControl = True
    On Error GoTo 0
    On Error GoTo ErrHandle
    sFullPath = "Another_file.xls"
    With oXL
        .Visible = True
        .Workbooks.Open (sFullPath)
    End With
    Set oXL = Nothing
    Exit Sub
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit

End Sub

But this will give me all results in a single file. How can iteratively filter, run, and export this query?


I am trying to execute an append query after I have created a temporary text file containing 800,000 records. The append query sums the Amount field and groups on a subset of the other fields. I want to execute the append query but my code fails:

Dim accApp As Object
Set accApp = CreateObject("Access.Application")
accApp.OpenCurrentDatabase (ThisWorkbook.Path & "\" & "Cash Flow Report.accdb")
With accApp
.Execute "qry Add to GLAuditTrail"
End With
Set accApp = Nothing

I have a query in Access that needs to be exported as a tab delimited txt file. I would like code to do this so that it will be placed on my web server at 1:00am every morning. Here is what I do manually to save the query and then I import to my server

From the main query menu, right-click on the query to be exported Export…

Select the folder to save the text file in and name the file PCSC_Employee_Query2.txt
Set the “Save as type” to Text Files and save
Choose Delimited as export type
Select Tab as the delimiter that separates the fields.
Check the box next to “Include Field Names on First Row”
Set the Text Qualifier to {none}