?
Solved

Trying to export an Access Report to Excel using VBA

Posted on 2002-03-20
13
Medium Priority
?
416 Views
Last Modified: 2008-02-01
I can do it manually through the menus just fine.

but when I try
DoCmd.OutputTo acOutputReport, "GraphData", ".xls", "C:\My Documents\GraphData.xls"

I get the error:

RunTime Error 2282
"formats that enable you to output data as a MS Excel, Rich Text, or HTML file are missing from the Windows Registry."

I assume if that were true, I wouldn't be able to do it manually either.

I want to export the report and not the query, because of the formatting on the report.

ideas?
thanks (yet again)
Sasha
0
Comment
Question by:SBFurr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 3

Expert Comment

by:TSellers
ID: 6883581
As it appears that ".xls" is not an invalid export type, then perhaps the path statement caused the grief and would more accurately be along the lines of:

"C:\Documents and Settings\Administrator\My Documents"



0
 

Author Comment

by:SBFurr
ID: 6883602
that just went way over my head.
why is C:\My Documents\GraphData.xls not a valid path name?

thanks
Sasha
0
 
LVL 3

Expert Comment

by:TSellers
ID: 6883672
Well, here is my thinking, but I have no idea if it is accurate but I also thought it would be easy enough to try and see if it works.

On my computer, there is in fact no absolute path called: C:\My Documents\
Although that folder will appear in explorer, it is virtually mapped there for your convenience. However, if I am instructing a program to find that location, I have to either do a virtual mapping to the absolute path, or give it the absoute path so that it can find where it really lives on your computer. On m computer the real path to the "My Documents" directory is as per the example I gave, perhaps yours is similar.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:SBFurr
ID: 6883760
I had no idea paths could be that complicated.  I thought if it showed up in WindowsExplorer, it was valid.
I can't actually see any folder with the path C:\Documents and Settings\Administrator\

but, I copied & pasted the path you suggested, just in case.  Unfortunately, I got the same error.

how do you go about finding out the 'real path'?

any other ideas for how to make this ******** run?
or an alternate way altogether?

thanks much,
Sasha
0
 
LVL 3

Expert Comment

by:TSellers
ID: 6883825
So we may or may not be on the right track, but I guess it's worth it to eliminate my suggestion one way or another just to be sure...

This should work for finding the correct path:

Go to My Documents in Explorer and go "File", "New", "Text Document", and create an unique empty text file called something like "Findmetoday.txt".

Then go to "Start", "Search", and do a search for "Findmetoday.txt".

The search window will give the absolute path in the "InFolder" col when it finds that file.

Now replace "Findmetoday.txt" with "GraphData.xls" in your line DoCmd.OutputTo acOutputReport, "GraphData", ".xls", "new path goes here"

and replace "C:\My Documents\" with the same syntax used in the "InFolder" col of the search window, and that should at least eliminate whether the path is the source of the problem or not.
0
 

Author Comment

by:SBFurr
ID: 6883835
the path that the search gives is C:\My Documents

so I guess it isn't the path.

Sasha
0
 
LVL 3

Expert Comment

by:TSellers
ID: 6883845
Argh, sorry I appear to have wasted your time then, I should have realized that not all configurations are like mine and it is entirely possible someone else could have a My Documents folder under their root.
0
 

Author Comment

by:SBFurr
ID: 6883882
I FOUND IT!

I made a macro which worked.
then I converted the macro to VBA to see what was different.

I should have thought of that first.

the error was in defining the output type as .xls
it is supposed to be MicrosoftExcel(*.xls)

wierd.
but it works.

so now what do I do with this question?

Sasha
0
 
LVL 3

Expert Comment

by:TSellers
ID: 6883897
That's great you found it. Humbling in that it was so simple of a solution withal. Wish I had though of it! That was ingenius to think of making a macro and then looking at it, I think I've learned from this as well.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6883931
you can ask a 0 point question in the community support with a link to this one and ask to get your points back and make a PAQ of this one

:O)Bruintje
0
 
LVL 7

Expert Comment

by:WiZaRd
ID: 6884504
Re the differences in paths here I would suggest one is running win9X & the other is running W2K/XP
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7062198
for SBFurr

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7080424
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question