troubleshooting Question

How to modify the MS Excel External Data Connection Source/Loction in an C# Visual Studio Application

Avatar of Gerald Jones
Gerald Jones asked on
C#Microsoft AccessMicrosoft ExcelMicrosoft Office
3 Comments2 Solutions453 ViewsLast Modified:
How to specify a path/location of the MS Access database using Excel interop for the Excel query that queries an MS Access database? Once the application is installed the an Excel spreadsheet is linked to an MS ACCESS database query can no longer locate the database. I would like to use C# code to change the database source. The pertinent code is below.

                Microsoft.Office.Interop.Excel.Application app1 = new Microsoft.Office.Interop.Excel.Application();

                app1.Visible = false;
                app1.DisplayAlerts = false;

                ReportsViewModel vm = (panelAnalysis.DataContext as ReportsViewModel);
                FileInfo fi = vm.ReportFileNamesView.CurrentItem as FileInfo;

                //OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Projects\TrackingMaster\TrackingMasterNew.MDB");
            
                var wb1 = app1.Workbooks.Open(appDir + "\\Projects\\TrackingMaster\\Reports\\Reports_ProjectsbyCohort.xlsx", UpdateLinks: 3);

                wb1.RefreshAll();
                
                foreach (Microsoft.Office.Interop.Excel.Worksheet ws1 in wb1.Worksheets)
                {
               
                   string Label_str1 = " ";
                    string Label_str2 = " ";

                    if (datePicker1_str.ToString("MM/dd/yyyy") != "01/01/1977") { Label_str1 += "    Beginning Report Date: " + datePicker1_str.ToString("MM/dd/yyyy"); }
                    if (datePicker2_str.ToString("MM/dd/yyyy") != "01/01/2050") { Label_str2 += "     Ending Report Date: " + datePicker2_str.ToString("MM/dd/yyyy"); }

                    object misValue = System.Reflection.Missing.Value;
                    ws1.Cells[7, 4] = Label_str1;
                    ws1.Cells[8, 4] = Label_str2;
                    wb1.SaveAs(appDir + "\\Projects\\TrackingMaster\\Reports\\Reports_ProjectsbyCohort.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                    ws1.ExportAsFixedFormat(XlFixedFormatType.xlTypeXPS, (appDir + "\\Projects\\TrackingMaster\\Reports\\Reports_ProjectsbyCohort.xps"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    wb1.Close(true, Type.Missing, Type.Missing);
                    app1.Application.Quit();
                    app1.Quit()
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros