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
943 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
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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 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

27 Experts available now in Live!

Get 1:1 Help Now