[AccessD] OT: Excel 97 Macro

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 --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 3544 bytes
Desc: not available
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030321/c5c56611/attachment-0001.bin>