<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2719.2200" name=GENERATOR></HEAD>
<BODY style="MARGIN-TOP: 2px; FONT: 10pt MS Sans Serif; MARGIN-LEFT: 2px">
<DIV>Here's just some additional information - if the first record Access "sees"
has the first cell of the column as TEXT, then it treats all the rest as text
whether they are numeric or not. It all depends on that first
record.</DIV>
<DIV> </DIV>
<DIV>I have run across this as I import a lot of files into Access and find that
the field used for an Item ID could be text or numeric or mixed throughout the
file. I just move a record that has Text for the ItemID to the first
record and it all imports just fine.</DIV>
<DIV> </DIV>
<DIV>Point being - that maybe all you have to do is change the first record -
not all the records - if that helps.<BR><BR>>>>
jcolby@colbyconsulting.com 03/21/03 11:23AM >>><BR>I have a similar
requirement, but I want to do something specific to<BR>selected ranges, from
inside of Access using automation, to any excel<BR>spreadsheet I choose.
Some background.<BR><BR>As I posted earlier this week, I am seeing problems
where the data in the<BR>first cells of a given column are numeric, but lower
down the cells switch<BR>to text. The data is still numeric, i.e. the data
is still simply a number<BR>- 2, 3.4 etc. but the FORMAT in the cell is
text. As a result, when linked<BR>to Access and displayed, Access decides
that the column is numeric because<BR>of the first few cells at the top of the
column, then can't figure out what<BR>to do with the cells that are actually
text down below. This is all<BR>discussed in <A
href="http://support.microsoft.com/default.aspx?scid=kb;en-us;162539">http://support.microsoft.com/default.aspx?scid=kb;en-us;162539</A><BR>as
Hayden pointed out.<BR><BR>The "Fix" is to go into the spreadsheet, select the
column of data, and<BR>prepend a space to the beginning of each cell, which
apparently causes any<BR>numeric data to turn into text. Now, in the
linked data inside of Access,<BR>because the entire column is a single type of
data (text) it can be<BR>displayed all the way down. I can then use a cLng
(or whatever is<BR>necessary) to convert the data back to the data type
needed. <BR><BR>PITA, but this is MS after all ;-)<BR><BR>So, the code
shown for doing this is:<BR><BR>Sub Addspace()<BR><BR> Dim cell As
Object<BR><BR> For Each cell In
Selection<BR> cell.Value = " " &
cell.Value<BR> cell.Value = Right(cell.Value,
Len(cell.Value) - 1)<BR> Next<BR> <BR>End Sub<BR><BR>run
as a macro inside of Excel. Of course if this is to be generic,
any<BR>given spreadsheet will not have this macro inside of it so I will have
to<BR>insert the macro. Further I have to add code to select a given
range, then<BR>run this code.<BR><BR>Several years ago, when I lived in Mexico,
I did a bunch of formatting of<BR>Excel. What I did in that case was to
build a workbook in which I created<BR>my macros. I then copied the
workbook to a new name, imported the<BR>worksheet(s) that needed formatting, and
then ran the formatting macros. I<BR>could do the same thing here but I
would prefer to have code stored inside<BR>of Access, open the spreadsheet that
needs this process performed on it,<BR>insert a module with the code, select the
area, and run the macro.<BR><BR>If this sounds like a major PITA to fix a bug in
Excel / Access interaction,<BR>I couldn't agree more. But we do what we
have to do. BTW, I have also seen<BR>dates with similar problems A
data column looks prefect inside of Excel,<BR>but when linked and viewed inside
of Access, some dates are hosed. The<BR>problem is exactly the same, they
are text.<BR><BR>Before I go off re-inventing the wheel, does anyone have code
for doing<BR>anything similar? Pieces for doing parts of what I am trying
to do?<BR>Interest in working with me to jointly solve this problem?<BR><BR>John
W. Colby<BR>Colby Consulting<BR><A
href="http://www.ColbyConsulting.com">www.ColbyConsulting.com</A><BR><BR>-----Original
Message-----<BR>From: accessd-admin@databaseadvisors.com<BR>[<A
href="mailto:accessd-admin@databaseadvisors.com]On">mailto:accessd-admin@databaseadvisors.com]On</A>
Behalf Of<BR>paul.hartland@fsmail.net<BR>Sent: Friday, March 21, 2003 11:50
AM<BR>To: accessd@databaseadvisors.com<BR>Subject: Re: Re: [AccessD] OT: Excel
97 Macro<BR><BR><BR>Terri, <BR><BR>Try this then<BR><BR>Sub
Macro1()<BR> Dim StartRow As Integer<BR> Dim FinishRow
As Integer<BR> Dim MyRange As String<BR><BR> StartRow =
InputBox("Enter Start Row")<BR> FinishRow = InputBox("Enter Finish
Row")<BR><BR> MyRange = "A" & startrow & ":R" &
finishrow<BR><BR> Range(MyRange).Select<BR>End Sub<BR><BR>This
should do the job.....<BR><BR>Paul
Hartland<BR><BR><BR>----------------------------------------------------<BR>Is
email taking over your day? Manage your time with eMailBoss. <BR>Try
it free! <A
href="http://www.eMailBoss.com">http://www.eMailBoss.com</A><BR></DIV></BODY></HTML>