在C#中创建带有宏的Excel文档后锁定VBProject的问题 [英] Problem with locked VBProject after created Excel document with macro in c#

问题描述

大家好.
我已经在C#中搜索了一个代码，该代码创建了带有动态添加的VBAProject宏代码的Excel文档.我发现了类似的东西-下面是一段代码.可以按预期创建工作簿和宏，但是存在一个问题.保存文档(行 wb.SaveAs )后，整个VBAProject都被锁定，我再也无法访问它了.当我单击Project Excel时，说该Project不可见.我仍然可以执行由C#( FormatSheet )添加的宏，但是无法编辑代码.
奇怪的是，当我尝试使用以下行: wb.Save(); 时，VBAProject并未锁定(但我必须在Excel中手动保存文件).
我在SaveAs方法中使用的参数有问题吗?
我的Office版本是2003.
如有任何建议，我将不胜感激.

Hi everyone.
I''ve searched for a code in C# which create Excel document with dynamically added macro code for VBAProject. I found something like this - a piece of code is below. A Workbook and macro is created as I expect but there is one problem with it. After saving document (line wb.SaveAs) a whole VBAProject become locked and I haven''t access to it anymore. When I click on Project Excel says that Project is unviewable. I can still execute a macro which is added by C# (FormatSheet) but can''t edit a code.
Weird thing that when I tried to use line: wb.Save(); then VBAProject isn''t locked (but I have to manually save file in Excel).
It is something wrong with parameters I am using in method SaveAs ?
My version of Office is 2003.
I''ll be appreciate for any sugestions.

string MyFile = Path.GetFullPath(".") + @"\sample.xls";
Excel.Application xl = null;
Excel._Workbook wb = null;
Excel._Worksheet sheet = null;
VBIDE.VBComponent module = null;
      
try
{

    if (File.Exists(FileName)) { File.Delete(FileName); }
  
    GC.Collect(); 

    xl = new Excel.Application();                
    xl.Visible = true;
		 
    wb = (Excel._Workbook)(xl.Workbooks.Add( Missing.Value ));              
    sheet = (Excel._Worksheet)wb.ActiveSheet;
				
    for(int r = 0;r<20;r++)
    {
    	for(int c=0;c<10;c++)
    	{
    		sheet.Cells[r + 1, c+1] = 125; 
    	}
    }

               
    module = wb.VBProject.VBComponents.Add( VBIDE.vbext_ComponentType.vbext_ct_StdModule);
    module.CodeModule.AddFromString(GetMacro);
      
    //wb.Save();
                			wb.SaveAs(FileName,Excel.XlFileFormat.xlWorkbookNormal , null, null, false, false,Excel.XlSaveAsAccessMode.xlShared,false,false,null,null,null);
				 
}
catch( Exception theException ) 
{
    Console.WriteLine(theException.Message );
}
finally
{

    try
    {
    	xl.Visible = false;
    	xl.UserControl = false; 					
    	xl.Workbooks.Close();
    }
    catch { }

    xl.Quit();
    
    if (module != null)  { Marshal.ReleaseComObject (module); }
    if (sheet !=null) { Marshal.ReleaseComObject (sheet); }
    if (wb !=null)    { Marshal.ReleaseComObject (wb); }
    if (xl !=null)    { Marshal.ReleaseComObject (xl); }
    
    module = null;
    sheet=null;
    wb=null;
    xl = null;
    GC.Collect(); 
}

private static string GetMacro()
{
   StringBuilder sb = new StringBuilder();
  
   sb.Append("Sub FormatSheet()" + "\n");
   sb.Append("  Range(\"A6:J13\").Select " + "\n");
   sb.Append("  Selection.Font.ColorIndex = 3" + "\n");
   sb.Append("End Sub");

   return sb.ToString();
}

推荐答案

我使用SaveAs方法的参数进行了一些实验，当我使用此表单时

I''ve done some experiments with parameters of the SaveAs method and when I used this form

wb.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, false, false, null, null, null);

我可以访问VBProject，因此似乎枚举

I have access to VBProject so it seems that enumeration

Excel.XlSaveAsAccessMode.xlShared

在这种情况下是造成我问题的原因.

is in this case responsible for my problem.

这篇关于在C#中创建带有宏的Excel文档后锁定VBProject的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！