<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1141" name=GENERATOR></HEAD>
<BODY style="MARGIN-TOP: 2px; FONT: 10pt MS Sans Serif; MARGIN-LEFT: 2px">
<DIV><SPAN class=510542914-24032003>Hmm, that's a good point. I built code
that does the whole column and it takes only a small part of a second to do 1200
cells, so it isn't a big deal, but only doing the first would be more
efficient.</SPAN></DIV>
<DIV> </DIV>
<P>John W. Colby<BR>Colby Consulting<BR>www.ColbyConsulting.com </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT
face=Tahoma>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Terri
Jarus<BR><B>Sent:</B> Monday, March 24, 2003 9:09 AM<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> RE: Re: [AccessD] OT: Excel 97
Macro<BR><BR></FONT></DIV>
<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></BLOCKQUOTE>
<DIV>
<HR>
</DIV>
<DIV>Is email taking over your day? Manage your time with eMailBoss. Try it
free! <A
href="http://www.eMailBoss.com">http://www.eMailBoss.com</A></DIV></BODY></HTML>