Linking data from an excel spreadsheet

[rossmcauslan]

Hi,<br><br>I am trying to import some data from an Excel spreadsheet into an Acess database but am having some problems. If I user the import function when creating the new table the data appears fine. However as the spreadsheet is constantly being changed I would like to set up the table as a linked one. The problem is that when I do this, any field that contains only numbers in some of the cells is imported as #Num! in Access. eg:<br><br>Excel&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Imported table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Linked table<br><br>A18482&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A18482&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A18482<br>5081&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5081&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#Num!<br>NO ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NO ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NO ID<br><br>Does anyone know of any reason why this would be the case or is there something that I am doing wrong?<br><br>Any help would be much appreciated<br>Cheers<br><br>-------------<br>Ross McAuslan<br>IT Consultant<br>CIA Ltd<br><A HREF="mailto:ross@c-i-a.co.uk">ross@c-i-a.co.uk</A>

 

[WP]

Ross,<br><br>In order for your data to be held in a single field in ACCESS ( even with linked tables ) the data must be of the same format.&nbsp;&nbsp;&nbsp;&nbsp;The number is undoubtedly being held as numeric in your spreadsheet.<br><br>In order for your linked table to show the number you must tell EXCEL to hold it as Alpha-numeric ( i.e. type text ).&nbsp;&nbsp;So,&nbsp;&nbsp;Highlight the column in your spreadsheet and then Format, Cells... Category - Text <br><br>WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[rossmcauslan]

I have changes all of the formating in excel to text but in Access when the table is linked it is still shown as #Num!<br><br>Any other ideas??<br>Cheers<br>-------------<br>Ross McAuslan<br>IT Consultant<br>CIA Ltd<br><A HREF="mailto:ross@c-i-a.co.uk">ross@c-i-a.co.uk</A>

 

[WP]

Ross,<br><br>Delete the link and link it again.<br><br>WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[rossmcauslan]

I tried that and even tried 2 new smaller files ( the spreadsheet I'm working on contains 3500 rows). But obtained the same result.<br><br>This is getting quite frustrating - I know I can just keep importing the tables but it is not useful for the users when they want a report on the fly.<br><br>Are there any options in Access that I might need to set but am missing.<br><br>Cheers again<br><br>-------------<br>Ross McAuslan<br>IT Consultant<br>CIA Ltd<br><A HREF="mailto:ross@c-i-a.co.uk">ross@c-i-a.co.uk</A>

 

[rossmcauslan]

I tried that and even tried 2 new smaller files ( the spreadsheet I'm working on contains 3500 rows). But obtained the same result.<br><br>This is getting quite frustrating - I know I can just keep importing the tables but it is very not useful for the users when they want a report on the fly.<br><br>Are there any options in Access that I might need to set but am missing.<br><br>Cheers again<br><br>-------------<br>Ross McAuslan<br>IT Consultant<br>CIA Ltd<br><A HREF="mailto:ross@c-i-a.co.uk">ross@c-i-a.co.uk</A>