transferspreadsheet error - importing

[Mandy]

I have created a macro to help my users to import excel data to access by
using transferspreadsheet import. It works for several times, then start
experiencing issues on only partial data get imported. There are no error
files get created. It is just missing data. Originally, I thought my
database was corrupted. The problem was solved by re-creating another
database. However, after a several importing, the same issue re-appear. It
leads me to think that there are some application issues on this macro
function. It works perfectly when I manually import the file. The file is
only 323 records. Can anybody help me with this?

 

[Ken Snell [MVP]]

Perhaps we can help... but, tell us more details... what are the data in the
spreadsheet? what are the macro action's arguments? Are you importing to an
existing table or to a new table? etc.

 

[Mandy]

Macro:
OpenQuery (To delete the existing table)
TransferSpreadsheet (TransferType: Import; Has Field Names: Yes)
OpenQuery (To update the imported data to another table)

Excel file: 20 fields all together, issues start appearing on the 16th field:
15th field - Activity type (text, with drop down box for users to select the
right category)
16th field - settlement type (text with drop down box) (actual, proposed or
not settled)
17th field - settled ID (text)
18th field - settled amount (number) - formula on excel =if field 16 <> "not
settled", field 14 *-1, "")
19th field - comments (text)
20th field - IC Associate (text with drop down box)

Issues: After macro is run or importing is completed, the table will have
the following results.
When 16th field has "proposed", 17th, 18th and 19th are blank and 18th is
zero even they all should have value.
However, no issues when the 16th field is "actual"

 

[Ken Snell [MVP]]

By "text with dropdown box" (e.g., "16th field" in your description), I
assume you mean a cell in EXCEL spreadsheet that has a "dropdown" box from
which the users can select from a limited option of values?

You posted "When 16th field has "proposed", 17th, 18th and 19th are blank
and 18th is zero even they all should have value." Does the 18th field have
a zero or is it blank? What type of values are supposed to be in those cells
that are coming up blank?

What values are in all the cells (1st through 20th)?

Are you sure that the values are missing after the import and not as a
result of the second update query?


--

Ken Snell
<MS ACCESS MVP>

 

[Mandy]

Please see the below response.

By "text with dropdown box" (e.g., "16th field" in your description), I
assume you mean a cell in EXCEL spreadsheet that has a "dropdown" box from
which the users can select from a limited option of values? YES

You posted "When 16th field has "proposed", 17th, 18th and 19th are blank
and 18th is zero even they all should have value." Does the 18th field have
a zero or is it blank? What type of values are supposed to be in those cells
that are coming up blank? There are no blank value and all are zero, which supposes to be an amount on each record.

What values are in all the cells (1st through 20th)? 1st and 2nd- Text (T); 3rd and 4th - Number (N); 5th to 9th - T; 10th - Date/Time (D/T); 11th - T; 12th - D/T; 13th to 17th - T; 18th - N; 19th and 20th - T

Are you sure that the values are missing after the import and not as a
result of the second update query? Yes, very sure. Especially, the same command was working before. After a several good importing, it stops working.

 

[Mandy]

Hi Ken,
I have been doing testing on this and I think the below info might help.

This is my design. First, I export the data file from Access to Excel. On
the excel file, I set up macros for users to add dropped down menu on a few
fields and a if,then statement on one of the fields. After the users update
the data, then import this file back to Access.

I am thinking Access somehow get confused the data type after the above
formatiing. The reason being is resoved if I import the file as csv.
However, it causes all the formatting problems that I need to resolve.

Please let me know what you think.
Mandy

 

[Ken Snell [MVP]]

I am not following in my "mind's eye" the entire process that is occurring
here. Nor am I completely sure I understand the problem. I don't know what
'macros' you're running in EXCEL, nor what the formula is that you use in a
cell, nor which cell has the formula. (I cannot "see" your files or setup,
so I rely on your careful, full description of what you have and what you're
doing at each step of the data export/update/import process.)

Am I correct in saying that the data from the EXCEL file are being imported
as if they were zeroes instead of the actual values that are in the cell? If
this is correct, I am guessing that the EXCEL file is "writing" values into
the cells as text and not as numbers, thus the import "fails" when the
import process sees text instead of numeric. Especially if the "default
value" for the numeric field is a zero.

Are you importing into a temporary table? Is the table already in existence,
or does the import process create the table? What if you let the import
process create its own table; do the data values come into ACCESS ok
(meaning you see the values and not zeroes)?
--

Ken Snell
<MS ACCESS MVP>

 

[Mandy]

Finally, I am able to identify the root cause. Thanks for your help.

 

[Ken Snell [MVP]]

We're interested in knowing what caused the problem. Please let us know.
Thanks.

--

Ken Snell
<MS ACCESS MVP>