更新时间:2023-02-10 07:46:33
如果工作簿文件始终与CSV文件位于同一文件夹中,则可以使用ActiveWorkbook.Path方法设置相对路径.
If the work book file is always located in the same folder as the CSV file you can utilize the ActiveWorkbook.Path method to set a relative path.
假设csv文件名和连接名相同的示例:
Example assuming csv file name and connection name are the same:
Sub refreshMsgConnection()
Dim csvFileName As String
csvFileName = "msg_by_weeks.csv"
Dim filePath As String
filePath = ActiveWorkbook.path
Dim conString As String
conString = "TEXT;" & filePath & "\" & csvFileName
With ActiveWorkbook.Connections("msg_by_weeks").Ranges.Item(1).QueryTable
.connection = conString
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.TextFilePromptOnRefresh = False
.Refresh BackgroundQuery:=False
End With
End Sub
您可能必须根据配置使用某些设置,例如定界符等.如果遇到问题,请在以所需格式设置新连接时记录宏,然后将设置复制到此子目录中
You may have to play with some of the settings based on your configuration such as delimiters etc. If you're having trouble, record a macro while you setup a new connection with the desired format and then copy the settings in this sub.
如果找不到该文件,它将弹出一个文件选择框,供用户查找所需文件.
If the file is not found, it will pop up a file selection box for the user to locate the desired file.