Standard Import is Scrambling Numbers

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I have a Pivot Table in Excel and I want to import the data into Access. I
run this code:
'etc...
DoCmd.RunSQL ("DELETE * FROM [Bookings - Pipe by Advertiser];")
DoCmd.TransferSpreadsheet acImport, 8, "Bookings - Pipe by Advertiser",
"C:\Documents and Settings\rs\Desktop\Forecasting\Up To Access\RVP
Revenue-Pipe.xls", True, "Pivot Sheet!A5:N2000"
'etc...
The names comes in fine, but the numbers sometimes appear random. Sometimes
the numbers are accurate and sometimes they are not (like off by 10k, and
other random amounts). The table is set up as data type Text and Number.
Does number have to be some other data type? What could cause this?

Please help.

Thanks,
Ryan---
 
Are you importing the pivot table or the underlying raw data?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks for looking Jeff. Actually, I created a macro in Excel to get the
Pivot Table and then, at the end of the macro, I did a copy/paste special to
get just the numbers (in Pivot Table format, but without the 'Pivot Table'
per se).

Anyway, I think I got a handle on it now. I had some blank rows in another
spreadsheet that I imported, and I guess something got lost during the
Transferspreadsheet process from Excel to Access. As I understand it, Access
doesn't like lots of blank rows. I used an Excel macro to delete all blank
rows. Also, in the Access Table, I changed the Data Type to Number and Field
Size was changed to Currency. Finally, in the Excel sheets, I converted
everything to Number format. Even though there is some text in there,
EVERYTHING on the Excel sheet is formatted as a number. The text always
imported fine...those numbers were importing correct for the most part, but
some were pretty screwed up. Finally, for anyone who someday encounters a
similar issue, and sees this post, make sure you have only numbers in a
column or only text in a column. In Column C, for instance, don't have this
in Excel: 23, 25, 34, fourty-nine, 56, 31. That was NOT my problem, but
during my research of this problem, I found this listed as a good idea of
what NOT to do.

Hope that helps others...


Ryan---

--
RyGuy


Jeff Boyce said:
Are you importing the pivot table or the underlying raw data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

ryguy7272 said:
I have a Pivot Table in Excel and I want to import the data into Access. I
run this code:
'etc...
DoCmd.RunSQL ("DELETE * FROM [Bookings - Pipe by Advertiser];")
DoCmd.TransferSpreadsheet acImport, 8, "Bookings - Pipe by Advertiser",
"C:\Documents and Settings\rs\Desktop\Forecasting\Up To Access\RVP
Revenue-Pipe.xls", True, "Pivot Sheet!A5:N2000"
'etc...
The names comes in fine, but the numbers sometimes appear random.
Sometimes
the numbers are accurate and sometimes they are not (like off by 10k, and
other random amounts). The table is set up as data type Text and Number.
Does number have to be some other data type? What could cause this?

Please help.

Thanks,
Ryan---
Click to expand...
Click to expand...
 
Back
Top