Excel中CSV导入的相对路径 [英] Relative Paths within Excel for CSV imports

问题描述

我有一个Excel工作簿，可以从CSV文件自动更新工作表.这些从硬编码文件夹(c:\ temp \ premiumreports \ name_of_CSV_file.csv)中提取数据.

I have an Excel workbook that auto-updates the worksheets from CSV files. These pull in the data from a hard coded folder (c:\temp\premiumreports\name_of_CSV_file.csv).

这种方法迫使我每次都将所有内容放入c:\ temp \ premiumreports，或者如果我将文件发送给客户，他们必须创建相同的目录结构.

This method forces me to put everything into c:\temp\premiumreports everytime or if I send the file to a customer they have to create the same directory structure.

我想将xlsx文件与必要的CSV文件放入任何文件夹(c:\ report或用户的桌面)中，当我打开xlsx时，数据将自动导入.

I would like to place my xlsx file into any folder (c:\report or the user's desktop) with the necessary CSV files and when I open the xlsx the data is imported automatically.

当前工作簿连接

Current Workbook Connection

推荐答案

如果工作簿文件始终与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.

这篇关于Excel中CSV导入的相对路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！