The Shy Retirer
New Member
- Joined
- Jan 11, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
I've got a large excel model and I start off by importing the data from a csv file via VBA. As some of my data has leading 0's that I don't want to lose and I need to retain the formatting on certain fields, I've used power query and VBA to come up with the following code that imports my data and works in the way I want it to work. I won't pretend to fully understand all that is happening but I've basically used as much google as I can find to convert the query to VBA and get this far. Note that I've edited below to make it easier to read since I have 59 columns.
This query does what I want it to do but I now want to apply a filter on one of the columns and only import what is left. I've noticed that I can filter the data in the Power Query and I'm struggling to replicate the amendment in VBA. This is the new Power Query. Again, I've deleted the majority of columns in the editor to make it easier to read, hopefully.
I've had a go at updating my original code above for the Filtered Rows section as per the following but its falling over unfortunately. Happy to admit that I'm not very good at VBA so I've probably got this completely wrong and its not just my syntax. Any help to correct the following code would be greatly appreciated.
VBA Code:
ActiveWorkbook.Queries.Add Name:="New_Data1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & _
" Filepath1 = Excel.CurrentWorkbook(){[Name=""Filepath_PolExtract""]}[Content]{0}[Column1]," & Chr(13) & "" & Chr(10) & _
" Source = Csv.Document(File.Contents(Filepath1),[Delimiter="","", Columns=59, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _
" #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _
" #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""PolicyNumber"", type text}, " & _
"{""Stream"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
wksht3.Activate
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""New_Data1"";Extended Properties=""""" _
, Destination:=Range("A1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [New_Data1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "New_Data1"
.Refresh BackgroundQuery:=False
End WithThis query does what I want it to do but I now want to apply a filter on one of the columns and only import what is left. I've noticed that I can filter the data in the Power Query and I'm struggling to replicate the amendment in VBA. This is the new Power Query. Again, I've deleted the majority of columns in the editor to make it easier to read, hopefully.
Power Query:
let
Source = Csv.Document(File.Contents("L:\Policy.csv"),[Delimiter=",", Columns=59, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"PolicyNumber", type text}, {"Stream", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Stream] = "A - B"))
in
#"Filtered Rows"I've had a go at updating my original code above for the Filtered Rows section as per the following but its falling over unfortunately. Happy to admit that I'm not very good at VBA so I've probably got this completely wrong and its not just my syntax. Any help to correct the following code would be greatly appreciated.
VBA Code:
ActiveWorkbook.Queries.Add Name:="New_Data1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & _
" Filepath1 = Excel.CurrentWorkbook(){[Name=""Filepath_PolExtract""]}[Content]{0}[Column1]," & Chr(13) & "" & Chr(10) & _
" Source = Csv.Document(File.Contents(Filepath1),[Delimiter="","", Columns=59, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _
" #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _
" #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""PolicyNumber"", type text}, " & _
"{""Stream"", type text}})" & Chr(13) & "" & Chr(10) & _
" #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", each ([""Stream""] = ""A - B""))" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Filtered Rows"""
