Empty link

  • Thread starter Thread starter hsiang Fu via AccessMonster.com
  • Start date Start date
H

hsiang Fu via AccessMonster.com

I have used the "transferspreadsheet aclink" function for many years without problems. But now I have a spreadsheet which seems normal: it has a date type column & a few number-type columns, all with headers. But when it is linked, the linked-object from within axs, shows the headers, the date values ok, but the number values are all blank/null!

I have no idea why this is happening. The spread sheet originated as a csv file which directly opens in ms-xl, then the columns were copied in to the standard template xls file which is then linked. The linking does not give any errors.

Any hints will GREATLY appreciated.
 
Hi,

This could happen if the "numbers" in the Excel sheet are actually
stored as text.

Would it not be simpler to import the csv file into Access without first
modifying it in Excel?

I have used the "transferspreadsheet aclink" function for many years without
Click to expand...
problems. But now I have a spreadsheet which seems normal: it has a date
type column & a few number-type columns, all with headers. But when it
is linked, the linked-object from within axs, shows the headers, the
date values ok, but the number values are all blank/null!
I have no idea why this is happening. The spread sheet originated as a
Click to expand...
csv file which directly opens in ms-xl, then the columns were copied in
to the standard template xls file which is then linked. The linking does
not give any errors.
 
1. I have tried selecting the whole column and setting the FORMAT to NUMBER so they explicitly have decimal places. The previous setting was GENERAL. This did not help.

2. How can I go directly from CSV to Access? Does the same linking trick work?
Even if so, problem is that There are other sheets on the XLS template which are needed in the access app for import, so the data must be copied into the temaplte. The datasheet is by itself linked to a table which comes up empty!

Could something bad happen:
A. during the copying from CSV opened in XLS to another XLS?
OR
B. Converting CSV into XLS format during SaveAs?


Further comments will be appreciated
 
1) Please make absolutely sure that the numbers in the Excel sheet are
stored as numbers and not as text. This is the bad thing that could be
happening between CSV and XLS format. It is a separate issue from the
question of how the cells are formatted.

You can check this with the ISNUMBER() worksheet function or by setting
Format|Cells|Alignment|Horizontal to General - which will align numbers
to the right and text to the left. (The latter works in Western
installations of Access, but I don't know if it's the same in Chinese
ones.)

2) You can link directly to a CSV file with DoCmd.TransferText acLink...
By using an import specification you can exert much more control over
the linking process than when linking or importing an Excel worksheet.

In general there's no reason you shouldn't link some of the data from
the CSV file and the rest from the other sheets in the template
workbook. (Although if the other sheets in the template don't change,
you might as well import that data once into Access tables and in future
just link to the CSV file.)



1. I have tried selecting the whole column and setting the FORMAT to NUMBER so they explicitly have decimal places. The previous setting was GENERAL. This did not help.

2. How can I go directly from CSV to Access? Does the same linking trick work?
Even if so, problem is that There are other sheets on the XLS template
Click to expand...
which are needed in the access app for import, so the data must be
copied into the temaplte. The datasheet is by itself linked to a table
which comes up empty!
 
Another trick to ensure you really do have a number is to
create another column in excel and put a formula
of "yourcell" *1. The *1 seems to convert text numbers and
numbers into numbers .... ?

Terry
-----Original Message-----
1. I have tried selecting the whole column and setting
Click to expand...
the FORMAT to NUMBER so they explicitly have decimal
places. The previous setting was GENERAL. This did not
help.
2. How can I go directly from CSV to Access? Does the same linking trick work?
Even if so, problem is that There are other sheets on the
Click to expand...
XLS template which are needed in the access app for
import, so the data must be copied into the temaplte. The
datasheet is by itself linked to a table which comes up
empty!
 
Back
Top