Prevent Text to Integer conversion

[NickH]

I am using Office 2003

I have a text field called 'Version' in an Access database which I am
pulling into an Excel spreadsheet so that I can generate a list of
unique version numbers to populate a ComboBox.

I have no control over the content of the database and the Version
field could contain anything alphanumeric.

My problem is that if a version number happens to be, for instance,
"1.0" then the moment it is copied from the recordset to a cell it
becomes "1".

What's the simplest way to ensure the cell contents (and ultimately
the ComboBox contents) reflect exactly what is in the database?

Here is the bit of code that I'm currently using to get recordset
contents into a worksheet...

' Deposit recordset into first column of temp sheet
rs.MoveFirst
Do
With ActiveCell
.Value = rs.Fields(0).Value
.Offset(1, 0).Activate
End With
rs.MoveNext
Loop Until rs.EOF


NickH

 

[GB]

within your with, if you set the datatype I.e., string, number, etc. to the
format that you want, then you should be good. You may need to test to see
what type of data you have. For example the below recorded step for
formatting a number to have two decimal places is for just that, a number
with two decimal places. You could test first to see if the data you have is
a number only, then if so, you can determine the number of decimals by
finding the decimal within the string and using the length of the string and
the location of the decimal set the format of the box. The other case that I
see is that if it is not a number, and then you should come out fine between
the "database" and the user entry field. Excel VBA help identifies the
following checks that are possible:

ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)

Another option is to add on an apostrophe to the beginning of your text when
storing the data, and if there is an apostrophe in the text and it is at the
beginning when you pull it out to remove it before putting the data into the
text field. An apostrophe forces the data to stay as a text string (i.e.,
1.0 will appear as 1.0, but the if you were to edit the field by using say
F2, then it would look like '1.0)

Selection.NumberFormat = "0.00"

 

[NickH]

Top man GB,

I like the apostrophe option (kicking myself for not thinking of it).
I suspect it'll be the most efficient and won't slow the loop too
much. If I also use a counter instead of .Activate the user shouldn't
have to wait several seconds for the list to populate.

Many thanks,

NickH

 

[GB]

May have already developed your solution, but if not, if you activate the
first cell, that gets things moving, then you can use
activecell.offset([COUNTER], 0) instead of activecell and then disregard the
offset inside of the loop. I think that would work. I don't typically use
the offset, and have been finding more and more reason to use it. I like to
be able to immediately see the location when debugging and not have to
determine exactly where it is because of a previous item.

Another thing to consider is the maximum number of rows in Excel as it
compares to Access. I am not sure of those exact values, but I thought there
was a way to pull that number out of at least excel irregardless of the
version number. (If not this, then need to catch the error that results from
going beyond the maximum number of rows when using the offset option.)