Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 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 ...
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 ...
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 ...
Open PDF from Excel
How to open a PDF file from a clickable button, link, or image in Excel.  This allows you to seamlessly link PDF f ...
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







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!


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.


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

    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.


Hello,

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


my goal is to write code to import a statically named file to a tabel. i've tried usign the docmd.transfer text.

is this possible? I am getting an error with the txt header not matching my pre define field names.

here's my coding:

DoCmd.TransferText acImportDelim, , "RMT_Trns_UPLOAD", strFullTxtPath & "RMT_TXNS_CURRENT.txt", True

more on the error:
if i open the .txt the the first row has YYYYMM and then data below ...this needs to import to the first field of the table.....then there is a space in the txt file to the second header Ethnic....this needs to improt tot he second field of the table.

NOTE: the field names in my table match those int he .txt.

thnaks
tuk


i have excel's add-in of ibm showcase query and have also setup the query reference.

below is my code,

i = Application.Run("ScRefreshQuery", "C:\Program files\GP.DBQ")

this will help me open the query and have to key in my criteria, the query will automatically export to the excel according to the query reference.

I have 2 questions,
1)what i need to do to bypass typing the username and password when the showcase query open up.

2)is there any way i can link the criteria to enter in query to 1 of the cell in the excel file?


I have a workbook which I have created from a transfer query from Access, if the file exists I want to delete it, I know that I can use the Kill command like this

Quote:

Kill CurrentProject.Path & "\" & "DUA Export Binder And Lineslip.xls"

But I want to check to see if it exists first, if it does Kill it if not then the code will create it.


Hello everyone!

I've just started playing with importing data into excel from an access database using Data / From Access / and then selecting a table.

I can even edit the query by right on the data returned, clicking on Table, Edit Query and playing with the command text.

So - my OLE DB Query Command text can look like:

select blah from blah

My question is - is there a way to embed a variable in the command text? I'm hoping to create another worksheet with values and then name one of the cells as a variable, and then use that variable in the query of the OLE DB Query command text

So, if I have another worksheet and in cell $a$1 I insert the date 11/30/2010 and name that 'pull_date'

I'd like to use that variable in OLD DB Query command text as

select blah from blah where blah = %pull_date%

I can use any number of formulas once I have the data pulled in from my access db, but I'm wondering if there's a fun way to play with the query in the query definition?


Hi all

I'm fairly new to this so be gentle with me.
I've got a few queries to run in a database but some need parameters.
Here's what I've got so far:-
Code:

Function test()
    Dim qdf As QueryDef

    reducer = 7
    While Format(Date - reducer, "Dddd")  "Monday"
        reducer = reducer + 1
    Wend
    agent_start_date = Date - reducer
    leave_start_date = agent_start_date - 98
    
    qrystartdate = InputBox("Enter Start date of data", "First day of Data Required", Date - 7)

    DoCmd.OpenQuery ("Update_Agent_Information")

    DoCmd.OpenQuery ("Append_Agent_Information")

    qdf.Parameters(0) = agent_start_date
    DoCmd.OpenQuery ("Delete_Agent_Leave")

    qdf.Parameters(0) = agent_start_date
    DoCmd.OpenQuery ("Update_Agent_Leave")

    qdf.Parameters(0) = agent_start_date
    DoCmd.OpenQuery ("Append_Agent_Leave")

    qdf.Parameters(0) = leave_start_date
    DoCmd.OpenQuery ("Output_Query")
End Function


I've got the dates sorted but I can't seem to pass them to the query to use instead of a prompt box.

Anyone any ideas?


Is it possible to epxport two different Access '03 queries to one excel file. Two sheets holding the data from the two different queries? So basically I have Query A and Query B. I want to Export Query A to excel file ABC, sheet1 and export Query B to excel file ABC, sheet2. I tried this with the macro builder but couldn't figure it out. thx


Hi All,

I am trying to create VBA in Excel 2003 to open an Access 2003 db, and run a MakeTable Query. I'm not sure if I'm using the wrong vba commands for Excel, or what the problem is- but I can't get my code to work:
Code:

Sub RunQuery()
DoCmd.OpenDatabase "S:\path\file.mdb"
db.TableDefs.Delete "2010DataSet"
db.Execute "MakeDataTable2010"
db.Close
Set db = Nothing
End Sub


I'm getting a runtime error 424 'Object Required' on the DoCmd line. I thought the database was the object?

For the record, I don't need the Excel users to see the database, so I'd like to add a visible=false in there somewhere.

Any help is appreciated, I get completely lost when I try to cross programs in VBA


Hello,

I have a question about changing the format of a document made with OutputTo
command.

I have an Access database which I'm exporting some of it's data to an Excel
document. Guess the Access table is named "MyAccessTable". I'm using the
following command to export the data in that table to an excel worksheet and
open it with Excel:

DoCmd.OutputTo acOutputTable , "MyAccessTable",_
acFormatXLS,"C:\ExcelOutputFile.xls",True.

What I wish to do is that after the file is opened in Excel, a procedure in
Excel will run, making all the changes needed in the formatting of the new
Excel file.

I think I will handle writing the code for changing the format in excel, but
what I don't know is how to make that code run automatically right after the
file is made.

How can I make this procedure run right after the outputto command?
Where should the procedure/module code be stored? (In Access? In another
excel file?)


Kind Regards,
Amir.





I have written a module in Access VBA that creates a query. It is written in the "modules" section. It runs correctly when Im in VBA. I created an "event procedure" by:
right clicking on the command button, then selecting properties and under the "on click" field I double clicked the "..." button which brough up VBA and the following code:
Private Sub PullData_Query1_Click()

End Sub

This code is not in the module section, but as code behind the form where my command button is located.
I put my module name in between the first and last line, but when i try and run the query from the command button itself I get the following message: "Sub of function not defined". If I copy the code below the code for the button it runs fine. The only problem is that I run this module on mulitple sheets and I don't want to have to keep copying and pasting the module to each sheet. Is there any way to trouble shoot this? Thanks.


Does anyone know how to export an Access pivot table to Excel. I've used the docmd.transferspreadsheet in the past and it works great for raw query data, however I cannot figure out how to export the pivot table view in a query. Thanks!


All,

I am receiving an error from the Microsoft Query wizard when I try and create a query of an xlsx or xlsm file. The error is " External table is not in the expected format."

Can anyone tell me why this error is coming up and how I can correct the file so that I can write a query to pull info from a Named Range in the file.

I have written some code to duplicate the Microsoft query using ADODB but get the same error message when the Open connection line runs.

I have been using this method with these files for a while now but curiously it has quit working as of late. This is very frustrating as this is a critical method of my workflow.


Thanks for any help you can provide.

John M.


Hey guys,

I noticed some quite peculiar. I have an acces 97 database which uses the DoCmd.OutPutTo to output about 13,000 records from a query to an Excell 2007 spreadsheet. This takes about 6 seconds to complete in 97. After converting the database to Access 2007 format and running the same code it takes about 13 seconds.

Here is the original line of code that does the exporting:

VB:

DoCmd.OutputTo acOutputQuery, "TimeServDownload", acFormatXLS, "C:\DataIntegrityCheck.xls", True 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



After playing around the with third argument, the acFormat I noticed that changing this argument to any integer value other than acFormatXLS speed up the action significantly.

For example:

VB:

 
DoCmd.OutputTo acOutputQuery, "TimeServDownload", 0, "C:\DataIntegrityCheck.xls", True 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Will take about 4 seconds to output to Excell 2007 in Access 2007, reducing the time it originally took by 9 seconds.

Why does using the acFormatXLS constant slow things down so much? Would it be safe to continue to use 0 in place of acFormatXLS? Speed is a big concern for me.

My only guess is that acFormatXLS causes it to export using an old Excel format. After browsing around it seems that Excel doesn't particularly care what file format you use because it works with header information instead. It's still weird that this should slow things down.

I have an excel spreadsheet and am getting data from my SQL database. In MS Query I have a query to get 3 fields from a database but need it to look better.

When I try and use the 'format' next to date field, it gives me an error about 'does not recognize format command'.

Do I add the
format(tablename.fieldname,"mm/dd/yyyy")

in the 'select', 'where' and 'order by' areas of the statement?Currently I have it only in the select part of the statement.Thanks!

Before I get an input file into Excel for further execution, I need to have a *.bat command file do 2 simple things:

1) Append 1 existing .csv file to another existing .csv file - I have this command line in the *.bat as 'TYPE FILE1.CSV>>FILE2.CSV' and it works fine.

But,

2) I first need to edit the 'FILE1.CSV' and delete the first row of data(the column headings) before I Append the 2 files.

Does anyone know the *.Bat file command line to do this?

This user does not have access to Excel, but they are the only one that has access to the original data files. Otherwise, I'd do this easy Append in Excel, but can't.


Dear all,

I looking for a solution to change a Access Query Criteria using a VB code in Excel, without opening the Access DB and changing the criteria manualy?


My normal Job is it:
1) I Work all the time in Excel
2) At the end of the work in Excel, I need to open Access
3) Open a query (Called "MyQuery")
4) Change one parameter the Date: Between 01/10/2008 and 30/10/2008 (This for each month)
5) Run the query

I looking to get rid of points 2) to 5) by replacing with a Command button on an Excel sheet


Sub Change_Criteria_And_Run_Query()


Dim mydbase As Object
Set mydbase = CreateObject("Access.Application")
mydbase.OpenCurrentDatabase ("C:\My doucments\DB1.mdb")

mybase."MyQuery"."Date Criteria" = Between (Worksheet("Sheet1").Range("A1").value) and (Worksheet("Sheet1").Range("A2").value)

>>>>>The above line of code I have problems to get right!

mydbase.DoCmd.RunMacro "MyQuery"

End Sub


Is this possible to do?



Thanks for any tip
Jerry


Hi,

I'm trying to make a query on another file. In the first column I want to extract I have the product code which is sometime a number and sometime in text format. The query gets the one that are numerical and leaves blank for the text ones. I tried to convert the source file to have all in text format and it works within the query. I can't really change the source, is there a way in the sql statement to force all value as text? No problem to add a text character in the query if it would be possible to force it as text...

Product can be like 28 or 28XLS or HP2001...

Any idea ?

Thanks
Eric


Good morning all!

I originally posted this query on the Developer board, as I thought it was a
problem with my code, but now I'm not so sure.

I have a workbook with a macro that runs a Save As within a loop, to create
test data files.

I was finding that for every new file I created, and additional file with a
filename comprisiong eight alphanumeric characters and no file extenson was
created in the same folder.

Thinking that the workbook might be corrupted, I created a new workbook
(with NO copying/pasting from the original - everything types in from
scratch) , created new code modules and copied the code first into Notepad,
then into the new workbook.

The problem still occurred.

Finally, I noticed that it even happened when I did a simple File Save As
from the menu, so I'm fairly certain it isn't my code that's causing the
problem.

Could my Excel document template be corrupted in some way?

Can anybody think of any tests or checks I could carry out on my workbook to
look for corruptions that might be causing these additional files to be
creaed? I'm assuming that they're TEMP files that are being created, but not
deleted, during the Save As process.

Yours in desperation, and thanks in advance

Pete Rooney




Hi,

Two questions (background below):
1. Why did Excel suddenly start prompting me to specify a ODBC data source?
2. How can I edit old *.dqy queries in MS Query now that it gives the error "Data source name not found and no default driver specified"?

Explaination:

I have a workbook that contains several macros (recorded many years ago, not written from scratch) that call on some *.dqy query files (also created many years ago, using MS Query) to query data located one worksheet and output the results into different worksheets. I currently use Excel 2003 on Windows XP.

About a year ago, I was working on a different project using SQL and databases using OpenOffice Base. After the first time I used that software, Excel suddenly started asking me to specify an ODBC data source when running my old macros in this old workbook. I was puzzled, but fixed it by making a new data source out of the workbook. Why did this start happening? Can I make it stop?

Now these macros have stopped running again (getting stuck at .Refresh BackgroundQuery:=False). In an effort to debug, or perhaps just rewrite the macros properly, I tried to check the *.dqy query files in MS Query. However, MS Query does not seem to like that these files were created before my computer suddenly started wanting me to specify ODBC data sources, and gives the error "Data source name not found and no default driver specified" followed by "Couldn't read file".

Any insights welcome. This ODBC stuff is all new to me!

Thanks!
-Amanda


I want to get the data directly from sharepoint site, where i have created a view.
i tried exporting the view as .iqy file and tried editing the query for the file but my edit query button is disabled always.

Please let me know how can i achieve this

Thanks
Sathish


I have an Access 2007 database and I am trying to export a query using the transferspreadsheet command to a folder on my desktop, Excel 2007. I am able to export if I use a file extension of xlsx but if I try xlsm I receive "runtime error 3027, cannot update database or object is read only". I have all the permissions to the folder.
I have changed all of the Excel macro security settings that I could find and am at a loss. The reason I need a macro enabled workbook is because it contains 3 worksheets, the exported data from Access, a pivot table and a pivot chart. I was looking for a way to refresh the pivot chart when new data was added so I added some code to the pivot chart worksheet but it told me I had to save the workbook as a macro enabled workbook so that is why I tried to export the data in the transferspreadsheet command as xlsm.

I hope I've made myself somewhat clear and any help is greatly appreciated.


I have a query which uses another excel file as the data source. When I add a CASE statement to the query I get the following error: "syntax error (missing operator) in query expression"
The syntax I used is as follows:
Code:

case when column1 = 'Other' then column2 else column1 end as MyColumn


Apparently there are issues with using a CASE statement in a query that targets an excel file.
What is the syntax for a conditional statement in the SELECT clause of a SQL query which targets an excel file?
Thanks!