Handling mixed data types in fields programmatically

[sap1958]

Here is my code. It transfers multiple excel sheets into one access table called invoice. Problem is sme of the excel tables have a mix of character and numeric data in the same field (ie ID_Num,Prod_ID). These are supposed to be character fields. Example data is 1232 2321a 2233ee. How can I address this using the code I have below programmatically instead of sifting through 90 excel spreadsheets and manually changing the data types to character.

Sub transferMult()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = True

' Real path of the excel file location
strPath = "C:\pcsas_export_files\Quest\"

' Assign a tablename
strTable = "invoice"

'The actual file directory that will reference the set of files to be imported
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

strFile = Dir()
Loop

End Sub

 

[SkipVought]

hi,

FIX the data in the source table.

Make ALL values CHARACTER. This can NOT be done using a number format. You must actually change the value in the cell. Prefixing an APOSTROPHY, makes numeric values a 'label'

dim r as range
'converts every number to a string in the COLUMN for any ActiveCell...
with ActiveCell
  for each r in intersect(.entirecolumn, .currentregion) 
    with r
       if isnumeric(.value) then
         .value = "'" & .value
       end if
    end with
  next
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}