Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

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

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 ...
Password Protect Excel Files
How to keep an Excel workbook safe by encrypting it with a password. This will make it so that a user cannot open ...
Remove Personal Information from Excel Files
How to remove all personally identifiable information from an Excel file. When a file is created in Excel, it auto ...

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.


What wrong with this code plz.
I'm trying to export a query to a csv file
I get a 3027 error - Cannot update database or object is read only
This is run from a button on a form with nothing else open


Private Sub Export_Tranship_Click()

Dim StQryName As String
Dim mypath As String
Dim myfile As String

StQryName = "ZTmp_Fdr_Tranship"
mypath = "N:\Newark\_Live_Planning\Imports\MTS\DB_Import\"

DoCmd.TransferText acExportDelim, Tranship_XDock_Export_Spec, StQryName, mypath + myfile
End Sub

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.


Have working code at:

which creates and opens a query. The original query was an append query and I need the same from this query or way to save into table "tblMODfnr".

All HOWTOs and helps I've read, do not show a way to do this and when I click "File" + "SaveAs" with the query open, table is not an option.

How do I get this done?

Do I need to change my SQL to "INSERT INTO" and then "DoCMD.RunSQL"?

Any help appreciated!



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!

Hi All

Ive made a new database that needs to have a .txt file transfered into it each day, up to now i have been doing this....but soon i will not have the time to. So i am handing over to someone else.

Unfortunatly, he does not have Microsoft Access and I cant get him it either. So, i made him a VB6 form that link into the access database, as is well except for one thing...i cant work out the coding to transfer a text file into the database. This is what i have in the normal access form

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblMain"
DoCmd.TransferText acImportDelim, "Spec1", "tblMain", DIRECT & "Outbound.txt", False
DoCmd.SetWarnings True
MsgBox "Import Complete!", vbInformation

How do I make this work in a VB6 form?

I am querying Access from Excel with VB using the below code, which works perfectly by the way. The problem is I have about 10 different queries in this DB that I need to run using the same 3 parameters for each query that prompts the user to input. So is there a way to enter the parameters once (i would like to create a form in excel) and have them pass thru to all the queries so I do not have to enter them for each query separately?

Sub MyQuery()

Set accApp = GetObject("c:\MyDatabase.accdb")
accApp.Visible = False

'this will run the query in your database
accApp.DoCmd.OpenQuery ("Query1")
accApp.DoCmd.RunCommand acCmdSelectAllRecords
accApp.DoCmd.RunCommand acCmdCopy

Range("a1").PasteSpecial (xlPasteValues)


End Sub


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