Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trying to export an Access Report to Excel using VBA

Posted on 2002-03-20
13
Medium Priority
?
420 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

610 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