Solved

Trying to export an Access Report to Excel using VBA

Posted on 2002-03-20
13
414 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

687 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