I successfully saved my excel file into a csv using vba. (because you cant have import specs on an excel file) But my problem is is that when i import the resulting csv file into my database using import specs, the information is all jibberish and not the data that it was when it was an excel file. Here is my code:


Private Sub ImportContract_Click()
Dim db As Database
Set db = CurrentDb
Dim Sql As String
Dim FileName As String
Dim CsvFile As String
Dim xlApp As Excel.Application, xlBook As Excel.Workbook
Dim TemplateLoc As String
Const InitialPath = "R:\Pricing\Access\SAPEnglishTool\"

db.Execute ("DELETE tblSAPImport.* FROM tblSAPImport;")
db.Execute ("DELETE tblContractImport.* FROM tblContractImport;")
db.Execute ("DELETE [English Version Template].* FROM [English Version Template];")

TemplateLoc = "R:\PRICING\Access\SAPEnglishTool\ZCDT Test.xlsx"
CsvFile = "R:\PRICING\Access\SAPEnglishTool\ZCDT Test.csv"

Set xlBook = GetObject(TemplateLoc)
Set xlApp = xlBook.Parent
xlBook.SaveAs CsvFile

Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

FileName = adhCommonFileOpenSave(, "CSV Files (*.csv)|" & "*.csv", , , , "Import CSV File", InitialPath, , False)
If Right(FileName, 3) <> "csv" Then
GoTo NotTxtFile
End If
If FileName <> "" Then
DoCmd.TransferText acImportDelim, "SAPImportSpec", "tblSAPImport", FileName, True
End If