[AccessD] OT: Excel 97 Macro

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.

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.

Point being - that maybe all you have to do is change the first record -
not all the records - if that helps.

>>> jcolby at colbyconsulting.com 03/21/03 11:23AM >>>
I have a similar requirement, but I want to do something specific to
selected ranges, from inside of Access using automation, to any excel
spreadsheet I choose.  Some background.

As I posted earlier this week, I am seeing problems where the data in
the
first cells of a given column are numeric, but lower down the cells
switch
to text.  The data is still numeric, i.e. the data is still simply a
number
- 2, 3.4 etc. but the FORMAT in the cell is text.  As a result, when
linked
to Access and displayed, Access decides that the column is numeric
because
of the first few cells at the top of the column, then can't figure out
what
to do with the cells that are actually text down below.  This is all
discussed in
http://support.microsoft.com/default.aspx?scid=kb;en-us;162539
as Hayden pointed out.

The "Fix" is to go into the spreadsheet, select the column of data, and
prepend a space to the beginning of each cell, which apparently causes
any
numeric data to turn into text.  Now, in the linked data inside of
Access,
because the entire column is a single type of data (text) it can be
displayed all the way down.  I can then use a cLng (or whatever is
necessary) to convert the data back to the data type needed.  

PITA, but this is MS after all ;-)

So, the code shown for doing this is:

Sub Addspace()

   Dim cell As Object

   For Each cell In Selection
      cell.Value = " " & cell.Value
      cell.Value = Right(cell.Value, Len(cell.Value) - 1)
   Next
   
End Sub

run as a macro inside of Excel.  Of course if this is to be generic, any
given spreadsheet will not have this macro inside of it so I will have
to
insert the macro.  Further I have to add code to select a given range,
then
run this code.

Several years ago, when I lived in Mexico, I did a bunch of formatting
of
Excel.  What I did in that case was to build a workbook in which I
created
my macros.  I then copied the workbook to a new name, imported the
worksheet(s) that needed formatting, and then ran the formatting macros.
 I
could do the same thing here but I would prefer to have code stored
inside
of Access, open the spreadsheet that needs this process performed on it,
insert a module with the code, select the area, and run the macro.

If this sounds like a major PITA to fix a bug in Excel / Access
interaction,
I couldn't agree more.  But we do what we have to do.  BTW, I have also
seen
dates with similar problems  A data column looks prefect inside of
Excel,
but when linked and viewed inside of Access, some dates are hosed.  The
problem is exactly the same, they are text.

Before I go off re-inventing the wheel, does anyone have code for doing
anything similar?  Pieces for doing parts of what I am trying to do?
Interest in working with me to jointly solve this problem?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: Friday, March 21, 2003 11:50 AM
To: accessd at databaseadvisors.com
Subject: Re: Re: [AccessD] OT: Excel 97 Macro


Terri, 

Try this then

Sub Macro1()
   Dim StartRow As Integer
   Dim FinishRow As Integer
   Dim MyRange As String

   StartRow = InputBox("Enter Start Row")
   FinishRow = InputBox("Enter Finish Row")

   MyRange = "A" & startrow & ":R" & finishrow

   Range(MyRange).Select
End Sub

This should do the job.....

Paul Hartland


----------------------------------------------------
Is email taking over your day?  Manage your time with eMailBoss.  
Try it free!  http://www.eMailBoss.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030324/413873aa/attachment-0001.html>