编辑“文本连接".路径 [英] Editing "text connection" paths

问题描述

我有一个Excel文件，可通过连接"菜单从多个CSV文件中提取数据.我遇到的问题是我需要能够从VBA中更改CSV文件的路径.

I have an Excel file that pulls data from multiple CSV files via the "Connections" menu. The problem I'm running into is I need to be able to change the path to the CSV files from within VBA.

在重复Bing(几乎是一个不好的词)之后，我遇到了一些解决方案，但是它们涉及的是SQL连接而不是文本连接.由于文件是CSV格式，因此Excel使其成为文本连接，因此没有ODBC连接字符串可修改(尝试从VBA对其进行修改时出现错误).我也没有仔细研究过MSDN文档.

After repeated Binging (that's almost a bad word) I came across some solutions, but they involve an SQL connection rather than a text connection. Since the files are CSV, Excel makes it a text connection, and thus there is no ODBC connection string to modify (I get an error when trying to modify it from VBA). I've also dug through the MSDN docs to no avail.

有人知道从VBA更改Excel中文本"连接路径的方法吗?

Does anyone know of a way to change a "Text" connection path in Excel, from within VBA?

另外，由于我是在这个话题上，是否可以有相对于文件的相对路径而不是完整的文件路径(例如"\ data \ some_report.csv"，而不是"c:\ somedir \ data \ some_report.csv)?

Also, since I'm on the topic, is it possible to have relative paths to files as opposed to the full file path (such as "\data\some_report.csv" rather than "c:\somedir\data\some_report.csv")?

推荐答案

您提到的...

我遇到了一些解决方案，但是它们涉及的是SQL连接而不是文本连接.

I came across some solutions, but they involve an SQL connection rather than a text connection.

所以使用.TextConnection.Connection代替.ODBCConnection.Connection:)

这是一个简单的例子.请对其进行适当的修改.

Here is a quick example. Please amend it as applicable.

Sub Sample()
    Dim Conn As Variant
    Dim ConString As String
    Dim oldPath As String, NewPath As String

    NewPath = "C:\MyPath.Csv"

    Set Conn = ActiveWorkbook.Connections.Item(1)

    Debug.Print Conn.TextConnection.Connection
    '==> TEXT;C:\Users\Siddharth\Desktop\Delete Later\Output.csv

    ConString = Conn.TextConnection.Connection

    oldPath = Split(ConString, ";")(1)

    ConString = Replace(ConString, oldPath, NewPath)

    Conn.TextConnection.Connection = ConString
End Sub

这篇关于编辑“文本连接".路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！