Linked Excel Tables w/ std formatting not pulling in w/ same forma

[Penntellect]

The problem I'm trying to resolve has to do with formatting and tables linked
ot Excel files. I know that I can write queries to format all of the data
the way that I want it, but this would lead to a loss of data integrity and
redundancy within my database. Besides, this is a HUGE database, and I
don't need it to be any bigger than it has to be...

I would like to link row data which has been manually input into about 100
Excel files on a weekly basis. I have ensured that all of the 100 files have
the exact same column format as each other. The first column contains a
string of numbers, but is formatted in Excel as "text." When I attempt to
establish linked tables in Access 2000, the fields link as a variety of text
and numeric strings, depending on which table you review-but never is it
consistent from table to table.

At this point, I don't know what would be better: trying to figure out the
Visual Basic (i've actually had pretty good success with a minimal amount of
hassling our tech guys) code to import the worksheet into one central file,
and then importing it; or battling the issue of inconsistent formats in
Access. Am I just overlooking something simple? I'm not a newbie to Access
design, but this is an area where I have always had trouble.

 

[John Nurick]

This is an area where almost everyone has trouble. If you need to link
lots of files and are willing to get under the bonnet, see from
http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Another approach is to ensure that, in Excel, the numbers are prefixed
with apostrophes. This forces both Excel and Access to treat them with
text, though the apostrophes are not displayd in Excel or imported into
Access. This Excel macro will prefix an apostrophe to every numeric
value in the selected cells, simultaneously converting formulas to
values:

Sub AddApostrophesNumericToSelection()
Dim C As Excel.Range
For Each C In
Application.Selection.SpecialCells(xlCellTypeConstants).Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Value
End If
Next
End Sub

and this Excel VBA procedure will do the same for every used cell in a
column:

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.Value
Next
End With
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