Import CSV File VBA

[Trevor G]

Hi I am using code to import a csv file but receive a message box stating this is a SYLK file, but cannot load it, either the file has errors (which it doesn't 4 rows with 4 columns all text entries) or it is not a SYLK file format.

Can anyone help get ride of this message box

My code is shown here

Option Compare Database<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Option Explicit<o></o>
<o> </o>
Function import1()<o></o>
Dim ws As Excel.Application<o></o>
Dim cnn As ADODB.Connection<o></o>
Dim rst As ADODB.Recordset<o></o>
<o> </o>
Set cnn = CurrentProject.Connection<o></o>
Set rst = New ADODB.Recordset<o></o>
Set ws = CreateObject("Excel.Application")<o></o>
ws.Workbooks.Open "F:\Data\tblPeeps.csv"<o></o>
rst.Open "tblPeeps", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect<o></o>
ws.Sheets("tblpeeps").Select<o></o>
ws.Range("A2").Select<o></o>
Do Until ws.ActiveCell.Value = ""<o></o>
With rst<o></o>
.AddNew<o></o>
.Fields("Title").Value = ws.ActiveCell.Offset(0, 1).Value<o></o>
.Fields("FirstName").Value = ws.ActiveCell.Offset(0, 2).Value<o></o>
.Fields("LastName").Value = ws.ActiveCell.Offset(0, 3).Value<o></o>
.Fields("JobTitle").Value = ws.ActiveCell.Offset(0, 4).Value<o></o>
.Fields("Company").Value = ws.ActiveCell.Offset(0, 5).Value<o></o>
.Update<o></o>
End With<o></o>
ws.ActiveCell.Offset(1, 0).Select<o></o>
Loop<o></o>
MsgBox "Records have been added"<o></o>
<o> </o>
End Function

Thank you

 

[Trevor G]

I have sorted this issue, you can't use ID in capitals in the csv file, changed that to lowercase and it works a treat.

Thank you for all who have viewed this thread.

Trevor

 

[Sawcy1]

Hello, I know this is a really old post, but if anyone runs across it, here is the VBA solution:

I was using a macro to convert text files to XLSX and one of the text files had "ID" in the first two characters. EXCEL automatically labels files like this as SYLK files and throws popups asking what you want to do about it (I know, it is ridiculous, but true). I tried a suggestion to convert the file to UTF-8, but then my pipe delimiter was not recognized. After trying may different things, I finally found a very simple solution.

I hope this helps those of you who have been pulling your hair out over this!

Application.DisplayAlerts = False
Workbooks.Open Filename:=folderName & "\" & myfile, Format:=6, Delimiter:="|"
Application.DisplayAlerts = True

Be simply adding the DisplayAlerts statements, it powered right through the popups and handled my file correctly.

Annette