Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


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

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

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?

Thanks

View Answers     

Similar Excel Video Tutorials

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.

G


I am running a module to export data
Code:

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.

a


I have an append and delete query that I want to run off the ******* of one command button. Currently it is only appending data and not deleting.

I have this in the criteria for the append and delete query's

[Forms]![Form Name]![From Field]

Here is what I have for the code of the command button as well:

Private Sub cmdArchive1_Click()



DoCmd.OpenQuery "AddRecord"
DoCmd.OpenQuery "ArchiveRecord"


End Sub


Can any one offer any help??


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

Code:

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\"
myfile = "MTS_XDOCK_PATHS.XLS"

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





Hi,
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 have code that copies an existing query to a new query. When I created the base query 'ciscoquerystructure' I used an existing table '0901', but I need to have the "new" queries I create from this code select from a DIFFERENT table. (i.e. 0902, 0903, 0807, etc)
Look:

Dim TableName As String, stSQL As String, CiscoTableName As String, CiscoFileName As String, CiscoQueryName As String

Err.clear

TableName = InputBox("Enter a Table Name for Updating", "Input")
CiscoTableName = TableName & "C"
CiscoQueryName = TableName & "CQ"

' Create append query
DoCmd.SetWarnings False
DoCmd.CopyObject , CiscoQueryName, acQuery, "ciscoquerystructure"

Right here, how can I tell the new query to use the new table as the source for data? i.e. new query named '0903CQ' should select records from table named '0903C'.


DoCmd.SetWarnings True
' Append cisco data to monthly table
DoCmd.SetWarnings False
DoCmd.OpenQuery CiscoQueryName, acViewNormal, acAdd
DoCmd.SetWarnings True


Hello,

I am using this command to export a query to Excel.

Code:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qry", "C:\Excel", True


Is it possible to name the Excel File before saving it?


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?
Code:

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


Thanks in advance.

a


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.Quit
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


Thanks!


Hello!

I have written an Access macro that will take a query I have and save it as a .csv file. The problem is that (using the TransferText command) I have to specify the file name and location to save it in advance, as part of the TrasnferText command.

Is there a way to do this so that the Save As window opens, or something like that so that the user can specify where and what name to save the .csv file as?

Thanks!

Chris


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.

Code:

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


    Else
        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)

Code:

Function fncFileSelected()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
  fd.AllowMultiSelect = False
  fd.ButtonName = "Save"
  fd.Filters.Clear
  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 having an issue running the following code.

asically im trying to step thru .txt in a file and import. they are downloaded ftp file saved as txt. you'll notice in my code for the strFile string i have to add a "'". this is the only way i could find the file as is step thru the code but now i'm getting a error:

getting a "you cannot import this file". error 31519!


Code:

DoCmd.SetWarnings False

'delete the import to table
DoCmd.OpenQuery ("qry_Import_File_DELETE")

Dim strFile As String
Dim theFilePath As String
Dim theFileName As String
Dim strFull As String

ChDir ("G:\Mypath\")
theFilePath = "G:\Mypath\"

strFile = "'" & Dir(theFilePath) & "'"

'strFull = theFilePath & strFile
Do While Len(strFile) > 0
'MsgBox (strFile)
DoCmd.TransferText acImportDelim, "FTP_Import", "Import_NonMon_File", theFilePath & strFile, False

'delete the File data
DoCmd.OpenQuery ("qry_Append_File_Date_DELETE")

'delete the header and totals row
DoCmd.OpenQuery ("qry_Append_File_Date")

'delete the header and totals row
DoCmd.OpenQuery ("qry_Import_File_CleanUp")

'append to the master table
DoCmd.OpenQuery ("qry_Import_File_Append_To_Master")

'update the master table with the file name
DoCmd.OpenQuery ("qry_Import_File_Update_Date_To_Master")

'proceed to the next file
strFile = Dir()
Loop

'set warnings back on
DoCmd.SetWarnings False


thanks a ton.


All,

Have working code at:

http://www.mrexcel.com/forum/showthread.php?t=104031

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!

Thanks!

OMR


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"
.Open
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:

Code:

'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"


where

Code:

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?

Thanks
Tuk


hello

I need help writing a macro Please.


I have create query and now want to Transfer that query to Text. I figure that part now i would like to now how to create columns M and add this formula to Column M SUMPRODUCT(--($l$1:l1=l1),--($l$1:l1=l1))


DoCmd.TransferText acExportDelim, "", "Invoices", "C:\Documents and Settings\Jon\My Documents\test\Book23.csv", False, ""

Range("M1").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--(R1C12:RC[-1]=RC[-1]),--(R1C12:RC[-1]=RC[-1]))"
Range("M1").Select
Selection.AutoFill Destination:=Range("M1:M1175")
Range("M1:M1175").Select

End sub




Thanks


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.

Code:

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:
  appAccess.CloseCurrentDatabase
  appAccess.Quit
  Set appAccess = Nothing
End Sub


Thank you in advance for your help and understanding!


Hi
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:
Code:

 
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


Thanks


Hello

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:= _
xlTextPrinter

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:
Code:

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, ""
    Beep
End Function


Is there anyway of doing this?

Thanking you!


hello

I need help writing a macro Please.


I have create query in access and now want to Transfer that query to Text. I figure that part now i would like to now how to create columns M and add this formula to Column M SUMPRODUCT(--($l$1:l1=l1),--($l$1:l1=l1))


DoCmd.TransferText acExportDelim, "", "Invoices", "C:\Documents and Settings\Jon\My Documents\test\Book23.csv", False, ""

Range("M1").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--(R1C12:RC[-1]=RC[-1]),--(R1C12:RC[-1]=RC[-1]))"
Range("M1").Select
Selection.AutoFill Destination:=Range("M1:M1175")
Range("M1:M1175").Select

End sub




Thanks


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 new to VBA and could greatly use some help with an error I am receiving. My goal is to add a single record from one table to another based on the current record on a form. I have successfully created a append query to do so which operates properly. I have created the following code in VBA to perform this operation using an "On click" event. I am receiving a "Run-time error 13, Type mismatch" when running the code. I have included the code and SQL statement below. Thanks in advance for your help.

INSERT INTO tblWorkOrder ( WoNumber )
SELECT tblWoReceivingInsp.WoRecvInspRecvrID
FROM tblWoReceivingInsp
WHERE (((tblWoReceivingInsp.WoRecvInspRecvrID)=[Forms]![frmWoReceivingInsp]![WoRecvInspRecvrID]));

Private Sub OpenWoButton_Click()

Dim mySQL As Integer
mySQL = "INSERT INTO tblWorkOrder ( WoNumber )"
mySQL = mySQL + " SELECT tblWoReceivingInsp.WoRecvInspRecvrID "
mySQL = mySQL + " FROM tblWoReceivingInsp "
mySQL = mySQL + " WHERE (((tblWoReceivingInsp.WoRecvInspRecvrID)=[Forms]![frmWoReceivingInsp]![WoRecvInspRecvrID])) "

DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True

End Sub


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