Converting Excel file

  • Thread starter Thread starter CLR
  • Start date Start date
C

CLR

Hi All..............
I am trying to bring an Excel file into ACCESS. I have a specific named
range on one Excel sheet and the top row of the range is the HEADERs and I
follow the steps and all seems to work ok except that certain cells
(randomly spaced through out the range, sometimes in groups) come through as
#Num! instead of the value that was in the Excel cell.............I
replaced the question marks with blanks and that solved some of the
problems, but many numbers formatted as text give me the #Num!, while others
come through just fine..............

What's wrong please?........

TIA
Vaya con Dios,
Chuck, CABGx3
 
Jet database engine does not look at all the data in the EXCEL spreadsheet's
columns, default is the first 8 or 25 rows (depending upon your registry
settings).

There is no way to tell Jet the specific format, so you will either need to
move some rows with nonnumeric data in the text columns to the first or
second row, or else preface all cells in the text-formatted column with '
characters so that Jet will know those values are text and not numeric.
 
Thanks for the answer Ken...........

I guess that sort of explains it, but in my opinion it kind of negates the
value of the procedure if ACCESS goes ahead with the process and don't even
mention this situation and makes you think your data is converted, and when
you check it, it is NOT!.............I managed to get around the problem tho,
by working the other way around. I converted the RANGE to ACCESS while still
in EXCEL and it worked fine the first time.....all data was transferred.

Thanks again for the response, at least I know whats happening now.

Vaya con Dios,
Chuck, CABGx3
 
Yes, allowing a user to set the formats for an EXCEL sheet on link or import
would be a great improvement.... we've suggested this to MS, so we'll have
to see if it shows up in ACCESS 12 version that is now in development.
 
Hi Ken and Chuck,

Jet database engine does not look at all the data in the EXCEL spreadsheet's
columns, default is the first 8 or 25 rows (depending upon your registry
settings).
Click to expand...
There is no way to tell Jet the specific format, so you will either need to
move some rows with nonnumeric data in the text columns to the first or
second row, or else preface all cells in the text-formatted column with '
characters so that Jet will know those values are text and not numeric.
Click to expand...

I recently discovered (or rather had it firmly pointed out to me) that
in fact it is possible to make Jet scan all the rows of data, and even
to exert some control over how it imports columns of mixed data types.
See http://www.dicks-blog.com/excel/2004/06/external_data_m.html for the
gory details.
 
Yes, and hopefully the next version will be a lot more user-friendly. I
passed the Office 2k Master MOUS test a few years back, but at present, am
very uncomfortable trying to do things in ACCESS. Yet things come very easy
to me in EXCEL. I try to do everything in EXCEL when possible, resorting to
ACCESS only when the client absolutely requires. But I don't for the life
of me know why they "require", beccause very few of their employees can do
"anything" in ACCESS (even though they have attended classes), but most are
able to muddle through EXCEL. Indeed, improving the User-friendliness
would be a big asset to ACCESS.

Thanks again for your help, Ken

Vaya con Dios,
Chuck, CABGx3
 
John Nurick said:
I recently discovered (or rather had it firmly pointed out to me) that
in fact it is possible to make Jet scan all the rows of data, and even
to exert some control over how it imports columns of mixed data types.
See http://www.dicks-blog.com/excel/2004/06/external_data_m.html for the
gory details.
Click to expand...

As author of those gory details (apologies for being firm <g>), I
should point out it relates to querying Excel data directly and will
apply to an import because AFAIK a query is executed under the covers
when importing. However, I understand the OP is querying an Excel via
a linked table, which *appears* to handle Excel data types slightly
differently. See this recent thread:

http://groups.google.com/[email protected]

This needs further investigation, which is a bit tricky for me because
I don't have MS Access installed on my new machine :-(

BTW the blog has moved:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Jamie.

--
 
Thanks John and Jamie for your responses, but those details are "much" too
technical for my humble self. I just took the easy way out and let EXCEL do
the conversion for me..........that worked fine to solve my immediate
problem.

For my own purposes, I only want to use ACCESS as a tool to accomplish a job
and satisfy a client that they have the data in ACCESS, not to take it to
raise and learn what makes it tick. As I said before, ACCESS needs "much"
more User-friendliness before I could ever cozy-up to it. So also even "my"
EXCEL could use some help. I'm totally in to EXCEL and can see where it can
do almost anything, and yet it's amazing to see how many people in the
workforce that have to use it everyday, know absolutely nothing about it's
higher capabilities. THAT is where Microsoft could make the biggest
inroads, is just making the programs easier to use and make it easier for
people to see just how powerful they really are. I talk with Secretaries
every day that have taken a company sponsored class in ACCESS, yet they have
no clue as to how to start to create a simple table.

"Thanks again to everybody for your kind help with my problem", Chuck said,
as he stepped down off his soapbox.....(sorry).


Vaya con Dios,
Chuck, CABGx3
 
Back
Top