?
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
Medium Priority
?
999 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
[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
  • 3
  • 3
6 Comments
 
LVL 66

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 66

Accepted Solution

by:
Jim Horn earned 2000 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 66

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.
Suggested Courses

770 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