public void LoadTempalteIntoWorkSheet(string path)
{
try
{
var xlApp = (MExcel.Application)ExcelDnaUtil.Application;
//xlApp.Visible = false;
MExcel.Workbook activeBook = xlApp.ActiveWorkbook;
//LogUsage("Fundamentals", control.Id, path);
// Open in same workbook
var sourceWorkBook = xlApp.Workbooks.Open(Filename: path, ReadOnly: true);
try
{
// if the active workbook on on initialization is wiped out and the source workbook
// becomes the active workbook we need to reset the active workbook to a new book.
if (xlApp.ActiveWorkbook.FullName == sourceWorkBook.FullName && xlApp.Workbooks.Count == 1)
{
xlApp.Workbooks.Add();
activeBook = xlApp.ActiveWorkbook;
}
xlApp.DisplayAlerts = false;
// copy all sheets from the source workbook at once to maintain references within all of the related worksheets.
var lastSheet = activeBook.Worksheets.Count;
sourceWorkBook.Worksheets.Copy(After: activeBook.Worksheets[lastSheet]);
}
catch (ApplicationException e)
{
var message = string.Format("Unable to load : '{0}' {1}{2}", path, Environment.NewLine, e.Message);
log.Error(message + e.Message);
MessageBox.Show(message);
}
catch (Exception e)
{
var message = string.Format("Unable to load : '{0}' {1}{2}", path, Environment.NewLine, e.Message);
log.Error(message + e.Message);
MessageBox.Show(message);
}
finally
{
sourceWorkBook.Close(Filename: Type.Missing, RouteWorkbook: Type.Missing);
//sourceWorkBook.Close(SaveChanges: false, Filename: Type.Missing, RouteWorkbook: Type.Missing);
var isSaved = activeBook.Saved;
Marshal.ReleaseComObject(sourceWorkBook);
}
}
catch (Exception e)
{
var message = string.Format("Error: File selected could not be loaded. {0}", Environment.NewLine);
log.Error(message + e.Message);
MessageBox.Show(message);
}
}
/// <summary>
/// https://support.microsoft.com/en-us/kb/282830
/// Remove if not needed
/// Copy the references from the source workbook to the destination workbook
/// </summary>
private void CopyReferences(MExcel.Workbook activeBook, MExcel.Workbook sourceWorkBook)
{
if (!_references.Any())
{
foreach (Microsoft.Vbe.Interop.Reference reference in activeBook.VBProject.References)
{
if (!_references.ContainsKey(reference.Guid))
{
_references.Add(reference.Guid, reference.Description);
}
}
}
foreach (Microsoft.Vbe.Interop.Reference reference in sourceWorkBook.VBProject.References)
{
if (!_references.ContainsKey(reference.Guid))
{
activeBook.VBProject.References.AddFromGuid(reference.Guid, reference.Major, reference.Minor);
_references.Add(reference.Guid, reference.Description);
}
}
}
/// <summary>
/// Copy Macros
/// https://support.microsoft.com/en-us/kb/282830
/// </summary>
private void CopyMacros(MExcel.Workbook activeBook, MExcel.Workbook sourceWorkBook)
{
if (!sourceWorkBook.HasVBProject)
{
return;
}
if (!_vbModules.Any())
{
foreach (VBComponent dest in activeBook.VBProject.VBComponents)
{
if (!_vbModules.ContainsKey(dest.Name))
{
_vbModules.Add(dest.Name, dest);
}
}
}
try
{
foreach (VBComponent sourceComp in sourceWorkBook.VBProject.VBComponents)
{
var sourceLines = sourceComp.CodeModule.CountOfLines;
if (sourceLines == 0)
{
continue;
}
if (_vbModules.ContainsKey(sourceComp.Name))
{
VBComponent destComp = _vbModules[sourceComp.Name];
if (destComp.CodeModule.CountOfLines == sourceLines)
{
var sourceCode = sourceComp.CodeModule.Lines[1, sourceLines];
var destCode = destComp.CodeModule.Lines[1, destComp.CodeModule.CountOfLines];
if (sourceCode == destCode)
{
continue; // the code is exactly the same as an existing module break and get out.
}
}
// append
if (destComp.CodeModule.CountOfLines == 0)
{
destComp.CodeModule.InsertLines(1, sourceComp.CodeModule.Lines[1, sourceLines]);
continue; // found the matching codemodule so lets exit
}
}
else
{
// Add new macro
var destComp = activeBook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
destComp.CodeModule.AddFromString(sourceComp.CodeModule.Lines[1, sourceLines]);
destComp.Name = sourceComp.Name;
_vbModules.Add(destComp.Name, destComp);
}
}
}
catch (Exception e)
{
throw new ApplicationException("Could not copy source macros");
}
activeBook.ChangeLink(Name: sourceWorkBook.Name, NewName: activeBook.Name, Type: MExcel.XlLinkType.xlLinkTypeExcelLinks);
}
public void LoadReportParameter(IRibbonControl control)
{
try
{
var path = _fileLookup[control.Id];
var xlApp = (MExcel.Application)ExcelDnaUtil.Application;
xlApp.Visible = true;
MExcel.Workbook activeBook = xlApp.ActiveWorkbook;
LogUsage(UsageResources.ToolsTemplates, control.Id, path);
if (_openNew)
{
var sourceWorkBook = xlApp.Workbooks.Open(Filename: path, ReadOnly: true);
}
else
{
// Open in same workbook
var sourceWorkBook = xlApp.Workbooks.Open(Filename: path, ReadOnly: true);
try
{
// if the active workbook on on initialization is wiped out and the source workbook
// becomes the active workbook we need to reset the active workbook to a new book.
if (xlApp.ActiveWorkbook.FullName == sourceWorkBook.FullName && xlApp.Workbooks.Count == 1)
{
xlApp.Workbooks.Add();
activeBook = xlApp.ActiveWorkbook;
}
xlApp.DisplayAlerts = false;
// copy all sheets from the source workbook at once to maintain references within all of the related worksheets.
var lastSheet = activeBook.Worksheets.Count;
sourceWorkBook.Worksheets.Copy(After: activeBook.Worksheets[lastSheet]);
//copy vbaproject
CopyMacros(activeBook, sourceWorkBook);
CopyReferences(activeBook, sourceWorkBook);
}
catch (ApplicationException e)
{
var message = string.Format("Unable to load : '{0}' {1}{2}", path, Environment.NewLine, e.Message);
//log.Error(message + e.Message);
//MessageBox.Show(message);
}
catch (Exception e)
{
var message = string.Format("Unable to load : '{0}'", path);
//log.Error(message + e.Message);
//MessageBox.Show(message);
}
finally
{
sourceWorkBook.Close(SaveChanges: false, Filename: Type.Missing, RouteWorkbook: Type.Missing);
var isSaved = activeBook.Saved;
Marshal.ReleaseComObject(sourceWorkBook);
}
}
}
catch (Exception e)
{
var message = string.Format("Error: File selected could not be loaded. {0}", Environment.NewLine);
// log.Error(message + e.Message);
//MessageBox.Show(message);
}
}
