MS Access 2002 VBA code for button to Save to File for a Report

Need VBA code for a button that will SAVE TO File an Access Report (MS Access 2002)
deer777Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn_MooreCommented:
I would suggest a learning experience.  It appears that you have already created your report.  With the Reports menu open, go to the access menu and Click on Tools, Macro, Record a new macro.  Then click on the report, and complete all the steps to save the report to a file.  Then Stop the Macro from recording.  Next, you have to create a command button and put the code into the properties of the mouse click on the command button.  
0
JezWaltersCommented:
Will the "DoCmd.OutputTo" method do what you want?

    DoCmd.OutputTo acOutputReport, "YourReport", acFormatRTF, "C:\Temp\YourReport.rtf"
0
Jeffrey CoachmanMIS LiasonCommented:
Glenn_Moore,

I'm using Access 2000/2003.

I do not have a "Record New Macro" menu option.
(I have only "VBEditor, "Run", and "Convert...)

Is this new in Acc 2007?

JeffCoachman
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jeffrey CoachmanMIS LiasonCommented:
deer777,

JezWalters's solution should work for you.

Just keep in mind, that the OutPutTo command will *TRY* to simulate the report's layout in a Word processing format.
It will not be perfect.

If all you need is the *Data* from the report, try exporting just the RecordSource of the report.
Use...
    DoCmd.TransferText acExportDelim, , "YourReportsRecordSource", "C:\Temp\YourReport.txt"
...for text

Or export to Excel:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "YourReportsRecordSource", "C:\Temp\YourReport.txt"

Hope this helps as well

JeffCoachman

0
JezWaltersCommented:
Jeff's quite right about how unsuccessful Access can be sometimes when it converts a report to Rich Text Format - it's best to try it yourself and see how well it works with the particular report you have in mind!

Good luck!  :-)
0
Jeffrey CoachmanMIS LiasonCommented:
JezWalters,

JW,

Just to keep things cool between us, I was not trying to say:
"Jez's solution is OK, but it has a downside"
;-)

It's just that there are so many posts here about the .rtf Report "not looking right", that I thought I should mention it.

So, anytime I see the Q, I ask for clarification.

Good luck with you continued ascent to the top of the ranks here!
:-)

JeffCoachman
0
JezWaltersCommented:
Hey, no problem Jeff - actually, I think my solution DOES have the downside you mentioned!  :-)

I only added my comment just to say I knew about the limitations too.  Like I said, it's well worth giving it a whirl to see how well the report comes out before worrying too much.

Somehow, I don't think I'll be climbing any higher in the ranks though - I have a day job you know!!  ;-)
0
JezWaltersCommented:
As a parting comment, it might be worth using a PDF printer driver (such as www.cutepdf.com) to save the report - you won't get any conversion problems printing to file!

Just a thought!  :-)
0
deer777Author Commented:
I was thinking something along the lines of:

dhFileDialog  

Just don't know how to bring up that Save screen
0
Jeffrey CoachmanMIS LiasonCommented:
deer777,

<Just don't know how to bring up that Save screen>

This can be done, but first, make sure you Export code is working flawlessly.

JeffCoachman
0
JezWaltersCommented:
It'd probably be easier to use the FileDialog property instead of messing around with an API call:

http://support.microsoft.com/kb/279508

You'll need to add a reference to the latest version of the Microsoft Office Library to do this.
Dim dlgSave As FileDialog
 
Set dlgSave = FileDialog(msoFileDialogSaveAs)
dlgSave.Show

Open in new window

0
JezWaltersCommented:
Where exactly were you thinking of invoking the Save dialog box from?
0
JezWaltersCommented:
I'm just wondering how FileDialog (or for that matter, dhFileDialog) will help - it just selects a file to save, but doesn't actually DO the save.
0
JezWaltersCommented:
Is this what you're trying to do?
Dim dlgSave As FileDialog
 
Set dlgSave = FileDialog(msoFileDialogSaveAs)
With dlgSave
    .Title = "Select file to save..."
    .ButtonName = "Save Report"
    .InitialFileName = "YourReport.rtf"
    If .Show Then
        DoCmd.OutputTo acOutputReport, "YourReport", acFormatRTF, .SelectedItems(1)
    End If
End With

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
deer777,

Are you still out there?

Jez, is providing lots of info.

One more thing though, whatever method use youes to invoke the save As dialog box, try to use a method that does not require any Dll/Ocx, registration issues.
This way a user does not have to worry about this.

JeffCoachman
0
deer777Author Commented:
Thanks much!!
0
JezWaltersCommented:
Just since Jeff mentions it (another very valid point!), of course the FileDialog property doesn't need any DLL registration.

On the other hand, FileDialog is ultimately not as flexible as directly calling GetSaveFileName() from comdlg32.dll, but has the advantage of being simpler - as long as you can do what you want!

Glad I could be of assistance!
0
Jeffrey CoachmanMIS LiasonCommented:
Go Jez!
;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.