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:
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.
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.