Related Question - Import Specific Excel Cells - Microsoft Access Forum


	Back to Access Forum Comments List

Related Question - Import Specific Excel Cells

Melissa Clark

8 hours ago

I have a question related to RR's video "Import Specific Excel Cells into Microsoft Access" (https://599cd.com/blog/display-article.asp?ID=3391).

He explains several ways to select exactly which data to import, but I was wondering if there's a way to skip a certain range and import the rest of the sheet.

I work with a type of text files that I download, edit, and upload back into the original system. Each file begins with 14 lines listing rules/directions for the user. The column headings appear in line 15, and the data is in lines 16+.

I have been just opening each file in Notepad and deleting the first 14 lines, but I think there must be a better way. It takes a couple minutes to do, especially with files that have more than a hundred thousand lines.

Since I don't know how long each file is unless I open it, which is what I'm trying to avoid, I can't give a specific range (e.g., A15:V234987). Opening it in Excel to select and name the range would take even longer.

Is there a way to skip those first 14 lines during import? If it helps, each of those lines being with (**).

Thanks!

	Kevin Robertson @Reply 8 hours ago
Excel Import Cleanup Excel Import Cleanup 2

Richard Rost @Reply

4 hours ago

Thanks for those links, Kevin. Excel Import Cleanup demonstrates removing unwanted rows above the headings after importing an Excel sheet, and Part 2 handles the footer rows. They're useful background for automating the cleanup, but Melissa's text files need a different import step, so the code isn't a drop-in solution for her files.

Melissa, you don't need to know the last line number. Since the first 14 lines are always instructions, you can use VBA to read the original text file and create a separate working copy containing everything from line 15 to the end. That leaves your column headings as the first line of the working copy, ready for a text import with field names. No Excel range is needed, and the original file stays untouched.

The details depend on the file format. Are the columns separated by commas, tabs, or fixed-width spacing? Also, does the receiving system need those 14 instruction lines put back when you upload the edited file? Those details matter so the cleanup and export preserve the format that system expects.

Next Unseen