Import CSV File VBA

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
Option Explicit<o:p></o:p>
<o:p> </o:p>
Function import1()<o:p></o:p>
Dim ws As Excel.Application<o:p></o:p>
Dim cnn As ADODB.Connection<o:p></o:p>
Dim rst As ADODB.Recordset<o:p></o:p>
<o:p> </o:p>
Set cnn = CurrentProject.Connection<o:p></o:p>
Set rst = New ADODB.Recordset<o:p></o:p>
Set ws = CreateObject("Excel.Application")<o:p></o:p>
ws.Workbooks.Open "F:\Data\tblPeeps.csv"<o:p></o:p>
rst.Open "tblPeeps", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect<o:p></o:p>
ws.Sheets("tblpeeps").Select<o:p></o:p>
ws.Range("A2").Select<o:p></o:p>
Do Until ws.ActiveCell.Value = ""<o:p></o:p>
With rst<o:p></o:p>
.AddNew<o:p></o:p>
.Fields("Title").Value = ws.ActiveCell.Offset(0, 1).Value<o:p></o:p>
.Fields("FirstName").Value = ws.ActiveCell.Offset(0, 2).Value<o:p></o:p>
.Fields("LastName").Value = ws.ActiveCell.Offset(0, 3).Value<o:p></o:p>
.Fields("JobTitle").Value = ws.ActiveCell.Offset(0, 4).Value<o:p></o:p>
.Fields("Company").Value = ws.ActiveCell.Offset(0, 5).Value<o:p></o:p>
.Update<o:p></o:p>
End With<o:p></o:p>
ws.ActiveCell.Offset(1, 0).Select<o:p></o:p>
Loop<o:p></o:p>
MsgBox "Records have been added"<o:p></o:p>
<o:p> </o:p>
End Function

Thank you
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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

New Member
Joined
Jan 9, 2020
Messages
6
Office Version
2010
Platform
Windows
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
 

Forum statistics

Threads
1,082,348
Messages
5,364,858
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top