transferring an excel file into a csv file using vba

[xfactor25]

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

[dilettante]

That isn't VB. I think you want the Office forum.

[incidentals]

Agreed should be in office

the op needs to put some examples of the initial file and the resultant csv

there is probably no need for any of it

will keep an eye open for it

here to help

[westconn1]

xlBook.SaveAs CsvFile

i assume this save an excel workbook file with a csv extension
you would need to specify the file type for a csv file as well as the filename for your saveAs