Linking to Excel Problems 
Author Message
 Linking to Excel Problems

I am linking to an excel worksheet from Access97. There
are 300 rows of information. In some of the columns, data
population is scarce, you could go down 250 rows or so
before there is data in the column.

This is a problem, because for one reason or another,
Access is assigning a datatype to the Excel worksheet
column that is incorrect.

For example, Access has decided to assign one column as
a "Number" field, where in fact is text. When the column
comes up empty of values when I look at in Access, or
displays "#Num".

Is there any way to ovverride what Access wants to assign
to the excel columns? Or is there any trick that anyone
knows as a work-around?

Thanks in advance,

Sean Doherty

Remove "zz" before email address to email me directly.



Sun, 11 Jan 2004 07:23:34 GMT  
 Linking to Excel Problems
Pre-define a table having fields of the correct data types, then apply
TransferSpreadsheet() to the table.  (You can define the table quickly by
running a make-table query, and then adjust each resulting data type or
field size.)  Refill the table with new records as needed by first applying
a delete-query, then an append-query.  The delete-query leaves the table
structure and data types intact.


Quote:
> I am linking to an excel worksheet from Access97. There
> are 300 rows of information. In some of the columns, data
> population is scarce, you could go down 250 rows or so
> before there is data in the column.

> This is a problem, because for one reason or another,
> Access is assigning a datatype to the Excel worksheet
> column that is incorrect.

> For example, Access has decided to assign one column as
> a "Number" field, where in fact is text. When the column
> comes up empty of values when I look at in Access, or
> displays "#Num".

> Is there any way to ovverride what Access wants to assign
> to the excel columns? Or is there any trick that anyone
> knows as a work-around?

> Thanks in advance,

> Sean Doherty

> Remove "zz" before email address to email me directly.



Sun, 11 Jan 2004 12:56:49 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Excel Linking and Excel/Access Linking

2. Problems using VBA to link or DAO to import Excel file into Access97

3. Linking Word and Excel, problem saving

4. Linked Excel table - Field Names Problem

5. Linked Excel table - Field Names Problem

6. Linked Excel table - Field Names Problem

7. Linking to Excel - many Excel processes left open?

8. Problem opening Excel Addin with Excel application object

9. Compatibility Problem Using MS Excel 97 VBA on Excel 2000/XP - Causes crashes

10. VB and Excel problem - VB corrupts Excel environment

11. Problem with opening Excel after opening Excel inside Microsoft Internet Controls

12. Excel linked table

 

 
Powered by phpBB® Forum Software