Import CSV (text file) into Excel - without PowerQuery

bigck2

Board Regular
Joined
Feb 20, 2014
Messages
146
I'm wanting to import a CSV file into an excel workbook, and previously had just recorded this operation and tweaked the code generated from the recorder. Now with the PowerQuery / Get Data features in excel it wants to create a query connection which seems like overkill for my needs.

Is it still possible to just import a CSV file into excel and not create a query connection?

Here is the code that was recorded:
Code:
Sub Macro1()


    ActiveWorkbook.Queries.Add Name:="Service Requests - v1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\Chris\Desktop\TEST - Weekly Leasing Report\Service Requests - v1.csv""),[Delimiter=""#(tab)"", Columns=7, Encoding=1200, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Heade" & _
        "rs"",{{""Property Name"", type text}, {""Service Requests Created"", Int64.Type}, {""Service Requests Cancelled"", Int64.Type}, {""Service Request Completed"", Int64.Type}, {""Service Request Created - Prior Week"", Int64.Type}, {""Service Requests Outstanding"", Int64.Type}, {""Service Requests Percent Complete"", type number}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    
    ActiveWorkbook.Worksheets.Add
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Service Requests - v1"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Service Requests - v1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Service_Requests___v1"
        .Refresh BackgroundQuery:=False
    End With
    
    Range("A2").Select
    
End Sub

I'm not opposed to learning the new technology but I'd prefer to keep this process as simple and fast as possible. Please let me know if you have a good resource that explains this VBA process.

IF I have to use the query connection, I'd prefer to disable that connection after the data is imported. I did that too, but it didn't record in the macro.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,147,748
Messages
5,742,976
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top