[[ Microsoft Access cannot determine which row in your list or selection contains columns labels, which are required for this command ]]
Does anyone know how a method in VBA to suppress the above message while exporting to Excel, other than sending keys?
I'm using Access as a middle tier between Oracle and Excel; Access pulls the data from Oracle and outputs it to Excel in a required format, which includes subtotals. There are gaps between the columns in Excel, so Excel throws the above message as a warning, but everything works fine after I press Yes.
Tips: I tried using "DoCmd.SetWarnings (False)" , and it didn't work.
Right now I'm sending the ENTER "~" key twice, but it is causing other problems.
Thanks a lot!
Alex
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
I agree but this Excel report is for a manager who needs it upon request, and he wants those gaps. The empty columns to separate sections of the report can be created later, but the report is over 100,000 KB (40,000+ rows with formulas,) so it takes an unacceptable amount of time for a simple "CTrl + [+]"
> for a manager who needs it upon request, and he wants those gaps.
That doesn't necessarily mean it has to be exported that way, just that that is the final product.
Options:
(1) Export all data into an otherwise unused tab, then move ranges to their ultimate desinations, to accommodate cosmetic stuff like empty columns.
(2) Divide your current export into two, then export to two different locations on the same tab, to accommodate the empty column.
(3) After the export use Excel VBA to insert a blank column where it should be. Not sure if that's cosmetically an option.
jimhorn,
Once again, thanks a bunch for your help. I resolved the issues with SendKeys. One of them was turning the Num Lock on after the code completed --somehow SendKeys "{ENTER}" was turning it off.
I used this link: http://www.experts-exchang
I would split the points with "Trygve", but it looks like that he or she hasn't been around for a while...
Cheers!
Business Accounts
Answer for Membership
by: jimhornPosted on 2006-11-10 at 07:43:40ID: 17914983
>There are gaps between the columns in Excel
It would be better to handle this with Excel VBA after you export data from Access to Excel without gaps, into a tab you can delete later.