数据连接的相对路径？ [英] Relative paths for data connections?

问题描述

Office 2013。

Office 2013.

我正在向外部用户分发Excel工作簿和Access ACCDB文件。

I am distributing an Excel workbook and an Access ACCDB file to outside users.

工作簿具有通过数据连接链接到Access数据库的数据透视表。

The workbook has pivot-tables linked via a data-connection to the Access database.

有没有办法建立相对于工作簿位置的Access连接路径？

我希望用户能够点击  刷新所有连接  ，不会提示您输入数据库文件的位置。

I would like the user to be able to click Refresh All Connections and not be prompted for the location of the database file.

我已经能够删除数据连接的路径，以便不会提示来自我的机器的路径（下面的代码），但是每个数据仍会提示两次工作簿中的连接，当他们执行 refresh
all 。

public static void ReplaceStringsInConnections(string filepath, string workingFolder, List<KeyValuePair<string, string>> replacementPairs)
{
    string oldFilepath = Path.Combine(workingFolder, "connectionsOLD.xml");
    string newFilepath = Path.Combine(workingFolder, "connectionsNEW.xml");

    try
    {
        using (ZipArchive archive = ZipFile.Open(filepath, ZipArchiveMode.Update))
        {
            ZipArchiveEntry archiveEntry = archive.GetEntry("xl/connections.xml");

            archiveEntry.ExtractToFile(oldFilepath, true);

            archiveEntry.Delete();

            string textOfFile = File.ReadAllText(oldFilepath);

            foreach (KeyValuePair<string, string> replacementPair in replacementPairs)
                textOfFile = textOfFile.Replace(replacementPair.Key, replacementPair.Value);

            File.WriteAllText(newFilepath, textOfFile);

            archive.CreateEntryFromFile(newFilepath, "xl/connections.xml");
        }
    }
    catch (Exception exception)
    {
        string subject = "ExcelUtilities.ReplaceStringsInConnections errored out.";

        string exceptionMessage = exception.Message + (exception.InnerException != null ? (STR.CrLf + exception.InnerException) : "");
        exceptionMessage += STR.CrLf + "Excel file: " + filepath;
        exceptionMessage += STR.CrLf + "Working folder: " + workingFolder;

        ProcessLogger.LogThis("ExcelUtilities.ReplaceStringsInConnections", "Error", null, exceptionMessage, true);

        EmailUtilities.Send(SplmJournalProcessingEvent.ReplaceStringsInConnectionsFailed, subject, exceptionMessage, Parameters.Customer);

        throw new Exception("ERROR: ExcelUtilities.ReplaceStringsInConnections: \"" + m_WorkbookFilePath + "\"." + STR.CrLf + "Error message: \"" + exceptionMessage + "\"");
    }
}

Kipp Woodard

Kipp Woodard

推荐答案

Excel和Access文件是否在同一个地方？

Are the Excel and Access files in the same place?

您可以使用以下方式获取相关数据库：  'CurrentProject.Path'（不带引号）。

You can get the relative DB using this:  'CurrentProject.Path' (without quotes).

如果Excel文件在同一个地方，什么都不做，如果它在SubFolder中，只需添加SubFolder。

If the Excel file is in the same place, do nothing and if it is in a SubFolder, simply add the SubFolder.

GetDBPath = CurrentProject.Path& " \ SourceData \"

GetDBPath = CurrentProject.Path & "\SourceData\"

如果您想获取相对Excel路径，请使用这个。

If you want to get the relative Excel path, use this.

FilePath = ActiveWorkbook.Path & "\sub-DirectoryName\"

这篇关于数据连接的相对路径？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！