ADODB Recorset中带有特殊字符的文件名
此以下代码错误是因为CSV文件名（COTPMS1_20220701.TXT_0107202_01H15M20S.CSV）还包含一个特殊字符（dot）。有没有办法逃脱这个特殊角色？我确实想避免在其他目录中复制和重命名该文件。
Sub testSpecialCharacter()
Dim cn As Object
Dim rsT As Object
Dim fullpath As String, _
ExtendedProp As String, _
query As String
Set cn = CreateObject("ADODB.Connection")
Set rsT = CreateObject("ADODB.Recordset")
fullpath = "C:\test\"
ExtendedProp = """text;HDR=NO"""
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0;"
.connectionstring = "Data Source=" & fullpath & ";Extended Properties=" & ExtendedProp
.CursorLocation = adUseClient
.Open
End With
query = "SELECT * FROM [COTPMS1_20220701.txt_01072022_01h15m20s.csv]"
rsT.Open query, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
rsT.Close
cn.Close
Set rsT = Nothing
Set cn = Nothing
End Sub
先感谢您
This following code bugs because the csv filename (COTPMS1_20220701.txt_01072022_01h15m20s.csv) contains a special character (a dot) in addition of the extension. Is there a way to escape this special character ? I would like indeed to avoid to copy and rename the file in other directory.
Sub testSpecialCharacter()
Dim cn As Object
Dim rsT As Object
Dim fullpath As String, _
ExtendedProp As String, _
query As String
Set cn = CreateObject("ADODB.Connection")
Set rsT = CreateObject("ADODB.Recordset")
fullpath = "C:\test\"
ExtendedProp = """text;HDR=NO"""
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0;"
.connectionstring = "Data Source=" & fullpath & ";Extended Properties=" & ExtendedProp
.CursorLocation = adUseClient
.Open
End With
query = "SELECT * FROM [COTPMS1_20220701.txt_01072022_01h15m20s.csv]"
rsT.Open query, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
rsT.Close
cn.Close
Set rsT = Nothing
Set cn = Nothing
End Sub
Thank you in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我遇到了与我的文件名称包括空间，破折号和下划线相同的问题。我试图通过反复试验来弄清楚这一点，但并没有得到很多运气。最后，我发现了Java帖子中的一些评论，并试图将其应用于VBA。它像魅力一样工作：基本上，您要做的就是将您的CSV文件名封装在方括号中，就像常见的查询一样。
例如：
希望这会有所帮助。
I ran into the very same issue as my file names included spaces, dashes and underscores. I tried to figure it out by trial and error but did not get much luck. In the end, I found some comments from a JAVA post and I tried to apply it to VBA. It worked like a charm: basically all you have to do is to encapsulate your csv file name in SQUARE BRACKETS, just like a common query.
For example:
Hope this helps.