at the moment my current process is as followed. Query database - > Save file locally -> Open Workbook using Excel Interop Dll, Make Changes To Work Book, Save As using Excel Interop Dll. The reason for save as is because I require some addition settings so the file isn't set to read only.
The issue I'm coming across is that it's saving locally twice. First time is fine, second time a prompt will appear asking if I would like to override. I'm wondering how can I remove the Save File Locally process and have it in memory to work with? If I am able to work with the file in memory, I would have the prompt on
//Save File Locally
var fileLocation = saveFileDialog.InitialDirectory + saveFileDialog.FileName;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Open Workbook Using Excel Interop Dll
Workbook wb = excel.Workbooks.Open(fileLocation);
Worksheet ws1 = wb.Worksheets.get_Item("English");
//Make Changes To WorkBook
ws1.Range["E5"].Value = StartDate;
ws1.Range["G5"].Value = EndDate;
// Save AS Using Excel Interop With shared settings to remove read only access
wb.SaveAs(fileLocation, AccessMode: XlSaveAsAccessMode.xlShared);
You'd better disable the prompt, to what I remember this is possible but it imply a lot of umnaged code...
Microsoft.Office.Interop.MSProject.Application msProjectApp = new Microsoft.Office.Interop.MSProject.Application(); msProjectApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Application msProjectApp = new Microsoft.Office.Interop.Excel.Application(); msProjectApp.Visible = true; //show the application and not need to start a process msProjectApp.DisplayAlerts = false;