Number field in a Link Excel File, Please Help

  • Thread starter Thread starter Sarah Wilouhby
  • Start date Start date
S

Sarah Wilouhby

Hi Guys

I have a tricky situation and would appreciate an advise
on this.

My situation

I have an excel file linked to an Access database as a
table. The excel file has a column which carries a number(
2.1.3)for identifing a plan number. In the link table of
the Access Database this field is identified as a Number
field as a default and I get a error message as #Num.

I can not change this number field to text field in Access
Database as the table is a Link Table ( in fact Access
does not allow it).

I have also tried converting this field to a text field
in the Excel file (Format >> cell >> then in number
categorey tab >>> text) but this does not seem to work.

PLEASE HELP. where have I gone wrong. Is there a way
around this.

Thanks

SW
 
Hi Sarah,

I get round this by sticking an apostrophe ' in front of the values in
the Excel sheet, e.g.
'2.1.3
in place of
2.1.3
This forces Excel and Access/Jet to treat the value as text, but doesn't
show up in the worksheet. The little VBA procedures below will add or
remove the apostrophes to/from selected cells:


Sub AddApostrophes() 'Numeric values only
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub AddApostrophesAll() 'All values including 1.2.3
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = "'" & C.Formula
Next
End Sub



Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

Convert Text to Numbers in Microsoft Excel 2002
http://support.microsoft.com/default.aspx?scid=kb;en-us;291047

How to Convert Text to Numbers
http://support.microsoft.com/default.aspx?scid=kb;en-us;181298
 
Thanks a heap John. This is exactly what I was after.

One little question. I am new to VBA. how would I specify
the range. I mean where would I specify the column that
contain these particular values.

regards

SW
-----Original Message-----
Hi Sarah,

I get round this by sticking an apostrophe ' in front of the values in
the Excel sheet, e.g.
'2.1.3
in place of
2.1.3
This forces Excel and Access/Jet to treat the value as text, but doesn't
show up in the worksheet. The little VBA procedures below will add or
remove the apostrophes to/from selected cells:


Sub AddApostrophes() 'Numeric values only
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub AddApostrophesAll() 'All values including 1.2.3
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = "'" & C.Formula
Next
End Sub



Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

Convert Text to Numbers in Microsoft Excel 2002
http://support.microsoft.com/default.aspx?scid=kb;en- us;291047

How to Convert Text to Numbers
http://support.microsoft.com/default.aspx?scid=kb;en- us;181298
Hi Guys

I have a tricky situation and would appreciate an advise
on this.

My situation

I have an excel file linked to an Access database as a
table. The excel file has a column which carries a number (
2.1.3)for identifing a plan number. In the link table of
the Access Database this field is identified as a Number
field as a default and I get a error message as #Num.

I can not change this number field to text field in Access
Database as the table is a Link Table ( in fact Access
does not allow it).

I have also tried converting this field to a text field
in the Excel file (Format >> cell >> then in number
categorey tab >>> text) but this does not seem to work.

PLEASE HELP. where have I gone wrong. Is there a way
around this.

Thanks

SW
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Sarah,

The little procedures I posted work on whatever cells are selected. To
make them work on a whole column, it would be something like

Sub AddApostrophesAllToColumn(TheColumn As Long)
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(.Columns(TheColumn), _
.UsedRange).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub




Thanks a heap John. This is exactly what I was after.

One little question. I am new to VBA. how would I specify
the range. I mean where would I specify the column that
contain these particular values.

regards

SW
-----Original Message-----
Hi Sarah,

I get round this by sticking an apostrophe ' in front of the values in
the Excel sheet, e.g.
'2.1.3
in place of
2.1.3
This forces Excel and Access/Jet to treat the value as text, but doesn't
show up in the worksheet. The little VBA procedures below will add or
remove the apostrophes to/from selected cells:


Sub AddApostrophes() 'Numeric values only
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub AddApostrophesAll() 'All values including 1.2.3
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = "'" & C.Formula
Next
End Sub



Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

Convert Text to Numbers in Microsoft Excel 2002
http://support.microsoft.com/default.aspx?scid=kb;en- us;291047

How to Convert Text to Numbers
http://support.microsoft.com/default.aspx?scid=kb;en- us;181298
Hi Guys

I have a tricky situation and would appreciate an advise
on this.

My situation

I have an excel file linked to an Access database as a
table. The excel file has a column which carries a number (
2.1.3)for identifing a plan number. In the link table of
the Access Database this field is identified as a Number
field as a default and I get a error message as #Num.

I can not change this number field to text field in Access
Database as the table is a Link Table ( in fact Access
does not allow it).

I have also tried converting this field to a text field
in the Excel file (Format >> cell >> then in number
categorey tab >>> text) but this does not seem to work.

PLEASE HELP. where have I gone wrong. Is there a way
around this.

Thanks

SW
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
John Nurick said:
The little procedures I posted work on whatever cells are selected. To
make them work on a whole column, it would be something like

Sub AddApostrophesAllToColumn(TheColumn As Long)
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(.Columns(TheColumn), _
.UsedRange).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub
Click to expand...

Tested (in an Excel workbook) with the following code:

Sub Main()
Dim ws As Excel.Worksheet
Set ws = ThisWorkbook.Worksheets.Add
With ws
.Range("A1").Formula = "1"
.Range("A2").Formula = "'2"
.Range("A3").Formula = "=3"
.Range("A4").Formula = "=""4"""
End With

ws.Activate
AddApostrophesAllToColumn 1

End Sub

AddApostrophesAllToColumn didn't handle the formula functions as it
should. May I suggest:

Sub AddApostrophesAllToColumn( _
ByVal TheColumn As Long _
)
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(.Columns(TheColumn), _
.UsedRange).SpecialCells(xlCellTypeConstants).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub

Jamie.

--
 
AddApostrophesAllToColumn didn't handle the formula functions as it
should. May I suggest:

Sub AddApostrophesAllToColumn( _
ByVal TheColumn As Long _
)
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(.Columns(TheColumn), _
.UsedRange).SpecialCells(xlCellTypeConstants).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub
Click to expand...

Thank you.

BTW, beside clarity and style, is there a pressing reason for the ByVal?
 
John Nurick said:
beside clarity and style, is there a pressing reason for the ByVal?
Click to expand...

I guess I'd have to turn that around and say, apart from one less word
to type, is there a reason for the implicit ByRef <g>?

To be honest, I did it automatically and inadvertently! I've recently
taken on a code base where implicit ByRef has been used for all
arguments and it's really frustrating i.e. there is no clarity about
which parameters *need* to be ByRef. I must have been in 'conversion'
mode at the time.

Jamie.

--
 
I guess I'd have to turn that around and say, apart from one less word
to type, is there a reason for the implicit ByRef <g>?

To be honest, I did it automatically and inadvertently! I've recently
taken on a code base where implicit ByRef has been used for all
arguments and it's really frustrating i.e. there is no clarity about
which parameters *need* to be ByRef. I must have been in 'conversion'
mode at the time.
Click to expand...

Since switching from Pascal to VB[A] and Perl I've come to think in
terms of which parameters *need* to be ByVal. Usually the answer is
"none": if I need a local copy I declare a variable and assign it.
 
John Nurick said:
I guess I'd have to turn that around and say, apart from one less word
to type, is there a reason for the implicit ByRef <g>?
Click to expand...
Since switching from Pascal to VB[A] and Perl I've come to think in
terms of which parameters *need* to be ByVal. Usually the answer is
"none": if I need a local copy I declare a variable and assign it.
Click to expand...

Thanks John. It's always good to get a new perspective.

Jamie.

--
 
Back
Top