Importing Excel Data - Recognizing Headers

[Ken Hudson]

I posted this questoin in the Excel group but didn't get a response. Perhaps
someone here has answer.
I have an Excel macro that creates a workbook with column headings in row one.
Data is listed in rows two and below.
When I go into Access and import the Excel workbook, the import wizard
opens. The first screen of the wizard looks for column headers and, if Access
"senses" that headers are present, it automatically checks the "first row
contains column headings" box.
I have two workbooks that look pretty much the same to me but Accees
recognizes headers in one and not the other.
Is there some kind of formatting or other trick I can use in the Excel macro
to make Access always recognize headers in this workbook?

 

[Klatuu]

If the headers are in row 1 and the data starts in row 2 and each column with
data has a header, then there should be no problem.
If you start the data in row 3, it will create this problem. Also, be sure
there are no other cells to the left or right of the data, but not contiguous
to the day you are trying to use that have values in them.
Access looks as the UsedArea of the worksheet to determine what to do. Non
contiguous data will cause this sort of behavior.

 

[Ken Hudson]

Hi Dave,
Thanks for the response. Everything looks as you say it should. When I hit
control-end in Excel, it takes me to the last cell of data and all cells are
contiguous.
How does Access differentiate a header row from a row with data in it?
If every column in row one has data in it, is it supposed to default to a
header row in the import wizard? It doesn't in this case.
It isn't that critical, it's just bugging me at this point.
Sample data:

Key Station ACC
Fund
6639200405GA8180S94 663 9200405GA 8180S
6639300405GA8180S94 663 9300405GA 8180S
6639400405GB8180S94 663 9400405GB 8180S
6639400405GB8180S94 663 9400405GB 8180S
6639600405GC8180S94 663 9600405GC 8180S
6630100403T10152A107 663 0100403T1 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403000152A107 663 010040300 0152A1
6630100403160152A107 663 010040316 0152A1
6630100403160152A107 663 010040316 0152A1
6630100431000152A107 663 010043100 0152A1
6630100431000152A107 663 010043100 0152A1
6630100431450152A107 663 010043145 0152A1
6630100441090152A107 663 010044109 0152A1
6630100441090152A107 663 010044109 0152A1
6630100652T10152A107 663 0100652T1 0152A1
66301AEN30Q20160A107 663 01AEN30Q2 0160A1
66301AEN30Q20160A107 663 01AEN30Q2 0160A1

 

[Klatuu]

<ctrl><end> is not a reliable way to test this. That only takes you to the
end of the contiguous data in the column or row you are in.
You can try this. Open your workbook and select the workdsheet and go into
the VBA editor <Alt><F11>
Then in the immediate window type:
activesheet.usedrange.select
Go back to the spreadsheet and you will see the usedrange highlighted.

 

[Ken Hudson]

Thanks.
I did and got the same results as control-end.

 

[Klatuu]

Ken,
Okay.
I don't really have another idea. I wonder if the workbook could have some
sort of corruption in it.
Sorry I could not resovle this for you.

 

[Ken Hudson]

That's okay.
Thanks a lot for the input!
When I first created the macro, Access recognized the row headers. I tweaked
it a bit (and don't remember the things I changed) and then Access didn't
recognize the headers. The outputs looked exactly the same and that was my
dilemma.
I'll keep looking and let you know if/wehn I find the answer.

 

[Ken Hudson]

Well, I think I figured it out.
The second column of data (see the 663 in the sample I sent) was stored as
text, even though it was numeric.
When I changed the macro to format that column as numeric, Access recognized
the headers.
I still have other columns with numbers entered as text, but they don't seem
to prevent the header recognition.

 

[Ken Snell \(MVP\)]

Best way to do this is to use TransferSpreadsheet action (macro or VBA) in
ACCESS and set the HasFieldNames argument to True. That will "force" ACCESS
to see that first row as having headers in them.

I'm not completely sure, but I'm guessing that ACCESS / Jet decide whether
the first row has field names in it by scanning the first x rows and looking
to see if data type changes as you go from the first row to the second row
in a column.

Are you trying to automate this process? Or just that you wish to avoid
having to manually check the "field names in first row" checkbox during the
import?

 

[Ken Hudson]

Hi Ken,
Thanks for the feedback.
Just trying to avoid having to cllick the checkbox. I will be sending this
out to scores of users and the fewer keystrokes/clicks they have to do, the
less chance for error.
The TransferSpreadsheet option would work, but each user will be storing the
workbook somewhere on their individual network. The macro requires a specific
path and filename I believe. You can't "browse" to it.

 

[Ken Snell \(MVP\)]

Actually, you can browse to the file's location, but it requires the use of
some VBA code that the macro would call. If you want to pursue this concept,
post back.