Solved

Microsoft Access cannot determine which row in your list or selection contains columns labels, which are required for this command

Posted on 2006-11-10
6
930 Views
Last Modified: 2008-01-09
[[ 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
0
Comment
Question by:paquicuba
  • 3
  • 3
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 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.
0
 
LVL 23

Author Comment

by:paquicuba
ID: 17915093
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 + [+]"
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 17915159
> 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.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 23

Author Comment

by:paquicuba
ID: 17915246
I really appreciate your help and I'll see what I can do, but this report goes up to column "EE" and there are 45 empty columns.
0
 
LVL 23

Author Comment

by:paquicuba
ID: 17915898
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-exchange.com/Databases/MS_Access/Q_10123974.html?query=turn+num+lock+on&topics=39

I would split the points with "Trygve", but it looks like that he or she hasn't been around for a while...

Cheers!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 17916337
Can't split with someone that doesn't post in the question.

If that link answered your question and not my comments, then we can PAQ-Refund the question.
Otherwise, if my comments helped you, thanks for the grade.

Good luck with your project.  -Jim
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
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…

747 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

11 Experts available now in Live!

Get 1:1 Help Now