Run-time error '-2147024809 (80070057)': The query name contains invalid characters

[Event2020]

The sub below calls a delimited text file and it is then supposed to pass the file name and file path to the Query in the appropiate places in the code below.


The Query then processes the data according to my needs before writing the result to a new worksheet.


The Query VBA was created by me carefully recording each step in a macro which works perfectly except the query only wants to open the file that was opened when the macro was record which is expected behaviour.


I have been trying to add a file picker dialog to the code that passes the chosen (picked) file name and the file path to the correct parts of the query that need that information. This way the query could be used over and over again.


Fine in theory, but when I try and import a file I get...
"Run-time error '-2147024809 (80070057)': The query name 'C:\Users\Username\Desktop\Test Data\Fake Test Data.1.txt' contains characters that are not valid.'"


I do not understand why this is happening as it is the same file and path that was used to record the Query.


I have tried to counter the error by declairing the characters that occur in the file name and path ":", "", and "." but that has changed nothing and Querys are a black art to me.


I would be grateful for any help.


I have attached a small Example Workbook and a small Fake/Dummy data.txt file.





Sub ImportTest()
Dim fd As FileDialog
Dim selectedFilePath As String
Dim selectedFileName As String
Dim backSlash As String
Dim colonString As String
Dim dot As String

' This creates a FileDialog object as a File Picker
Set fd = Application.FileDialog(msoFileDialogFilePicker)

' Filter to only allow text files to be selected
fd.Filters.Add "Text Files", "*.txt"

' Shows the File Picker dialog box
If fd.Show = -1 Then
selectedFilePath = fd.SelectedItems(1) ' Gets the selected file path
Else
Exit Sub ' User cancelled, exit the sub
End If

ActiveWorkbook.Queries.Add Name:=selectedFilePath, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\Username\Desktop\Test Data\Fake Test Data.1.txt""),[Delimiter="":"", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}})," & Chr(13) & "" & Chr(10) & " #""Added Index"" = Table.AddIndexColumn(#""Changed Type"", """ & _
"Index"", 1, 1, Int64.Type)," & Chr(13) & "" & Chr(10) & " #""Filtered Rows"" = Table.SelectRows(#""Added Index"", each ([Column1] = ""Company Name"" or [Column1] = ""Complete name"" or [Column1] = ""Credit Card #"" or [Column1] = ""Credit Card Type"" or [Column1] = ""Currency"" or [Column1] = ""email""))," & Chr(13) & "" & Chr(10) & " #""Pivoted Column"" = Table.Pivot(#""Filtered Rows"", List.Distinct(#""Filtered R" & _
"ows""[Column1]), ""Column1"", ""Column2"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Pivoted Column"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Fake Test Data 1"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Fake Test Data 1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Fake_Test_Data_1"
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects("Fake_Test_Data_1").Unlist
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Application.CommandBars("Queries and Connections").Visible = False
End Sub

[Paul_Hossler]

I'm not very good with queries, but I believe that it did not like the spaces or dots or back slashes in 'selectedFilePath' as the Name

https://support.microsoft.com/en-us/office/power-query-specifications-and-limits-in-excel-5fb2807c-1b16-4257-aa5b-6793f051a9f4




Query name length
80 characters



Invalid characters in a query name
Double quotes (“), periods (.), leading or trailing whitespaces





ALso to get it to run farther, I changed the File.Contents




selectedFilePath = "C:\Users\Daddy\Downloads\Example\Fake Test Data1txt"

ActiveWorkbook.Queries.Add Name:=selectedFilePath, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\Daddy\Downloads\Test Data\Fake Test Data.1.txt"")


Just some thoughts

[Event2020]

ALso to get it to run farther, I changed the File.Contents






selectedFilePath = "C:\Users\Daddy\Downloads\Example\Fake Test Data1txt"


ActiveWorkbook.Queries.Add Name:=selectedFilePath, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\Daddy\Downloads\Test Data\Fake Test Data.1.txt"")



Just some thoughts


I looked at the above and I can not see anything that is different to what I am doing.


The thing is.


As I known hardly anything about Querys in VBA, I created the code by:


Setting a macro to record.


I then clicked of the Data tab > From Text/CSV and in the popup file picker I navigated to the "C:\Users\Username\Desktop\Test Data" and then clicked on "Fake Test Data.1.txt"
which was fine, and then I performed the actions I require in Power Query, then stopped the Macro recording.


Now here's the thing, and this is what I can not understand, the macro recorded the spaces or dots or back slashes in 'selectedFilePath' and the query was fine with it.
I can re-run the same query VBA again and again and it works and Excel does not give any errors for the the spaces or dots or back slashes.




What I am trying to achieve with my code is to replace "C:\Users\Username\Desktop\Test Data\Fake Test Data.1.txt"")" with what ever file I click on in the file picker.
I admit that my knowledge of VBA is none compared to the experts here but I can see no difference between the two approaches.


I just do not know how to do it.

[Event2020]

Just as a quick test I changed the test file name "Data.1.txt" to "Data 1.txt" to remove the dot from the file name and it makes no difference.

[Paul_Hossler]

No 'dots' in the Name, but you do need them in the Connection. I just didn't bother to show



selectedQueryName = Trim(Replace(selectedFilePath, ".", vbNullString)) ' <<<<<<<<<<<<<<<<<<<

On Error Resume Next
ActiveWorkbook.Queries.Add Name:=selectedQueryName, Formula:= _




This gets past the error line and seems to agree with the Name restrictions

Like I said, I'm not good with queries, so maybe someone else will see more

[Aflatoon]

Why would you want to use a full file path as a query name?

[Event2020]

Why would you want to use a full file path as a query name?


Hi Aflatoon

I dont but the query code seems to need it.

When I selected a file using the picker the path appeared in the query code.

As I recorded the first query as a macro, the file path became hard coded so, without knowing any different, I assume the path needed to be passed to the query
but as Querys are something I know very little about that is why I am asking here.

[Paul_Hossler]

I just used this to test, simple 1 word name and the location of file on my PC



ActiveWorkbook.Queries.Add Name:=selectedFilePath, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\Daddy\Downloads\Test Data\Fake Test Data.1.txt"")


and it seems to run OK

BTW, if the query already exists, it will error out so I think you need to delete it before the .Add

Edit:

When I recorded a macro, it got loading a txt file called "1.txt" so it looks like the default query name is just the file without the path or extension



ActiveWorkbook.Queries.Add Name:="1", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\Daddy\Desktop\1.txt""),[Delimiter="","", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type text}, {""Column5"", type text}, {""Column6"", type text}, " & _
"{""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""

[Aflatoon]

I dont but the query code seems to need it.

It needs it here:


File.Contents(""C:\Users\Username\Desktop\Test Data\Fake Test Data.1.txt"")


but it definitely does not need it for the name of the query! ;)