Can't import xls file unless it's open

  • Thread starter Thread starter rbm
  • Start date Start date
R

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.
 
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?
 
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.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top