Solved

Trying to export an Access Report to Excel using VBA

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

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

13 Experts available now in Live!

Get 1:1 Help Now