Problem importing excel data

[Guest]

Hi,

I'm new to access and been having this problem with importing an excel file
to Access. The excel file is in a form and I have linked the form data cells
to first and second row. For example, the form uses Column A to I, rows 1 to
112. The linked data is starting from Column J to BU, with Field names in
row 1 and data, formulas in row two.

When I import for the first time and let Access link the Field names in
excell for access, it sometimes imports okay, sometimes not. When I try to
import the excel file with data, I keep getting an error, but nothing telling
me what the error is.

Is there a problem having formulas in row 2 to pull the relavent data from
the form? I don't know if it is my work computer causing the problem,
because my company unfortunately doesn't seem to install the patches and
updates for both the OS and Office 2000. Thank you

 

[j.nurick]

I'm having trouble understanding your terminology. You say 'the excel
file is in a form', but an Access form can't contain a file. Nor does
Access have 'form data cells'.

You can have a text box on a form whose value is the name (or path and
name) of an Excel file: is that what you've got? Or you can have a
form that is bound to a linked table that is connected to a worksheet
or range in an Excel file: is that it?

I'm totally mystified by 'having formulas in row 2 to pull the
relevant data from the form'.

Please try and explain more clearly.

 

[Guest]

Hi John,

Sorry for the confusion. I'll see if I can explain it properly.

First, I have recreated a word document that is a problem report form into
Excel. So Columns A to I and Rows 1 to 110 are being used for the report. I
have cells with text for the questions and cells that are blank for the
answers. Also have some check boxes and option buttons as well.

Now for Columns J to BU, Row 1, I have heading names that are related to the
blank cells for the report. In Row 2, it receives the data entered in the
answer cells.

I hope that explains what I mean.

Now the problem is, when I do a first import into Access, I check the box
for "Row one containing heading names, or whatever it says". Then when I
have the option to choose which fields I want to be import, I skip the fields
that represent column A to I and only choose J to BU.

Now the first time importing is no problem, but when I try to import the
file again with different data and import it into an existing table, I get an
error and the import is aborted.

Is there a problem with access not able to match the fields to be imported
since I orginally didn't select columns A to I? Thank you

 

[j.nurick]

So you're actually just trying to import data from the range J1:BU2,
of which J1:BU2 contain field names.

That shouldn't be a problem, though it can be simplified by defining a
named range in the workbook covering J1:BU2 and importing the named
range.

I assume you're importing to an existing table. You must make certain

1) that the field names in the workbook match those in the Access
table

2) that all the data values in row 2 are compatible with the field
types, indexes and validation rules in the Access table.