[AccessD] OT: Excel 97 Macro

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.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Terri Jarus
  Sent: Monday, March 24, 2003 9:09 AM
  To: accessd at databaseadvisors.com
  Subject: RE: Re: [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


----------------------------------------------------------------------------
----

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/14ce20f1/attachment-0001.html>