excel - 添加 PowerQuery 时如何使用字符串作为源
问题描述
我正在尝试将字符串添加filename
为添加 PowerQuery 连接的源,但由于某种原因 Excel 没有接收到字符串?有任何想法吗?如果我将完整路径添加为文本,则效果很好。
我的错误在线Csv.Document(File.Contents(filename)
Sub AddQuery()
Dim newdate As Range
Set newdate = Range("E2")
Do Until Dir("D:\Users\rosenberggitty2\Dropbox\Personal\Done\Options\" & newdate.Text & ".csv") <> vbNullString
newdate = newdate + 1
Worksheets(1).Columns(5).AutoFit
Loop
Dim filename As String
filename = "D:\Users\rosenberggitty2\Dropbox\Personal\Done\Options\" & newdate.Text & ".csv"
Dim myConnection As WorkbookConnection
Dim mFormula As String
mFormula = _
"let Source = Csv.Document(File.Contents(filename),null,""#(tab)"",null,1252) in Source"
query1 = ActiveWorkbook.Queries.Add(newdate, mFormula)
End Sub
解决方案
弄清楚了!需要有 3 组引号才能正确识别路径。File.Contents(""" & filename & """)
这是我更新的代码(包括对查询的一些更改)。
Sub AddQuery()
Dim newdate As Range
Set newdate = Range("E2")
Do Until Dir("D:\Users\rosenberggitty2\Dropbox\Personal\Done\Options\" & newdate.Text & ".csv") <> vbNullString
newdate = newdate + 1
Worksheets(1).Columns(5).AutoFit
Loop
Dim filename As String
filename = "D:\Users\rosenberggitty2\Dropbox\Personal\Done\Options\" & newdate.Text & ".csv"
ActiveWorkbook.Queries.Add Name:=newdate, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""" & filename & """),[Delimiter="","", Columns=26, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Change 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}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", type text}, {""Column19"", type te" & _
"xt}, {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", type text}, {""Column25"", type text}, {""Column26"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Change Type"""
End Sub
推荐阅读
- javascript - 增加一天格式日期(不是数字)javascript
- r - 如何使用 dummy_cols 拆分列
- embedded - 为什么ROM的闪存扇区在起始地址处较小?
- javascript - 获取 jquery 中的链接元素以在 $.fn.{FunctionName} 中使用
- bootstrap-4 - 与 Bootstrap 示例相比,Tempusdominus Datetimepicker 变形或不显示
- python-3.x - 如何使用 Pandas 加入可变长度的日期和时间
- javascript - 带有 useSelector 和 useDispatch 的 Redux Hooks - 为什么要使用它?
- java - 从 AWS ECS 集群获取标签返回空列表
- python-3.x - 无法使用 discord.py 向频道发送消息
- elixir - Ecto.Migrations.references/2 中的 on_delete 选项有什么作用?