Problem with custom formats

[bdoiron]

I have a client that is trying to export an excel
spreadsheet to an Access database The excel spreadshheet
has some custom formats to aid in the speed of data entry.
The problem is this. For Instance:

Cell A1 is formated Custom 5_??"/"32. When you go to that
cell and enter 8 .... 5 8/32 is displayed. However the
actual value in the cell is 8.
When I import this cell into Access I get 8. I actually
need 5 8/32 even if it is a text field. I tried all the
cut and paste and special paste options to no avail. Is
there any way to capture the literals in the format of the
cell and string them together with the value to get the
value I am looking for. I guess similar to drilling
backwards.

Any help would be deeply appreciated.

Thanks BD

 

[Ron Rosenfeld]

I have a client that is trying to export an excel
spreadsheet to an Access database The excel spreadshheet
has some custom formats to aid in the speed of data entry.
The problem is this. For Instance:

Cell A1 is formated Custom 5_??"/"32. When you go to that
cell and enter 8 .... 5 8/32 is displayed. However the
actual value in the cell is 8.
When I import this cell into Access I get 8. I actually
need 5 8/32 even if it is a text field. I tried all the
cut and paste and special paste options to no avail. Is
there any way to capture the literals in the format of the
cell and string them together with the value to get the
value I am looking for. I guess similar to drilling
backwards.

Any help would be deeply appreciated.

Thanks BD

In order to do what you want, unless you can do something within ACCESS, I
believe you will have to export it as a TEXT field with the proper TEXT in the
field.

That will require either running a macro (SUB) to change the contents of the
field, or setting up a helper column, with the formula

=TEXT(A1,"5_??\/\3\2")

Copy this down, then paste it over the original (being sure to back up the
original worksheet first.


--ron

 

[Guest]

-----Original Message-----


In order to do what you want, unless you can do something within ACCESS, I
believe you will have to export it as a TEXT field with the proper TEXT in the
field.

That will require either running a macro (SUB) to change the contents of the
field, or setting up a helper column, with the formula

=TEXT(A1,"5_??\/\3\2")

Copy this down, then paste it over the original (being sure to back up the
original worksheet first.


--ron
.
What I am working with is the results of drill pipe

inspection. This is a very short job however. A typical
inspection job would be 400 rows. Usually it is of the
same size with small variances. To help aid in the speed
of data entry they use these custom formats. For instance
one spreadsheet might be all 5" pipe some 6" 7" 8" so on.
I am trying to write an interface in Access and pull in
each pipe inspection job from the workstation as they
complete one. Therefore the formats will not be known at
the time I am importing it. Another words I don't want to
pre-format the sheet because I know the format by looking
at it. I want to be able to read the format and build the
proper format to give me the right number in the cell.
Hope this explains things. I really appreciate your effort

 

[Ron Rosenfeld]

What I am working with is the results of drill pipe
inspection. This is a very short job however. A typical
inspection job would be 400 rows. Usually it is of the
same size with small variances. To help aid in the speed
of data entry they use these custom formats. For instance
one spreadsheet might be all 5" pipe some 6" 7" 8" so on.
I am trying to write an interface in Access and pull in
each pipe inspection job from the workstation as they
complete one. Therefore the formats will not be known at
the time I am importing it. Another words I don't want to
pre-format the sheet because I know the format by looking
at it. I want to be able to read the format and build the
proper format to give me the right number in the cell.
Hope this explains things. I really appreciate your effort

Does the format in Access matter, or only the value?

In other words, if the entry is an '8', would you want Access to import this as
5 8/32 or would 5.25 be OK?

You could use a user form to input the data as the fractional numerator, and
then do the math to store the number as the correct value.

You could also write a macro to do the conversion after the entry has been
made, but somewhere the denominator will need to be defined. A conversion
macro could be fairly simple. A loop such as:

For each c in selection
c.value = c.text
next c

The unknown (to me) is the type of output you require for Access. If it must
be fractional with the same denominator as originally entered, then you'd have
to pull that out and convert the result to a text string as Excel will store a
numeric value as an ordinary number.



Soe
--ron

 

[Dave Peterson]

Can you run a macro before you import it to access?

This puts the value in the cell to its right:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
.Offset(0, 1).Value = .Text
.Offset(0, 1).NumberFormat = "General"
End If
End With
Next myCell
End With
End Sub

But you can get to the number format by using, er, .numberformat.

With myCell
If IsNumeric(.Value) Then
.Offset(0, 1).Value = Application.Text(.Value, .NumberFormat)
.Offset(0, 1).NumberFormat = "General"
End If
End With