从C＃打开Excel ODBC连接

I'm looking to create a process that opens several excel files and just refreshes them. I have code:

我正在寻找创建一个打开几个excel文件并刷新它们的进程。我有代码:

   excel.Visible = true;
    Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open("Testfile.xlsx");

    workbook.RefreshAll();
    workbook.Save();

The issue is that because on these worksheets the connections are set to refresh in the background it is trying to save before it has finished refreshing. I know i need to set BackgroundQuery = false, but I don't know how to access the already existing connections. Any help is appreciated.

问题是因为在这些工作表上,连接被设置为在它完成刷新之前尝试保存的后台刷新。我知道我需要设置BackgroundQuery = false,但我不知道如何访问已经存在的连接。任何帮助表示赞赏。

2 个解决方案

#1

So I didn't manage to get this specific code to work, but by trial and error I figured out that:

所以我没有设法使这个特定的代码工作,但通过反复试验,我发现:

foreach (Microsoft.Office.Interop.Excel.WorkbookConnection i in workbook.Connections)
        { System.Console.WriteLine(i.Name);
        i.OLEDBConnection.BackgroundQuery = false;
        }

works.

#2

I don't know much C, so the syntax may be wrong, but it's the worksheet.querytable object that you want:

我不太了解C,所以语法可能有误,但它是你想要的worksheet.querytable对象:

Microsoft.Office.Interop.Excel.Worksheet ws = workbook.worksheets(1)
Microsoft.Office.Interop.Excel.Worksheet qt = ws.querytables(1)

ws.QueryTables(1).RefreshOnFileOpen = False
ws.QueryTables(1).BackgroundQuery = False

#1