asked on

Running access query with specs to .txt file

I have a form in Access 2010 that runs the following line of code when a button is pressed:

DoCmd.TransferText acExportDelim, "mySpecs", "myQueryName", "C:\myFileName.txt", False

I wish to use Excel and perform this same exact functionality using VBA within Excel.

I am able to run the query and have the output in Excel just fine, but I need to use the specs that are saved within the database. Any ideas?

Thanks!

Rgonzo1971

HI,

pls try

Dim acObj As Object
Set acObj = CreateObject("Access.Application")
acObj.Application.Visible = True
acObj.OpenCurrentDatabase "C:\testMDB\TEST.accdb",,"ADatabasePassword"
acObj.DoCmd.TransferText acExportDelim, "mySpecs", "myQueryName", "C:\myFileName.txt", False
acObj.Application.Quit
Set acObj = Nothing

Open in new window

Regards

aikimark

You can use Access automation from within Excel. It would look something like this:

Dim oACC as object
dim oDB as object
set oACC = Createobject("access.application")
oACC.Opendatabase "your database path and name"
oACC.DoCmd.TransferText acExportDelim, "mySpecs", "myQueryName", "C:\myFileName.txt", False
oACC.Close
oACC.Quit
set oACC = nothing

Open in new window

You could also populate an ADO recordset variable in Excel and do your exporting directly from the recordset or through an intermediate variant array (populated with GetRows method)

mak345

ASKER

Using either of the above code, I get the following message:

The Microsoft Access database engine could not find the object 'myFileName.txt'. Make sure the object exists and that you spell its name and the path name correctly. If 'myFileName.txt' is not a local object, check your network connection or contact the server administrator.

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mak345

ASKER

Thank You!!