private static void OnDispatch(EventHandler <ComponentEventArgs> dispatched, VB.VBComponent vbComponent)
{
var component = new VBComponent(vbComponent);
var handler = dispatched;
if (handler == null)
{
component.Dispose();
return;
}
IVBProject project;
using (var components = component.Collection)
{
project = components.Parent;
}
if (project.Protection == ProjectProtection.Locked)
{
project.Dispose();
component.Dispose();
return;
}
var eventArgs = new ComponentEventArgs(project.ProjectId, project, component);
handler.Invoke(component, eventArgs);
}
private static void AddModule(Excel.Workbook xlWbk, string source)
{
xlWbk.Application.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityLow;
Excel.Application xlApp = xlWbk.Application;
VBE.VBProject vbProject = xlWbk.VBProject;
VBE.vbext_ComponentType vbComponentType = VBE.vbext_ComponentType.vbext_ct_StdModule;
VBE.VBComponent vbModule = vbProject.VBComponents.Add(vbComponentType);
VBE.CodeModule vbCode = vbModule.CodeModule;
// Adds code to the module
vbCode.Name = Path.GetFileName(source).Split(".")[0];
vbCode.InsertLines(1, File.ReadAllText(source));
xlApp.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityByUI;
// Clean up
while (Marshal.ReleaseComObject(vbProject) != 0)
{
}
while (Marshal.ReleaseComObject(vbModule) != 0)
{
}
while (Marshal.ReleaseComObject(vbCode) != 0)
{
}
}
private void frmMacros_Load(object sender, EventArgs e)
{
wb = xl.ActiveWorkbook;
proj = wb.VBProject;
var projName = proj.Name;
projType = Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc;
cmbMacrosNames.Items.Clear();
foreach (var item in proj.VBComponents)
{
vbide.VBComponent vbComponent = item as vbide.VBComponent;
if (vbComponent != null)
{
string componentName = vbComponent.Name;
vbide.CodeModule comCode = vbComponent.CodeModule;
int comCodeLines = comCode.CountOfLines;
int line = 1;
while (line <= comCodeLines)
{
string proceName = comCode.get_ProcOfLine(line, out projType);
if (line == comCode.get_ProcStartLine(proceName, projType))
{
if (proceName != null)
{
cmbMacrosNames.Items.Add(proceName);
}
}
line = line + 1;
}
}
}
}
void VB._dispVBComponentsEvents.ItemRenamed(VB.VBComponent VBComponent, string OldName)
{
var component = new VBComponent(VBComponent);
var handler = ComponentRenamed;
if (handler == null)
{
component.Dispose();
return;
}
IVBProject project;
using (var components = component.Collection)
{
project = components.Parent;
}
if (project.Protection == ProjectProtection.Locked)
{
project.Dispose();
component.Dispose();
return;
}
handler.Invoke(component, new ComponentRenamedEventArgs(project.ProjectId, project, component, OldName));
}
private void btnCreateMacro_Click(object sender, EventArgs e)
{
foreach (vbide.VBComponent item in proj.VBComponents)
{
vbide.VBComponent Md = item as vbide.VBComponent;
vbide.CodeModule MdCode = Md.CodeModule;
if (MdCode != null)
{
if (MdCode.get_ProcOfLine(1, out projType) == txtMacroName.Text)
{
MdCode.DeleteLines(1, Md.CodeModule.get_ProcCountLines(txtMacroName.Text, projType));
}
}
}
vbide.VBComponent Mdn;
Mdn = proj.VBComponents.Add(vbide.vbext_ComponentType.vbext_ct_StdModule);
string sCode = "";
sCode = "public Sub " + txtMacroName.Text + "() \n";
int LC = txtVBACode.Lines.Count();
for (int i = 0; i < LC; i++)
{
sCode += "" + txtVBACode.Lines[i].ToString() + "\n";
}
Mdn.CodeModule.AddFromString(sCode);
cmbMacrosNames.Items.Clear();
foreach (var item2 in proj.VBComponents)
{
vbide.VBComponent vbComponent = item2 as vbide.VBComponent;
if (vbComponent != null)
{
string componentName = vbComponent.Name;
vbide.CodeModule comCode = vbComponent.CodeModule;
int comCodeLines = comCode.CountOfLines;
int line = 1;
while (line <= comCodeLines)
{
string proceName = comCode.get_ProcOfLine(line, out projType);
if (line == comCode.get_ProcStartLine(proceName, projType))
{
if (proceName != null)
{
cmbMacrosNames.Items.Add(proceName);
}
}
line = line + 1;
}
}
}
MessageBox.Show(txtMacroName.Text + " Macro has been saved successfully");
}
private void LaunchExcel(string fullPath, string name, IEnumerable <KeyValuePair <string, string> > macroRangeList, string destinationFolderPath)
{
try
{
//if (IsFileLocked(fullPath))
// return;
//CurrentFilename = name;
//WriteLog("Launch Excel in background");
// Create an instance of Microsoft Excel
Excel = new Excel.Application
{
Visible = false,
DisplayAlerts = false,
ScreenUpdating = true,
};
// Define Workbooks
Books = Excel.Workbooks;
Book = null;
//WriteLog(string.Format("Open Excel workbook {0}", name));
Book = Books.Open(fullPath, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing,
_missing, _missing, _missing, _missing, _missing, _missing);
// Create a new VBA code module.
Module = Book.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
var i = 0;
foreach (var macroRange in macroRangeList)
{
i++;
// VBA code for the dynamic macro that calls
KeyValuePair <string, string> range = macroRange;
//WriteLog(string.Format("Add dynamically macro {0}", range.Key.Replace(" ", string.Empty)));
// Add the VBA macro to the new code module.
var macro = GetMacroCode(range.Key, range.Value, i, destinationFolderPath);
Module.CodeModule.AddFromString(macro);
//WriteLog("Run macro");
RunMacro(Excel, new Object[] { range.Key.Replace(" ", string.Empty) + i });
}
// Quit Excel and clean up.
QuitExcel();
//CurrentFilename = string.Empty;
}
catch (Exception)
{
//WriteLog("LaunchExcel Error");
}
}
private void cmbMacrosNames_SelectedIndexChanged(object sender, EventArgs e)
{
// proj = wb.VBProject;
// projType = Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc;
txtMacroName.Text = cmbMacrosNames.Text;
foreach (var item in proj.VBComponents)
{
vbide.VBComponent vbComponent = item as vbide.VBComponent;
vbide.CodeModule comCode = vbComponent.CodeModule;
for (int i = 1; i <= comCode.CountOfLines; i++)
{
if (comCode.ProcOfLine[i, out projType] == txtMacroName.Text)
private void RunMacro(Excel.Workbook wb)
{
VBIDE.VBComponent module = wb.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(getMacro());
wb.Application.Run("jelolt" + nbrOfNominees.ToString(), misValue, misValue, misValue,
misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue,
misValue, misValue, misValue);
}
private static void OnDispatch(EventHandler <ComponentEventArgs> dispatched, VB.VBComponent vbComponent)
{
var handler = dispatched;
if (handler != null)
{
var component = new VBComponent(vbComponent);
var project = component.Collection.Parent;
if (project.Protection != ProjectProtection.Locked)
{
handler.Invoke(component, new ComponentEventArgs(project.ProjectId, project, component));
}
}
}
private static void OnComponentRenamed(VB.VBComponent vbComponent, string oldName)
{
var handler = ComponentRenamed;
if (handler != null)
{
var component = new VBComponent(vbComponent);
var project = component.Collection.Parent;
if (project.Protection != ProjectProtection.Locked)
{
handler.Invoke(component, new ComponentRenamedEventArgs(project.ProjectId, project, new VBComponent(vbComponent), oldName));
}
}
}
private int GetInstalledVBACodeVersion(int pCurrentVersion, Excel._Workbook pWorkbook)
{
// Legacy method name: VBACodeVersion
// Returns current installed VBA code version in the user's spreadsheet.
// Removes old version if found
int vbaCodeVersion = 0;
var project = pWorkbook.VBProject;
var projectName = project.Name;
foreach (var component in project.VBComponents)
{
VBA.VBComponent vbComponent = (VBA.VBComponent)component;
if (vbComponent != null)
{
string componentName = vbComponent.Name;
var componentCode = vbComponent.CodeModule;
int componentCodeLines = componentCode.CountOfLines;
int line = 1;
string oneLine = "";
while (line < componentCodeLines)
{
// Looking for the version number in the format YYMMDDHHmm
// This must be in the VBA code in Excel to detect if needs updated with the code in the ExcelVBA.txt that is included in this project.
oneLine = componentCode.Lines[line, 1];
// Only look for version if not already found
if (vbaCodeVersion == 0)
{
int versionSearchResult = SearchVBACodeVersionInLine(oneLine);
if (versionSearchResult > 0)
{
vbaCodeVersion = versionSearchResult;
}
}
line++;
}
}
}
return(vbaCodeVersion);
}
public bool InsertProcedure(Accessor accessor, string name, string ReturnType = "")
{
VBA.VBComponent newStandardModule = VBProj.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
newStandardModule.Name = "Test Module";
string code = "";
if (accessor == Accessor.Private)
{
code = "Private";
}
else if (accessor == Accessor.Public)
{
code = "Public";
}
code += " ";
if (ReturnType == "")
{
code += "Sub";
}
else
{
code += "Function";
}
if (!FilterName(name))
{
return(false);
}
code += " " + name + "() ";
if (ReturnType != "")
{
code += $"As {ReturnType}";
}
MessageBox.Show(code);
return(true);
}
void VB._dispVBComponentsEvents.ItemRenamed(VB.VBComponent VBComponent, string OldName)
{
using (var component = new VBComponent(VBComponent))
{
var handler = ComponentRenamed;
if (handler == null)
{
return;
}
using (var components = component.Collection)
using (var project = components.Parent)
{
if (project.Protection == ProjectProtection.Locked)
{
return;
}
}
var qmn = new QualifiedModuleName(component);
handler.Invoke(component, new ComponentRenamedEventArgs(qmn, OldName));
}
}
public void Update()
{
List <string> list = new List <string>();
var procedureType = VBA.vbext_ProcKind.vbext_pk_Proc;
VBA.VBComponent comp = Comp;
if (comp != null)
{
// 파일 이름
string componentName = comp.Name;
var compCode = comp.CodeModule;
int codeLines = compCode.CountOfLines;
int line = 1;
while (line < codeLines)
{
string procedureName = compCode.get_ProcOfLine(line, out procedureType);
if (procedureName != string.Empty)
{
int procedureLines = compCode.get_ProcCountLines(procedureName, procedureType);
int procedureStartLine = compCode.get_ProcStartLine(procedureName, procedureType);
int codeStartLine = compCode.get_ProcBodyLine(procedureName, procedureType);
list.Add(procedureName);
line += procedureLines - 1;
}
line++;
}
}
AllProcedureNames = list;
}
private static void OnDispatch(EventHandler <ComponentEventArgs> dispatched, VB.VBComponent vbComponent)
{
using (var component = new VBComponent(vbComponent))
{
var handler = dispatched;
if (handler == null)
{
return;
}
using (var components = component.Collection)
using (var project = components.Parent)
{
if (project.Protection == ProjectProtection.Locked)
{
return;
}
var qmn = new QualifiedModuleName(component);
var eventArgs = new ComponentEventArgs(qmn);
handler.Invoke(component, eventArgs);
}
}
}
private static void OnComponentSelected(VB.VBComponent vbComponent)
{
OnDispatch(ComponentSelected, vbComponent);
}
public static void Main(string[] args)
{
Console.WriteLine("Process models mining started...");
string[] models = Directory.GetFiles(MODELS_PATH, "*.vsd", SearchOption.AllDirectories);
foreach (string model in models)
{
Console.WriteLine(model);
Visio.Application visioApp = new Visio.Application();
Visio.Document document = visioApp.Documents.Open(model);
visioApp.Visible = true;
VBA.VBComponent codeModule = document.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
string vbaCode = @"
Sub ExtractProcessModel()
Set file = CreateObject(""Scripting.FileSystemObject"").CreateTextFile(""${models_path}"" & ActiveDocument.Name & "".java"", True, True)
file.Write (""/* "" & ActiveDocument.Name & "" */ {"" & vbNewLine)
file.Write (""Process process = repository.createProcess("""""" & ActiveDocument.Name & """""");"" & vbNewLine)
For i = 1 To ActivePage.Shapes.Count
'For native Visio-based EPC diagrams
If InStr(ActivePage.Shapes(i).NameU, ""Dynamic connector"") <> 0 Then
FromShape = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(1).ToSheet.Text
ToShape = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(2).ToSheet.Text
FromShapeID = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(1).ToSheet.ID
ToShapeID = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(2).ToSheet.ID
FromShapeName = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(1).ToSheet.NameU
ToShapeName = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(2).ToSheet.NameU
If InStr(FromShapeName, ""Organizational unit"") <> 0 Then
file.Write (""repository.createResourceFlow("")
ElseIf InStr(FromShapeName, ""Information/ Material"") <> 0 Then
file.Write (""repository.createInputFlow("")
ElseIf InStr(ToShapeName, ""Information/ Material"") <> 0 Then
file.Write (""repository.createOutputFlow("")
Else
file.Write (""repository.createControlFlow("")
End If
For j = 1 To ActivePage.Shapes(i).FromConnects.ToSheet.Connects.Count
CurrentShapeName = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.NameU
ResText = Null
ResID = Null
If j = 1 Then
ResText = FromShape
ResID = FromShapeID
Else
ResText = ToShape
ResID = ToShapeID
End If
If InStr(CurrentShapeName, ""Event"") <> 0 Then
file.Write (""repository.createEvent("""""" & ResText & """""", process)"")
End If
If InStr(CurrentShapeName, ""Function"") <> 0 Then
file.Write (""repository.createFunction("""""" & ResText & """""", process)"")
End If
If InStr(CurrentShapeName, ""XOR"") <> 0 Then
file.Write (""repository.createXOrGateway("""""" & ResID & """""", process)"")
ElseIf InStr(CurrentShapeName, ""OR"") <> 0 Then
file.Write (""repository.createOrGateway("""""" & ResID & """""", process)"")
End If
If InStr(CurrentShapeName, ""AND"") <> 0 Then
file.Write (""repository.createAndGateway("""""" & ResID & """""", process)"")
End If
If InStr(CurrentShapeName, ""Process path"") <> 0 Then
file.Write (""repository.createProcessInterface("""""" & ResText & """""", process)"")
End If
If InStr(CurrentShapeName, ""Organizational unit"") <> 0 Then
file.Write (""repository.createOrganizationalUnit("""""" & ResText & """""")"")
End If
If InStr(CurrentShapeName, ""Information/ Material"") <> 0 Then
file.Write (""repository.createBusinessObject("""""" & ResText & """""")"")
End If
If j = 1 Then
file.Write ("", "")
Else
file.Write ("");"" & vbNewLine)
End If
Next
End If
'For custom ARIS stencil-based diagrams
If InStr(ActivePage.Shapes(i).Name, ""IsPredecessorOfARIS"") <> 0 Then
Subj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(1).ToSheet.Text
Obj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(2).ToSheet.Text
file.Write (""repository.createControlFlow("")
For j = 1 To ActivePage.Shapes(i).FromConnects.ToSheet.Connects.Count
ResName = Null
If j = 1 Then
ResName = Subj
Else
ResName = Obj
End If
ValID = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.ID
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""EventARIS"") <> 0 Then
file.Write (""repository.createEvent("""""" & ResName & """""", process)"")
End If
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""FunctionARIS"") <> 0 Then
file.Write (""repository.createFunction("""""" & ResName & """""", process)"")
End If
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""ProcessInterfaceARIS"") <> 0 Then
file.Write (""repository.createProcessInterface("""""" & ResName & """""", process)"")
End If
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""ANDruleARIS"") <> 0 Then
file.Write (""repository.createAndGateway("""""" & ValID & """""", process)"")
End If
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""XORruleARIS"") <> 0 Then
file.Write (""repository.createXOrGateway("""""" & ValID & """""", process)"")
ElseIf InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""ORruleARIS"") <> 0 Then
file.Write (""repository.createOrGateway("""""" & ValID & """""", process)"")
End If
If j = 1 Then
file.Write ("", "")
Else
file.Write ("");"" & vbNewLine)
End If
Next
End If
If InStr(ActivePage.Shapes(i).Name, ""RoleInProcessARIS"") <> 0 Then
Subj = Null
Obj = Null
For j = 1 To ActivePage.Shapes(i).FromConnects.ToSheet.Connects.Count
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""FunctionARIS"") <> 0 Then
Subj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Text
Else
Obj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Text
End If
Next
file.Write (""repository.assignOrganizationalUnits(repository.createFunction("""""" & Subj & """""", process), repository.createOrganizationalUnit("""""" & Obj & """"""));"" & vbNewLine)
End If
If InStr(ActivePage.Shapes(i).Name, ""SoftwareRoleARIS"") <> 0 Then
Subj = Null
Obj = Null
For j = 1 To ActivePage.Shapes(i).FromConnects.ToSheet.Connects.Count
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""FunctionARIS"") <> 0 Then
Subj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Text
Else
Obj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Text
End If
Next
file.Write (""repository.assignApplicationSystems(repository.createFunction("""""" & Subj & """""", process), repository.createApplicationSystem("""""" & Obj & """"""));"" & vbNewLine)
End If
If InStr(ActivePage.Shapes(i).Name, ""InputObjectARIS"") <> 0 Then
Subj = Null
Obj = Null
For j = 1 To ActivePage.Shapes(i).FromConnects.ToSheet.Connects.Count
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""FunctionARIS"") <> 0 Then
Subj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Text
Else
Obj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Text
End If
Next
file.Write (""repository.assignInputs(repository.createFunction("""""" & Subj & """""", process), repository.createBusinessObject("""""" & Obj & """"""));"" & vbNewLine)
End If
If InStr(ActivePage.Shapes(i).Name, ""OutputObjectARIS"") <> 0 Then
Subj = Null
Obj = Null
For j = 1 To ActivePage.Shapes(i).FromConnects.ToSheet.Connects.Count
If InStr(ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Name, ""FunctionARIS"") <> 0 Then
Subj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Text
Else
Obj = ActivePage.Shapes(i).FromConnects.ToSheet.Connects(j).ToSheet.Text
End If
Next
file.Write (""repository.assignOutputs(repository.createFunction("""""" & Subj & """""", process), repository.createBusinessObject("""""" & Obj & """"""));"" & vbNewLine)
End If
Next
file.Write (""repository.store();"" & vbNewLine)
file.Write (""}"")
file.Close
End Sub";
vbaCode = vbaCode.Replace("${models_path}", MODELS_PATH);
codeModule.CodeModule.AddFromString(vbaCode);
document.ExecuteLine("ExtractProcessModel");
document.VBProject.VBComponents.Remove(codeModule);
document.Save();
document.Close();
visioApp.Quit();
}
string[] files = Directory.GetFiles(MODELS_PATH, "*.java", SearchOption.AllDirectories);
string modelsCollection = @"
package main.resources.repository;
import edu.kopp.phd.ProcessModelRepositoryApplication;
import edu.kopp.phd.model.flow.Process;
import edu.kopp.phd.repository.RDFRepository;
import org.junit.Test;
public class PortalProcessModelsCollection {
public RDFRepository repository = RDFRepository.getInstance();
";
foreach (string file in files)
{
modelsCollection += File.ReadAllText(file) + "\n";
File.Delete(file);
}
modelsCollection += @"
@Test
public void deploy() {
ProcessModelRepositoryApplication.run();
}
}";
File.WriteAllText(PORTAL_COLLECTION_PATH, modelsCollection);
string[] pages = Directory.GetFiles(PAGES_PATH, "*.html", SearchOption.AllDirectories);
foreach (string page in pages)
{
File.Delete(page);
}
Console.WriteLine("Done!");
}
private void ParseVBComponent(string filePath, VBA.VBComponent component)
{
if (component != null)
{
// Get the file name prefix to remove.
string filePrefix = ConfigurationManager.AppSettings["RemoveNamePrefix"];
VBA.vbext_ProcKind procedureType = VBA.vbext_ProcKind.vbext_pk_Proc;
VBA.CodeModule componentCode = component.CodeModule;
// Clear out the containers.
macroOperations.Clear();
macroCellFormula.Clear();
string procedureName = "";
for (int line = 1; line < componentCode.CountOfLines; line++)
{
// Name of the macro procedure.
procedureName = componentCode.get_ProcOfLine(line, out procedureType);
if (procedureName != string.Empty)
{
int procedureLines = componentCode.get_ProcCountLines(procedureName, procedureType);
int procedureStartLine = componentCode.get_ProcStartLine(procedureName, procedureType);
string procedureBody = componentCode.get_Lines(procedureStartLine, procedureLines);
CompilationUnitSyntax root = VisualBasicSyntaxTree.ParseText(procedureBody).GetRoot()
as CompilationUnitSyntax;
/* TODO: If we wanted to iterate through the statements of the sub block.
* // Get the statements within the sub block.
* MethodBlockSyntax macro = root.Members[0] as MethodBlockSyntax;
* SyntaxList<StatementSyntax> statements = (SyntaxList<StatementSyntax>)macro.Statements;
*
* int statementCount = 0;
* foreach (StatementSyntax statement in statements)
* {
* Console.WriteLine(statement.Kind().ToString());
* Console.WriteLine(i.ToString() + " : " + statement.ToString());
* statementCount++;
* }
*/
// Do the parsing inside the syntax walker.
this.Visit(root);
line += procedureLines - 1;
}
}
if (macroCellFormula.Count > 0 || macroOperations.Count > 0)
{
// Add the file name.
macroInfoBuilder.Append(filePath.Replace(filePrefix, ""));
macroInfoBuilder.Append("," + procedureName);
// Check if we have information for this component and add them.
if (macroCellFormula.Count > 0)
{
macroInfoBuilder.Append("," + EscapeCsvData(string.Join(", ", macroCellFormula)));
}
else
{
macroInfoBuilder.Append(",");
}
if (macroOperations.Count > 0)
{
macroInfoBuilder.Append("," + EscapeCsvData(string.Join(", ", macroOperations)));
}
else
{
macroInfoBuilder.Append(",");
}
macroInfoBuilder.Append(Environment.NewLine);
}
}
}
public VBACodeConnector(int slidenumber, VBA.VBComponent comp)
{
Slidenumber = slidenumber;
Comp = comp;
}
void VB._dispVBComponentsEvents.ItemActivated(VB.VBComponent VBComponent)
{
OnDispatch(ComponentActivated, VBComponent);
}
void VB._dispVBComponentsEvents.ItemReloaded(VB.VBComponent VBComponent)
{
OnDispatch(ComponentReloaded, VBComponent);
}
public void Main()
{
Variables UserVariables = null;
Dts.VariableDispenser.LockForRead("User::VBA_MacroName");
Dts.VariableDispenser.LockForRead("User::VBA_Script");
Dts.VariableDispenser.LockForRead("User::VBA_Parameters");
Dts.VariableDispenser.GetVariables(ref UserVariables);
//The macro and the macro name were stored in the original data set, so we can get those from local variables.
string Macro = UserVariables["User::VBA_Script"].Value.ToString();
string Macro_name = UserVariables["User::VBA_MacroName"].Value.ToString();
XmlDocument doc = new XmlDocument();
doc.LoadXml(UserVariables["User::VBA_Parameters"].Value.ToString());
XmlNodeList Parameters = doc.GetElementsByTagName(@"Parameter");
object[] AllParamArray = new object[31];
object[] MyParamArray = new object[Parameters.Count];
//Fill the array with as many missing values as there are parameters in the Run macro command
for (int i = 0; i < AllParamArray.Length; i++)
{
AllParamArray[i] = Missing.Value;
}
//get the parameters that we are actually going to use.
for (int i = 0; i < Parameters.Count; i++)
{
MyParamArray[i] = Parameters[i].Attributes["Value"].Value.ToString();
}
//the first parameter is always the macro name
AllParamArray[0] = Macro_name;
//after that, we can insert our list of all the parameters we need into the list of all the parameters Excel needs
MyParamArray.CopyTo(AllParamArray, 1);
//Get Excel ready to be opened
Excel.Application ExcelObject = default(Excel.Application);
Excel.WorkbookClass oBook = default(Excel.WorkbookClass);
Excel.Workbooks oBooks = default(Excel.Workbooks);
//get the vba module ready
VBIDE.VBComponent module = null;
//open excel in the background
ExcelObject = new Excel.Application();
ExcelObject.Visible = false;
ExcelObject.DisplayAlerts = false;
//Open our report
oBooks = ExcelObject.Workbooks;
oBook = (Excel.WorkbookClass)oBooks.Add(Missing.Value);
//Add a module to our report and populate it with our vba macro
module = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(Macro);
ExcelObject.Run
(AllParamArray[0], AllParamArray[1], AllParamArray[2], AllParamArray[3], AllParamArray[4], AllParamArray[5], AllParamArray[6], AllParamArray[7], AllParamArray[8],
AllParamArray[9], AllParamArray[10], AllParamArray[11], AllParamArray[12], AllParamArray[13], AllParamArray[14], AllParamArray[15], AllParamArray[16], AllParamArray[17],
AllParamArray[18], AllParamArray[19], AllParamArray[20], AllParamArray[21], AllParamArray[22], AllParamArray[23], AllParamArray[24], AllParamArray[25], AllParamArray[26],
AllParamArray[27], AllParamArray[28], AllParamArray[29], AllParamArray[30]);
UserVariables["User::TriedToSaveFileAs"].Value = UserVariables["User::Current_DataFile"].Value.ToString();
oBook.Close(false, Missing.Value, Missing.Value);
ExcelObject.Application.Quit();
ExcelObject = null;
}
void VB._dispVBComponentsEvents.ItemSelected(VB.VBComponent VBComponent)
{
OnDispatch(ComponentSelected, VBComponent);
}
static void Main(string[] args)
{
var wordApp = new Word.Application();
wordApp.Documents.Add();//@"C:\Users\ASUS\Desktop\Титульник.docx");
var doc = wordApp.Documents[1];
VBIDE.VBComponent oModule = doc.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
try
{
string sCode =
//"public sub VBAMacro()\r\n" +
//" msgbox \"VBA Macro called\"\r\n" +
//"end sub";
"Sub Title()\r\n" +
"Selection.PageSetup.TopMargin = CentimetersToPoints(1)\r\n" +
"Selection.PageSetup.LeftMargin = CentimetersToPoints(2)\r\n" +
"Selection.PageSetup.RightMargin = CentimetersToPoints(1)\r\n" +
"Selection.Font.Name = \"Times New Roman\"\r\n" +
"Selection.Font.Size = 12\r\n" +
"Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter\r\n" +
"Selection.ParagraphFormat.LineSpacingRule = wdLineSpaceSingle\r\n" +
"Selection.ParagraphFormat.SpaceAfter = 0\r\n" +
"Selection.Font.AllCaps = True\r\n" +
"Selection.TypeText Text:= \"{ОБЪЕКТ}\"\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeText Text:= \"{ЗАКАЗЧИК}\"\r\n" +
"Selection.Font.AllCaps = False\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.Font.Size = 28\r\n" +
"Selection.TypeText Text:= \"СДАТОЧНАЯ ДОКУМЕНТАЦИЯ\"\r\n" +
"Selection.Font.Size = 12\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft\r\n" +
"ActiveDocument.Tables.Add Range:= Selection.Range, NumRows:= 2, NumColumns:= _\r\n" +
"2, DefaultTableBehavior:= wdWord9TableBehavior, AutoFitBehavior:= _\r\n" +
"wdAutoFitFixed\r\n" +
"With Selection.Tables(1)\r\n" +
".LeftPadding = CentimetersToPoints(0)\r\n" +
".RightPadding = CentimetersToPoints(0)\r\n" +
"End With\r\n" +
"Selection.Tables(1).Columns(1).SetWidth ColumnWidth:= 42.8, RulerStyle:= _\r\n" +
"wdAdjustFirstColumn\r\n" +
"Selection.Tables(1).Columns(2).SetWidth ColumnWidth:= 169.8, RulerStyle:= _\r\n" +
"wdAdjustFirstColumn\r\n" +
"Selection.TypeText Text:= \"Проект:\"\r\n" +
"Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveRight Unit:= wdCharacter, Count:= 1\r\n" +
"Selection.Font.Bold = wdToggle\r\n" +
"Selection.Font.Italic = wdToggle\r\n" +
"Selection.TypeText Text:= \"{Раздел проекта 1}\"\r\n" +
"Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveDown Unit:= wdLine, Count:= 1\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveLeft Unit:= wdCharacter, Count:= 1\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveRight Unit:= wdCharacter, Count:= 1\r\n" +
"Selection.Font.Bold = wdToggle\r\n" +
"Selection.Font.Italic = wdToggle\r\n" +
"Selection.TypeText Text:= \"{Раздел проекта 2}\"\r\n" +
"Selection.MoveDown Unit:= wdLine, Count:= 1\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"ActiveDocument.Tables.Add Range:= Selection.Range, NumRows:= 5, NumColumns:= _\r\n" +
"2, DefaultTableBehavior:= wdWord9TableBehavior, AutoFitBehavior:= _\r\n" +
"wdAutoFitFixed\r\n" +
"With Selection.Tables(1)\r\n" +
".LeftPadding = CentimetersToPoints(0)\r\n" +
".RightPadding = CentimetersToPoints(0)\r\n" +
"End With\r\n" +
"Selection.Tables(1).Columns(1).SetWidth ColumnWidth:= 56.8, RulerStyle:= _\r\n" +
"wdAdjustFirstColumn\r\n" +
"Selection.Tables(1).Columns(2).SetWidth ColumnWidth:= 453.7, RulerStyle:= _\r\n" +
"wdAdjustFirstColumn\r\n" +
"Selection.Tables(1).Cell(1, 2).Merge Selection.Tables(1).Cell(4, 2)\r\n" +
"Selection.TypeText Text:= \"Объект:\"\r\n" +
"Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveRight Unit:= wdCharacter, Count:= 1\r\n" +
"Selection.Font.Bold = wdToggle\r\n" +
"Selection.Font.Italic = wdToggle\r\n" +
"Selection.ParagraphFormat.Alignment = wdAlignParagraphJustify\r\n" +
"Selection.TypeText Text:= \" {Полное наименование проекта}\"\r\n" +
"Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone\r\n" +
"With ActiveDocument.Shapes.AddLine(114, 448, 567, 448).Line\r\n" +
".ForeColor.RGB = RGB(0, 0, 0)\r\n" +
"End With\r\n" +
"With ActiveDocument.Shapes.AddLine(114, 462, 567, 462).Line\r\n" +
".ForeColor.RGB = RGB(0, 0, 0)\r\n" +
"End With\r\n" +
"With ActiveDocument.Shapes.AddLine(114, 476.5, 567, 476.5).Line\r\n" +
".ForeColor.RGB = RGB(0, 0, 0)\r\n" +
"End With\r\n" +
"Selection.MoveDown Unit:= wdLine, Count:= 1\r\n" +
"Selection.Font.Bold = wdToggle\r\n" +
"Selection.Font.Italic = wdToggle\r\n" +
"Selection.TypeText Text:= \"{адрес объекта}\"\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveLeft Unit:= wdCharacter, Count:= 16\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone\r\n" +
"Selection.TypeText Text:= \"по адресу:\"\r\n" +
"Selection.MoveUp Unit:= wdLine, Count:= 1\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveUp Unit:= wdLine, Count:= 1\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveUp Unit:= wdLine, Count:= 1\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveDown Unit:= wdLine, Count:= 4\r\n" +
"Selection.Font.Size = 8\r\n" +
"Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter\r\n" +
"Selection.TypeText Text:= \"(адрес объекта)\"\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.Font.Size = 12\r\n" +
"Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"ActiveDocument.Tables.Add Range:= Selection.Range, NumRows:= 1, NumColumns:= _\r\n" +
"2, DefaultTableBehavior:= wdWord9TableBehavior, AutoFitBehavior:= _\r\n" +
"wdAutoFitFixed\r\n" +
"With Selection.Tables(1)\r\n" +
".LeftPadding = CentimetersToPoints(0)\r\n" +
".RightPadding = CentimetersToPoints(0)\r\n" +
"End With\r\n" +
"Selection.Tables(1).Columns(1).SetWidth ColumnWidth:= 71#, RulerStyle:= _\r\n" +
"wdAdjustFirstColumn\r\n" +
"Selection.Tables(1).Columns(2).SetWidth ColumnWidth:= 439.4, RulerStyle:= _\r\n" +
"wdAdjustFirstColumn\r\n" +
"Selection.TypeText Text:= \"Исполнитель:\"\r\n" +
"Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveRight Unit:= wdCharacter, Count:= 1\r\n" +
"Selection.Font.Bold = wdToggle\r\n" +
"Selection.Font.Italic = wdToggle\r\n" +
"Selection.TypeText Text:= \" {должность} ЗАО \"\"ГК \"\"ТЭКС-Автоматик\"\" {Ф.И.О.}\"\r\n" +
"Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone\r\n" +
"Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone\r\n" +
"Selection.MoveDown Unit:= wdLine, Count:= 1\r\n" +
"Selection.Font.Size = 8\r\n" +
"Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter\r\n" +
"Selection.TypeText Text:= \"(должность, наименование монтажной организации, Ф.И.О.)\"\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.Font.Size = 12\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeParagraph\r\n" +
"Selection.TypeText Text:= \"20{год} г.\"\r\n" +
"End Sub\r\n";
// Add the VBA macro to the new code module.
oModule.CodeModule.AddFromString(sCode);
RunMacro(wordApp, new Object[] { "Title" });
wordApp.Visible = false;
doc.SaveAs2(@"C:\Users\ASUS\Desktop\Титульник.docx");
doc.Close();
wordApp.Quit();
}
catch
{
Console.WriteLine("Произошла ошибка");
}
}
public static void ReadAllMacros(string fileName)
{
var excel = new Excel.Application();
var workbook = excel.Workbooks.Open(fileName, false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);
var project = workbook.VBProject;
var projectName = project.Name;
Console.WriteLine($"Project Name = {projectName}");
foreach (var component in project.VBComponents)
{
VBA.VBComponent vbComponent = component as VBA.VBComponent;
if (vbComponent != null)
{
string componentName = vbComponent.Name;
Console.WriteLine("------------------------------------------");
Console.WriteLine($"vbComponentName = {componentName}");
Console.WriteLine("------------------------------------------");
var componentCode = vbComponent.CodeModule;
//Console.WriteLine($"vbComponentCodeModule = {componentCode}");
int componentCodeLines = componentCode.CountOfLines;
//string comments = string.Empty;
if (componentCodeLines > 0)
{
var code = componentCode.get_Lines(1, componentCodeLines);
Console.WriteLine($"code = {code}");
List <string> codeLines = code.Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).ToList();
var allMacros = new List <Macro>();
//string curMacroName = string.Empty, comments = String.Empty;
const string subStr = "Sub", endSubStr = "End Sub";
List <string> curMacroDescription = new List <string>(), curMacroCode = new List <string>();
Macro macro = null;
foreach (string codeLine in codeLines)
{
string trimmedCodeLine = codeLine.Trim();
if (trimmedCodeLine.StartsWith("'") && trimmedCodeLine.Length > 1)
{
curMacroDescription.Add(trimmedCodeLine.Substring(1));
curMacroCode.Add(codeLine);
}
else if (trimmedCodeLine.StartsWith(subStr) && trimmedCodeLine.Length > subStr.Length)
{
curMacroCode.Add(codeLine);
trimmedCodeLine = trimmedCodeLine.Substring(subStr.Length).Trim();
int openBracketIdx = trimmedCodeLine.IndexOf('(');
if (openBracketIdx > -1)
{
macro = new Macro {
Name = $"{componentName}.{trimmedCodeLine.Substring(0, openBracketIdx)}"
};
allMacros.Add(macro);
}
}
else if (trimmedCodeLine == endSubStr)
{
curMacroCode.Add(codeLine);
if (macro != null)
{
macro.Description = string.Join(Environment.NewLine, curMacroDescription);
macro.Code = string.Join(Environment.NewLine, curMacroCode);
curMacroCode = new List <string>();
curMacroDescription = new List <string>();
}
}
else
{
curMacroCode.Add(codeLine);
}
}
//comments = string.Join(Environment.NewLine, codeLines.Where(x => x.StartsWith("'") && x.Length > 1).Select(x => x.Substring(1).Trim()));
Console.WriteLine();
Console.WriteLine("------ RECAP --------------------------");
foreach (var curMacro in allMacros)
{
Console.WriteLine("---------------------------------------");
Console.WriteLine($"Macro name: {curMacro.Name}");
Console.WriteLine();
Console.WriteLine("Macro description:");
Console.WriteLine(string.Join(Environment.NewLine, curMacro.Description));
Console.WriteLine();
Console.WriteLine("Macro code:");
Console.WriteLine(string.Join(Environment.NewLine, curMacro.Code));
}
}
//Console.WriteLine($"Description = {comments}");
//int line = 1;
//while (line < componentCodeLines - 1)
//{
// VBA.vbext_ProcKind procedureType;
// string procedureName = componentCode.get_ProcOfLine(line, out procedureType);
// if (!string.IsNullOrEmpty(procedureName))
// {
// string macroName = $"{componentName}.{procedureName}";
// Console.WriteLine($"Macro Name = {macroName}");
// int procedureLines = componentCode.get_ProcCountLines(procedureName, procedureType);
// int procedureStartLine = componentCode.get_ProcStartLine(procedureName, procedureType);
// int codeStartLine = componentCode.get_ProcBodyLine(procedureName, procedureType);
// if (codeStartLine != procedureStartLine)
// {
// comments += componentCode.get_Lines(line, codeStartLine - procedureStartLine);
// }
// //int signatureLines = 1;
// //while (componentCode.get_Lines(codeStartLine, signatureLines).EndsWith("_"))
// //{
// // signatureLines++;
// //}
// //string signature = componentCode.get_Lines(codeStartLine, signatureLines);
// //signature = signature.Replace("\n", string.Empty);
// //signature = signature.Replace("\r", string.Empty);
// //signature = signature.Replace("_", string.Empty);
// line += procedureLines - 1;
// }
//line++;
//}
//}
//}
}
}
excel.Quit();
}
public async Task <IHttpActionResult> GetExcelFileAfterMacrosExec(string macroDataListJsonStr)
{
Excel.Application excel = null;
Excel._Workbook workbook = null;
VBA.VBComponent module = null;
bool saveChanges = false;
string resultedTempFileName = null;
byte[] resultedBuffer = null;
try
{
if (!Request.Content.IsMimeMultipartContent())
{
throw new Exception();
}
var provider = new MultipartMemoryStreamProvider();
await Request.Content.ReadAsMultipartAsync(provider);
var file = provider.Contents.First();
var filename = file.Headers.ContentDisposition.FileName.Trim('\"');
var buffer = await file.ReadAsByteArrayAsync();
var tempFileName = Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, filename);
resultedTempFileName = Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, $"{Path.GetFileNameWithoutExtension(filename)}_transformed");
//var tempFileName = Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, "TempFiles", filename);
//resultedTempFileName = Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, "TempFiles", filename + "_transformed");
//File.WriteAllBytes(tempFileName, buffer);
await FileHelper.WriteAllBytesAsync(tempFileName, buffer);
excel = new Excel.Application {
Visible = false
};
workbook = excel.Workbooks.Open(tempFileName, false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);
var macroDataList = JsonConvert.DeserializeObject <List <MacroData> >(macroDataListJsonStr);
foreach (var macroData in macroDataList)
{
module = workbook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(macroData.MacroCode);
// Run the named VBA Sub that we just added. In our sample, we named the Sub FormatSheet
workbook.Application.Run(macroData.VbaSubName, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
}
// Let loose control of the Excel instance
excel.Visible = false;
excel.UserControl = false;
// Set a flag saying that all is well and it is ok to save our changes to a file.
saveChanges = true;
// Save the file to disk
//workbook.SaveAs(fileNameToSave, Excel.XlFileFormat.xlWorkbookNormal,
// null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
// false, false, null, null, null);
workbook.SaveAs(resultedTempFileName, Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled, Missing.Value,
Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges, true,
Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception e)
{
Console.WriteLine($"Error in WriteMacro: {e.Message}, Stack Trace: {e.StackTrace}");
}
finally
{
try
{
// Repeat xl.Visible and xl.UserControl releases just to be sure
// we didn't error out ahead of time.
if (excel != null)
{
excel.Visible = false;
excel.UserControl = false;
}
// Close the document and avoid user prompts to save if our
// method failed.
workbook?.Close(saveChanges, null, null);
excel?.Workbooks.Close();
}
catch
{
// ignored
}
// Gracefully exit out and destroy all COM objects to avoid hanging instances
// of Excel.exe whether our method failed or not.
excel?.Quit();
if (module != null)
{
Marshal.ReleaseComObject(module);
}
if (workbook != null)
{
Marshal.ReleaseComObject(workbook);
}
if (excel != null)
{
Marshal.ReleaseComObject(excel); //This is used to kill the EXCEL.exe process
}
GC.Collect();
if (!string.IsNullOrEmpty(resultedTempFileName))
{
resultedTempFileName += ".xlsm";
resultedBuffer = File.ReadAllBytes(resultedTempFileName);
}
}
return(Ok(resultedBuffer));
}
private static void OnComponentReloaded(VB.VBComponent vbComponent)
{
OnDispatch(ComponentReloaded, vbComponent);
}
public static void WriteMacro(string fileName,
string fileNameToSave,
Macro macro)
{
Excel.Application excel = null;
Excel._Workbook workbook = null;
VBA.VBComponent module = null;
bool saveChanges = false;
try
{
excel = new Excel.Application {
Visible = false
};
workbook = excel.Workbooks.Open(fileName, false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);
module = workbook.VBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(macro.Code);
// Run the named VBA Sub that we just added. In our sample, we named the Sub FormatSheet
workbook.Application.Run(macro.VbaSubName, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
// Let loose control of the Excel instance
excel.Visible = false;
excel.UserControl = false;
// Set a flag saying that all is well and it is ok to save our changes to a file.
saveChanges = true;
// Save the file to disk
//workbook.SaveAs(fileNameToSave, Excel.XlFileFormat.xlWorkbookNormal,
// null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
// false, false, null, null, null);
workbook.SaveAs(fileNameToSave, Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled, Missing.Value,
Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges, true,
Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception e)
{
Console.WriteLine($"Error in WriteMacro: {e.Message}, Stack Trace: {e.StackTrace}");
}
finally
{
try
{
// Repeat excel.Visible and excel.UserControl releases just to be sure
// we didn't error out ahead of time.
if (excel != null)
{
excel.Visible = false;
excel.UserControl = false;
}
// Close the document and avoid user prompts to save if our
// method failed.
workbook?.Close(saveChanges, null, null);
excel?.Workbooks.Close();
}
catch
{
// ignored
}
// Gracefully exit out and destroy all COM objects to avoid hanging instances
// of Excel.exe whether our method failed or not.
excel?.Quit();
if (module != null)
{
Marshal.ReleaseComObject(module);
}
if (workbook != null)
{
Marshal.ReleaseComObject(workbook);
}
if (excel != null)
{
Marshal.ReleaseComObject(excel); //This is used to kill the EXCEL.exe process
}
GC.Collect();
}
}
public void runApp(string databaseName, string function)
{
VBA.VBComponent f = null;
VBA.VBComponent f2 = null;
Microsoft.Office.Interop.Access.Application app = null;
object Missing = System.Reflection.Missing.Value;
Object tempObject = null;
try
{
app = new Microsoft.Office.Interop.Access.Application();
app.Visible = true;
app.OpenCurrentDatabase(databaseName, false, "");
//Step 1: Programatically create a new temporary class module in the target Access file, with which to call the target function in the Access database
//Create a Guid to append to the object name, so that in case the temporary class and module somehow get "stuck",
//the temp objects won't interfere with other objects each other (if there are multiples).
string tempGuid = Guid.NewGuid().ToString("N");
f = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_ClassModule);
//We must set the Instancing to 2-PublicNotCreatable
f.Properties.Item("Instancing").Value = 2;
f.Name = "TEMP_CLASS_" + tempGuid;
f.CodeModule.AddFromString(
"Public Sub TempClassCall()\r\n" +
" Call " + function + "\r\n" +
"End Sub\r\n");
//Step 2: Append a new standard module to the target Access file, and create a public function to instantiate the class and return it.
f2 = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
f2.Name = "TEMP_MODULE";
f2.CodeModule.AddFromString(string.Format(
"Public Function instantiateTempClass_{0}() As Object\r\n" +
" Set instantiateTempClass_{0} = New TEMP_CLASS_{0}\r\n" +
"End Function"
, tempGuid));
//Step 3: Get a reference to a new TEMP_CLASS_* object
tempObject = app.Run("instantiateTempClass_" + tempGuid, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing);
//Step 4: Call the method on the TEMP_CLASS_* object.
Microsoft.VisualBasic.Interaction.CallByName(tempObject, "TempClassCall", Microsoft.VisualBasic.CallType.Method);
}
catch (COMException e)
{
MessageBox.Show("A VBA Exception occurred in file:" + e.Message);
}
catch (Exception e)
{
MessageBox.Show("A general exception has occurred: " + e.StackTrace.ToString());
}
finally
{
//Clean up
if (f != null)
{
app.VBE.ActiveVBProject.VBComponents.Remove(f);
Marshal.FinalReleaseComObject(f);
}
if (f2 != null)
{
app.VBE.ActiveVBProject.VBComponents.Remove(f2);
Marshal.FinalReleaseComObject(f2);
}
if (tempObject != null)
{
Marshal.FinalReleaseComObject(tempObject);
}
if (app != null)
{
//Step 5: When you close the database, you call Application.Quit() with acQuitSaveNone, so none of the VBA code you just created gets saved.
app.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
Marshal.FinalReleaseComObject(app);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public void open(string FileName)
{
var powerpnt = new POWERPNT.Application();
var presentation = powerpnt.Presentations.Open(FileName, MsoTriState.msoFalse, MsoTriState.msoFalse, MsoTriState.msoFalse);
var project = presentation.VBProject;
var projectName = project.Name;
var procedureType = Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc;
foreach (var component in project.VBComponents)
{
VBA.VBComponent vbComponent = component as VBA.VBComponent;
if (vbComponent != null)
{
string componentName = vbComponent.Name;
var componentCode = vbComponent.CodeModule;
int componentCodeLines = componentCode.CountOfLines;
int line = 1;
while (line < componentCodeLines)
{
string procedureName = componentCode.get_ProcOfLine(line, out procedureType);
if (procedureName != string.Empty)
{
int procedureLines = componentCode.get_ProcCountLines(procedureName, procedureType);
int procedureStartLine = componentCode.get_ProcStartLine(procedureName, procedureType);
int codeStartLine = componentCode.get_ProcBodyLine(procedureName, procedureType);
string comments = "[No comments]";
if (codeStartLine != procedureStartLine)
{
comments = componentCode.get_Lines(line, codeStartLine - procedureStartLine);
}
int signatureLines = 1;
while (componentCode.get_Lines(codeStartLine, signatureLines).EndsWith("_"))
{
signatureLines++;
}
string signature = componentCode.get_Lines(codeStartLine, signatureLines);
signature = signature.Replace("\n", string.Empty);
signature = signature.Replace("\r", string.Empty);
signature = signature.Replace("_", string.Empty);
for (int i = 0 + line; i <= procedureLines + line - 1; i++)
{
string lineText = componentCode.get_Lines(i, 1);
MessageBox.Show(lineText);
}
line += procedureLines - 1;
}
line++;
}
}
}
powerpnt.Quit();
}