Help!!! Excel column is invalid when spreadsheet is linked as a ta

[Guest]

Hi,

I'm having a very rare, but deadly problem with Excel and Access. Every
day, I get data in an application-generated Excel sheet with always the same
structure, and I link it into an Access database by using the command:

DoCmd.TransferSpreadsheet acLink, , "tbInput", strPathAndFileName, False

I process that linked table with VBA code and store some of its columns and
data in another, permanent table in Access.

This works perfectly for about 95% of the cases. However, at unpredictable
times, suddenly a column in Excel which was full of numbers (but for the
first 3 rows because these contain headers) ends up, after the linking,
showing only "#Num!" where the numbers were. That crashes my code (with
numeric overflow error) which is reading the content of the spreadsheet,
because I'm expecting a number in that column, not an "#Num!". I can't make
out when this is happening, i.e. what's the cause. Moreover, this happens on
my users PC's, but NOT on my PC if I copy the DB and the Excel sheet ! It's
only one column, and when it happens, it's always affecting the same column.

Has any of you had this problem before, if yes what is it due to ? How can I
avoid it ?

I'd be VERY thankful for any help and hints on that one.

Regards
Balex

 

[John Nurick]

Hi Balex,

I'm surprised it works 95% of the time. AFAIK this

DoCmd.TransferSpreadsheet acLink, , "tbInput", _
strPathAndFileName, False

tells Jet to link to the first sheet of the workbook, starting at cell
A1 and continuing to the bottom right cell of the UsedRange, even though
- you tell us - rows 1 to 3 contain heading information.

I suspect that the heading rows are confusing the algorithm that Jet
uses to work out field types. The reason it behaves differently on
different computers may reflect different versions or service packs of
Access or Jet, or perhaps different registry settings (see
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
for more about these).

A good workround might be to use Automation to open the workbook and
delete the first three rows of the sheet, then save and close before
importing.

 

[Guest]

Thanks a lot, John, I saw the link you indicate also in another mail and I
guessed the problems were lying in the registry entries, which they were
(Text instead of Majority Type), so I changed my settings and tried on my PC.
Unfortunately, I cannot make the same change in the registry of the users'
PCs, because they do not have admin rights on their PC (big org...), I had to
fool Excel there by adding numbers in the header rows, but that's a boring
and confusing thing to do for the users on a daily basis, so, next to your
idea of stripping the header rows, I'm looking at other alternatives like
having the spredsheet format changed, or getting right at the source of data,
which is a Sybase DB.

Balex