none
Excel, relative path for a data connection. RRS feed

  • Question

  • Office 2013.

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

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

    Is there a way to make the Access connection path relative to the workbook's location?

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

    I have been able to remove the path of the data connections so that they aren't prompted with paths that come from my machine (code below), but they still get prompted twice for each data connection in the workbook when they do a 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

    Thursday, October 8, 2015 12:58 PM

Answers