Microsoft Excel cannot access the file on Windows Server 2012

Question

I am running an SSIS Package that contains a C# Script which formats an Excel File on Windows Server 2012 R2.

When I run the package it gives me this error Microsoft Office Excel cannot access the file '\\FolderPath\FilePath'

I have seen this question Microsoft Office Excel cannot access the file 'c:\inetpub\wwwroot\Timesheet\App_Data\Template.xlsx' and have checked my permission's and they are correct.

I also tried to add Double Quotes around the final FilePath like this sFile = "\"" + sFile + "\""; but this outputs the error Microsoft Excel cannot access the file '"\FolderPath\FilePath" it is removing one \ I really don't understand why.

Below is the original code

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Text;
using System.Diagnostics;
using System.Reflection;

public int Main()
    {

     StringBuilder sb = new StringBuilder();
     string LogFilePath = "\\\\LogFilePath";
     string  strExcelDataOutPut = "\\\\FolderPath"; 
     string sPath = "\\filePath";

    try {
        FormatFile(strExcelDataOutPut,sPath);

    } catch (Exception ex) {

using (System.IO.StreamWriter outfile = new System.IO.StreamWriter(LogFilePath))
       {
   sb.AppendLine("Error Occured ..Please see the error Message :" + ex.Message);
   outfile.Write(sb.ToString());

       }
   }

    }
 public void FormatFile(string strExcelDataOutPut, string sPath)
                {
                    Microsoft.Office.Interop.Excel.Application objExcelApp = new Excel.Application();
                    Microsoft.Office.Interop.Excel.Workbook objExcelWbk = default(Excel.Workbook);
                    Microsoft.Office.Interop.Excel.Worksheet objWrksheet = default(Excel.Worksheet);

                    object missing = Missing.Value;
                    Excel.Range crange1;

                    string sFile = string.Empty;
                    string sWorkSheet = string.Empty;

                    //--Month in English/French
                    string sMonthYear = string.Empty;

                    try
                    {
                        objExcelApp.DisplayAlerts = false;
                        objExcelApp.Visible = false;

                        sFile = strExcelDataOutPut + sPath;

                        //--Check if the file exists ---------------------------------------------------------
                        if (System.IO.File.Exists(sFile))
                        {
                            sWorkSheet = "Sheet1";
                        }

                        objExcelWbk = objExcelApp.Workbooks.Open(sFile.Trim(), missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, true);
                        objWrksheet = (Excel.Worksheet)objExcelWbk.Worksheets[sWorkSheet];
                        ((Microsoft.Office.Interop.Excel._Worksheet)objWrksheet).Activate();

                        //--Format
                        sMonthYear = "Report as at: " + DateTime.Today.ToString("MMMM") + " " + DateTime.Today.Day.ToString() + ", " + DateTime.Today.Year.ToString();
                        objWrksheet.PageSetup.LeftHeader = "&8&F";
                        //objWrksheet.PageSetup.CenterFooter = @"&12&""Arial,Bold" + sMonthYear;
                        objWrksheet.PageSetup.CenterFooter = " " + sMonthYear;

                        crange1 = (Excel.Range)objWrksheet.Cells[1, 1];
                        crange1.Select();

                        //objExcelWbk.SaveAs(sFile, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                        //objExcelWbk.Close(true, missing, missing);
                        objExcelWbk.Save();
                        objExcelWbk.Close(true, sFile, missing);                
                        objExcelApp.Quit();
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        objWrksheet = null;
                        objExcelWbk = null;
                        objExcelApp = null;
                        System.GC.Collect();
                    }
                }

Answer 1

So I came across this Microsoft Blog. Although this is very weird that why you need to create the Desktop folders but it works. In case the link gets removed see the Answer Below

Resolution ************ ·

A “Desktop” folder seems to be necessary in the “systemprofile” folder in the location C:\Windows\SysWOW64\config\ to open an Excel file ·

Create the “Desktop” folder for Windows 2008 Server (x64) under the location C:\Windows\SysWOW64\config\systemprofile ·

And for a 32 bit Windows 2008 Server create the “Desktop” folder under the location C:\Windows\System32\config\systemprofile ·

After creating the folder the SQL Server jobs should execute successfully