Issue with Importing Data and Data Type Conversion

[SStrange]

I am having an issue with my VBA for this import. It works but, when I use this with certain files some of the records are not putting in one of the fields correctly and kicks out a data conversion error. the field is a mixed data type field. Basicly text. I think it is reading as numeric only though as it is kicking out the any alphabetic characters and not converting them. So far this has only happened on one file. As far as the excel file, the type is static in that field. Can someone help me amend the code to make it pull in as a specific type? As it comes in it loads into F1 through F13 and I would like those to be labelled anyway. The file does not come with the fields appropriately labelled. In total there are over 40 files that are loaded over varying number of records. The fields are always the same but they aren't in a true tabular form. None will have over 2000 records however. As an aside, the reason for the conversion is that when I try to import the file otherwise it gives an error and when I do the conversion to xlsx it works so... Either way, I appreciate the assistance.
Private Sub CtlPBVIMPRT_Click()
Dim filepath As String
Dim Fpath As String
Dim ExcelApp As Object
filepath = "C:\Users\Strange\Desktop\FSSCVP\FY2015\P10PBV.xls"
Fpath = "C:\Users\Strange\Desktop\FSSCVP\FY2015\XL13\P10PBV.xlsx"
If FileExist(Fpath) Then
DoCmd.TransferSpreadsheet acImport, , "PBV", Fpath, False, "A5:M2005"
MsgBox "Import Complete"
Else
Set ExcelApp = CreateObject("Excel.Application")
With ExcelApp
.Workbooks.Open filepath
.DisplayAlerts = False
.ActiveWorkbook.SaveAs Fpath, FileFormat:=51
.Visible = False
.ActiveWorkbook.Close False
End With
DoCmd.TransferSpreadsheet acImport, , "PBV", Fpath, False, "A5:M2005"
MsgBox "Import Complete"
End If
End Sub

 

[ranman256]

I must correct some workbooks.
my code opens the file, then sets the correct fieldnames. Range("A3").value = "State"

Sometimes, I must convert all records of 1 field and force them to text, because excel puts the #NUM error in the cell.

Sub Cvt2Txt()

with xl
.range("A2").select
While .ActiveCell.Value <> ""
   vTxt = .ActiveCell.Value
   If Left(vTxt, 1) <> "'" Then .ActiveCell.Value = "'" & vTxt
   .ActiveCell.Offset(1, 0).Select       'next row
Wend
end with
End Sub