Solved

Trying to export an Access Report to Excel using VBA

Posted on 2002-03-20
13
373 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
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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now