Want command button to output data to an Excel file 1

[elviajero]

I have a report based on a query. If I run the query I can choose through Office Tools to export the data to an Excel file. What I would like to do have a command button on a form that would either run the report based on the query or just run the query, and have the resulting data export into an Excel file.

It seems like there isn't a way to have the query do this every time - it seems you have to force it by using Office Tools which is not going to be available in the .mde file.

Any help would be most appreciated.

 

[tlbroadbent]

The button can start a macro that executes the TransferSpreadsheet action or VBA code that executes the DoCmd.TransferSpreassheet method. These functions are fairly easy to use and good help is available. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[ruthcali]

what about the TransferSpreadsheet method? ruth.jonkman@wcom.com

 

[gwinn7]

There is also the "OutputTo" method. However, watch out! Its a good function, but the documentation may be incorrect regarding the coding of it. If you are careful, it should work.

Gary
gwinn7

 

[elviajero]

Actually the output macro worked like a charm. I probably should have figured that out by myself but I haven't worked a lot with macros.

Thanks folks!

 

[gwinn7]

Good to see!

Good Job Elviajero!

Gary
gwinn7

 

[Guest_imported]

I am getting a message, File already exists. How Do I write code, to remove or kill the file first before it outputs.

 

[tlbroadbent]

In the macro add a step that runs the "SetWarnings" action to "No" before exporting. In VBA code use "DoCmd.SetWarnings (False)."

This will suppress warning messages.

Make sure you run the action "SetWarnings Yes" or method "DoCmd.SetWarning (True)" after the export so you'll see normal warning messages. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Guest_imported]

I am still getting run time error #53, file not found.

Please help.

 

[tlbroadbent]

mcampane,

I'm confused. One post indicated the message was "File Already Exists" but now it is "File Not Found." Are you doing the same operation each time?


Are you exporting or importing? You shouldn't get that error on export.

Are you using a macro or VBA code? Could you post the macro arguments or VBA code so we can review it? Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Guest_imported]

Thanks.
We are outputting a form from MS Access 97 to an html file, called "go.html".

We want it to run by itself every 5 mins, triggered by on timer on the form itself. That works ok.,but we want it to delete the original file "go.html" before overwriting it without user intervention. If we dont we get that error 53 msg. Not sure on how to do an if then else statement. Or what do you suggest. Please advise.


Function steve()
kill "c:\temp\go.html"
End Function

 

[tlbroadbent]

How are you doing the export? Which method or action do you use?

You can test for the existence of a file in this way.

Function steve()
If dir("c:\temp\go.html&quot = "go.html" Then
Kill "c:\temp\go.html"
End If
End Function
Terry

;-) USER, n.: The word computer professionals use when they mean "idiot." -Dave Barry

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[Guest_imported]

Hey thanks that worked perfectly....


Much appreciated.......