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
981 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 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
Technology Partners: 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!

 
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

Independent Software Vendors: 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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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 …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

717 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