Can't import xls file unless it's open

[rbm]

I am importing an Excel spreadsheet Named Range into an
existing Access 97 table using the TransferSpreadsheet
Action. I had my share of weird problems, but I seem to
have got to the point where everything will import
correctly. BUT this only works when the Excel file is
open. If I close Excel (or just the worksheet), it won't
import at all - I get a "Numeric Field Overflow" error.
It I open the spreadsheet and then go back to Access and
run the procdure again, I get no error and the data
imports.

This is the code I am using:
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "tblRegistration", varFileName,
True, "Registration"

"varFileName" is the name of the file containing the data
to import (C:\Program Files\Access\Register.xls")

"Registration" is the Named Range in the spreadsheet

It doesn't seem right that I should have to have the file
open in order to import data.

 

[John Nurick]

Hi rbm,

A couple of thoughts: are there any autoexec macros in the workbook, or
does it contain links to other data sources (including other workbooks)?

Is the range you're importing a simple block of cells containing fixed
values, or does it contain formulas?

 

[Guest]

Thanks for the reply. There are no autoexec macros in the
workbook and no links to other data sources. The range I
am importing is largely formulas - mostly references from
a set of input fields.

The Range I am imported is also locked and hidden. The
sheet is password protected with everything but "Content"
unchangeable. The workbook is unprotectd.

I found later (I have another post above this one), that
the problem only occurs when I import to a linked table.
If I copy that same table into the frontend database, I
can import the Excel file with no problem. I am going to
try to import to a temporary table in the frontend and
then, by a query, send the data to the backend database.

-----Original Message-----

Hi rbm,

A couple of thoughts: are there any autoexec macros in the workbook, or
does it contain links to other data sources (including other workbooks)?

Is the range you're importing a simple block of cells containing fixed
values, or does it contain formulas?

I am importing an Excel spreadsheet Named Range into an
existing Access 97 table using the TransferSpreadsheet
Action. I had my share of weird problems, but I seem to
have got to the point where everything will import
correctly. BUT this only works when the Excel file is
open. If I close Excel (or just the worksheet), it won't
import at all - I get a "Numeric Field Overflow" error.
It I open the spreadsheet and then go back to Access and
run the procdure again, I get no error and the data
imports.

This is the code I am using:
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel97, "tblRegistration", varFileName,
True, "Registration"

"varFileName" is the name of the file containing the data
to import (C:\Program Files\Access\Register.xls")

"Registration" is the Named Range in the spreadsheet

It doesn't seem right that I should have to have the file
open in order to import data.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.