Hi everyone,
I have a relatively simple question (but still do not know how to do this!!).
I have a VBA macro to import a text file into a woksheet using powerquery. Everything is working fine if I put the exact path of the file in the code, for example :
SourceFormula = "let Source = Csv.Document(File.Contents(""C:\Users\Mike\Desktop\FEC29022020.txt""),[Delimiter=""#(tab)"", Columns=18, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""JournalCode"", ty" & _
"pe text}, {""JournalLib"", type text}, {""EcritureNum"", Int64.Type}, {""EcritureDate"", type date}, {""CompteNum"", Int64.Type}, {""CompteLib"", type text}, {""CompAuxNum"", type text}, {""CompAuxLib"", type text}, {""PieceRef"", type text}, {""PieceDate"", Int64.Type}, {""EcritureLib"", type text}, {""Debit"", type number}, {""Credit"", type number}, {""EcritureLe" & _
"t"", type text}, {""DateLet"", type text}, {""ValidDate"", Int64.Type}, {""Montantdevise"", type text}, {""Idevise"", type text}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""JournalCode"", ""EcritureNum"", ""CompAuxNum"", ""PieceRef"", ""PieceDate"", ""EcritureLet"", ""DateLet"", ""ValidDate"", ""Montantdevise"", ""Idevise""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""" & _
"Removed Columns"""
Now, what I would like to do is to open a dialog box, choose the .txt file from anywhere in my PC, and pass the full path into the code. I am trying to use this : fname = Application.GetOpenFilename("Text Files (*.txt), *.txt"), and then insert "fname" into the previous big formula. However, this doesn't work as the source formula has just to be a string.
Any idea would be of great help. Thank you!
Mike
I have a relatively simple question (but still do not know how to do this!!).
I have a VBA macro to import a text file into a woksheet using powerquery. Everything is working fine if I put the exact path of the file in the code, for example :
SourceFormula = "let Source = Csv.Document(File.Contents(""C:\Users\Mike\Desktop\FEC29022020.txt""),[Delimiter=""#(tab)"", Columns=18, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""JournalCode"", ty" & _
"pe text}, {""JournalLib"", type text}, {""EcritureNum"", Int64.Type}, {""EcritureDate"", type date}, {""CompteNum"", Int64.Type}, {""CompteLib"", type text}, {""CompAuxNum"", type text}, {""CompAuxLib"", type text}, {""PieceRef"", type text}, {""PieceDate"", Int64.Type}, {""EcritureLib"", type text}, {""Debit"", type number}, {""Credit"", type number}, {""EcritureLe" & _
"t"", type text}, {""DateLet"", type text}, {""ValidDate"", Int64.Type}, {""Montantdevise"", type text}, {""Idevise"", type text}})," & Chr(13) & "" & Chr(10) & " #""Removed Columns"" = Table.RemoveColumns(#""Changed Type"",{""JournalCode"", ""EcritureNum"", ""CompAuxNum"", ""PieceRef"", ""PieceDate"", ""EcritureLet"", ""DateLet"", ""ValidDate"", ""Montantdevise"", ""Idevise""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""" & _
"Removed Columns"""
Now, what I would like to do is to open a dialog box, choose the .txt file from anywhere in my PC, and pass the full path into the code. I am trying to use this : fname = Application.GetOpenFilename("Text Files (*.txt), *.txt"), and then insert "fname" into the previous big formula. However, this doesn't work as the source formula has just to be a string.
Any idea would be of great help. Thank you!
Mike