Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Linking - how to keep the column headings if they aren't on the first row?

Posted on 2014-01-29
2
Medium Priority
?
389 Views
Last Modified: 2014-01-29
Hello - I've got a situation where I've got to get the rows from some HUGE Excel 2010 spreadsheets (200K+ rows) into Access tables, so I'm trying to link to them, rather than import.

Most of these workbooks have multiple worksheets, with report headings and dates in the first several rows. Right now, I'm specifying a range that is just the tab name.

When I link with "First row contains field names" = False, field names come across as "F1", "F2", etc...

Is there a  slick trick for applying the values in, say, the 6th row as field names?

Thanks
0
Comment
Question by:mlagrange
2 Comments
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39817457
I generally use code to open the spreadsheet, delete the first 5 rows, then save the spreadsheet with a different name (so you don't destroy the original).

Then I Link to the new spreadsheet and the Column headers show up the way I want.
0
 

Author Closing Comment

by:mlagrange
ID: 39818602
That's a great idea - Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

971 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