Importing Data from Excel is missing a field?!?

[AC5FF]

Okay I am stumped on this one...

I've got a Macro that imports data from an excel spreadsheet each morning. Up until yesterday morning this has worked flawlessly. I can't seem to find any reason it would have stopped working - but....

The import step still works - to an extent. The excel data gets imported just fine; except for one column. That is what is stumping me... Access is not giving me an error during the import (like column name missing/etc...) but yet it is like it just skips over that data. The fields in the excel file that are before this particulare column and the ones after it import fine - but this one is just blank...

IMHO ... just WIERD

I even tried an import into a new table... I let access do it's thing and all the data imported - except for this one column!!! I'm at a loss!

 

[DCrake]

Can you supply a sample of the xls file? it may be something to do with the first few rows of the workbook.

Have you tried this with a workbook from earlier this week that worked ok?

 

[AC5FF]

I'm loosing it....
The field that won't import is the last field in the excel sheet. There are added fields after this one in my table - but those are added through update queries in the macro.

I attached a sample spreadsheet - just removed all the data except for 2 rows...

 

[The_Doc_Man]

The first few rows of a spreadsheet define how the rest of the rows are to be imported - as numbers, text, dates, etc. If there is an inconsistency, the import wizard isn't so wizardly.

 

[AC5FF]

Looking at things again it appears to me like the wizard isn't even seeing the last column in the spreadsheet. But I just can't figure out why. There's no special formatting on this particular column - or any others for that matter.

I have selected the whole spreadsheet; changed formatting to text for all columns and still get the same thing - that last column just does not show up.

One other thing I tried ... and I don't see why it would have effected things ... was there are 30 columns in the spreadsheet. I deleted half of them thinking that maybe 30 was too many?!? ... But I got the same results - that last column just does not want to import...

 

[DCrake]

Well I just attempted to import the spreadsheet throught the wizard accepting the defaults for each step, and it worked fine. I also linked the spreadsheet and that was fine too.

 

[AC5FF]

I hadn't thought about just 'linking' in the spreadsheet... Hmmmm Might have to give that a try.

I don't really like the idea though because of the way our network is set up. It's not if, but when computers get upgraded they are just pulled out and replaced. Each computer has it's own 'name' for the network and I have to go in and totally re-write a long list of macros/VB/etc to get it running on a new machine. Just a total PITA
I've been working on updating all that using a config table that stores locations for all the files this DB has to import/link with - but that's been a slow process since maintaining the DB is only a 2ndary part of my job.. Really need to have a dedicated DBM just for this one system..

 

[gemma-the-husky]

are you 100% sure you are importing the spreadsheet, or a csv?

of you are importing a csv then you may have an import spec set to ignore certain columns - and a new column would definitely be ignored.

 

[AC5FF]

100% sure this import is a spreadsheet. I have others that are CSV and TXT that have import specs (Spent ages trying to get that working right!) but this one is a spreadsheet.