CODEWITHSUNDEEP
excel
Barnabeck
Barnabeck

Reputation: 481

Remove data connection of Excel Spreadsheet automatically

I need to automatize the following operation:

That report is sent to the customer and therefore can't possible include the query code.

The opening, updating and saving as a copy is not a problem and I will do it with a scripting tool that creates a Windows exe-file that then can be launched at the time I schedule it in Windows TaskScheduler.

But how can I manage to eliminate the data connection?

Regard, Martin

Upvotes: 1

Views: 1246

Answers (2)

Asbjoedt
Asbjoedt

Reputation: 89

If you are using Excel Interop, you can use this code

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application app = new Excel.Application();
app.DisplayAlerts = false;
Excel.Workbook wb = app.Workbooks.Open(filepath);
int count_conn = wb.Connections.Count;
if (count_conn > 0)
{
    for (int i = 1; i <= wb.Connections.Count; i++)
    {
        wb.Connections[i].Delete();
        i = i - 1;
    }
    count_conn = wb.Connections.Count;
    wb.Save(); // Save workbook
}
wb.Save();
wb.Close();
app.Quit();
if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
    Marshal.ReleaseComObject(wb);
    Marshal.ReleaseComObject(app);
}

Upvotes: 0

teylyn
teylyn

Reputation: 35915

Use a Workbook_Open event, which runs when the file is opened. Then use the scripting tool to open the file and the VBA should run automatically.

Upvotes: 1

Related Questions