通过VBA更改Excel中的现有连接 [英] Changing an existing Connection in Excel via VBA

问题描述

我目前有一个excel透视报表，每天为我的用户刷新.我已经创建了2个确切的报告.万一我的第一台服务器发生故障，每个报告都指向一个不同的SQL Server.

我希望有1个报告，在这里我可以以编程方式更改数据源，而不是尝试维护2个报告中的更改.


我有C#例程，该例程当前打开报告，刷新报告，然后将其保存到用户文件夹中.

有没有一种方法可以在刷新数据透视表之前设置数据源，因此我不必维护2个报告?在电子表格的VBA中还是通过C#应用程序?

我在c#中使用了Microsoft.Office.Interop.Excel对象.如果在VBA中执行此操作，如何传递参数以告诉报告哪个服务器?

I currently have an excel pivot report that is refreshed daily for my users. I have created 2 exact reports. Each report points to a different SQL Server in the case that my 1st server fails.

I would like to have 1 report where I can change the data source pro-grammatically instead of trying to maintain changes in 2 reports.


I have C# routine that opens the report currently, refreshes it and then saves it out to the users folders.

Is there a way that I can set the data source in the pivot report before refreshing it so I don''t have to maintain 2 reports? Either in VBA in the Spreadsheet or through the C# app?

I use a Microsoft.Office.Interop.Excel object in the c#. If I do it in VBA, how do I pass the parameter to tell the report which server?

Thanks!

推荐答案

void RefreshWorkbookTwo(字符串SourcePath，字符串SourceFilename，字符串ReportName，Guid ExecutionID)
{
试试
{
Microsoft.Office.Interop.Excel.Application excelApp =新的Microsoft.Office.Interop.Excel.ApplicationClass();

excelApp.DisplayAlerts = false;

//如果将Visible设置为false，则由于某种原因我们会得到一个异常
excelApp.Visible = false;

工作簿excelWorkbook = excelApp.Workbooks.Open(
SourcePath + SourceFilename，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value，
System.Reflection.Missing.Value);

//透视表通常在后台线程中刷新
//将其设置为false意味着我们可以同步保存

Console.WriteLine(正在遍历查询表...");


foreach(excelWorkbook.Worksheets中的工作表ws)
{
foreach(ws.QueryTables中的QueryTable qt)
{
qt.BackgroundQuery = false;
}
}


foreach(excelWorkbook.PivotCaches()中的PivotCache缓存)
{
cache.BackgroundQuery = false;
}


Console.WriteLine(刷新...");

excelWorkbook.RefreshAll();

var refreshedWorkBookPath = Path.Combine(Path.GetDirectoryName(SourcePath + SourceFilename)，Path.ChangeExtension(Path.GetFileName(Path.GetTempFileName())，"xls"));

Console.WriteLine("Saving ...");

excelWorkbook.Save();

//确保关闭所有excel对象并释放引用
//如果不这样做，则excel实例将保留在运行的进程中
excelWorkbook.Close(false，SourcePath + SourceFilename，null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;

excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;

}
catch(异常e)
{
///关于失败向我和Marie Coleman发送消息
字符串failureMessage ="The" + ReportName +失败" + DateTime.Now.ToString()+，出现以下错误:\ n \ nSource:" + e.Source.ToString()+"\ n \ nMessage:" + e.Message.ToString();
SendMessage(ReportName +"Application Failure"，failureMessage);
///写入错误文件
ProcessLog(ExecutionID，"ReportRunner应用程序错误"，"ERROR"，DateTime.Now，"ReportRunner"，"ReportRunner应用程序"，"Application Error"，null，null，failureMessage);

///杀死Excel进程(如果处于活动状态)
KillProcesses("EXCEL");

}

终于
{
KillProcesses("EXCEL");
}

void RefreshWorkbookTwo(string SourcePath, string SourceFilename, string ReportName, Guid ExecutionID )
{
try
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

excelApp.DisplayAlerts = false;

// if Visible is set to false we get an exception for some reason
excelApp.Visible = false;

Workbook excelWorkbook = excelApp.Workbooks.Open(
SourcePath + SourceFilename,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value);

// pivot tables are normally refreshed in a background thread
// setting this to false means that we can save synchronously

Console.WriteLine("Iterating through the Query Tables...");


foreach (Worksheet ws in excelWorkbook.Worksheets)
{
foreach (QueryTable qt in ws.QueryTables )
{
qt.BackgroundQuery = false;
}
}


foreach (PivotCache cache in excelWorkbook.PivotCaches())
{
cache.BackgroundQuery = false;
}


Console.WriteLine("Refreshing...");

excelWorkbook.RefreshAll();

var refreshedWorkBookPath = Path.Combine(Path.GetDirectoryName(SourcePath + SourceFilename), Path.ChangeExtension(Path.GetFileName(Path.GetTempFileName()), "xls"));

Console.WriteLine("Saving...");

excelWorkbook.Save();

// ensure all excel objects are closed and references are released
// if this is not done an instance of excel will stay in the running processes
excelWorkbook.Close(false, SourcePath + SourceFilename, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;

excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;

}
catch (Exception e)
{
///Sending message to me and Marie Coleman on failure
String failureMessage = "The " + ReportName + " failed " + DateTime.Now.ToString() + " with the following error:\n\nSource: " + e.Source.ToString() + "\n\nMessage: " + e.Message.ToString();
SendMessage(ReportName + " Application Failure", failureMessage);
///Writing to error file
ProcessLog(ExecutionID, "ReportRunner application error", "ERROR", DateTime.Now, "ReportRunner", "ReportRunner Application", "Application Error", null, null, failureMessage);

///Killing the Excel process if it''s active
KillProcesses("EXCEL");

}

finally
{
KillProcesses("EXCEL");
}

这篇关于通过VBA更改Excel中的现有连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！