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

[[ 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
LVL 23
paquicubaAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
> 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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
paquicubaAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
paquicubaAuthor Commented:
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
 
paquicubaAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.