oExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim exl As New Microsoft.Office.Interop.Excel.Application()
Dim wb As Microsoft.Office.Interop.Excel.Workbook = exl.Workbooks.Open("C:\Test\ExcelTest.xlsx")
Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(exl.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
exl.Visible = True
Dim rang As Range = ws.Cells(1, 1)
rang.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
rang.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDashDotDot
'''''wb.Close(True, Type.Missing, Type.Missing)
''''exl.Quit()
End Sub
End Class
This worked for me using a windows form applicationI cant seem to make this work
Code: [Select]Dim rang As Range = ws.Cells(1, 1)
try
{
xlApp.Visible = true;
xlApp.UserControl = true;
Excel.Range currentFind = null;
Excel.Range firstFind = null;
currentFind = (Excel.Range)ws.Columns["B",Type.Missing ];
firstFind = currentFind.Find(strDwgNum, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, 0, 0, Type.Missing);
string foundAddress = firstFind.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
firstFind.Activate();
int rowNum = firstFind.Row;
Excel.Range rowRange = (Excel.Range)ws.Cells("A",rowNum);
rowRange.Value2 = strDwgRev;
}
[CommandMethod("OpenExcelEarlyBind")]
public void OpenExcelEarlyBind()
{
Microsoft.Office.Interop.Excel.Application exl = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
Microsoft.Office.Interop.Excel.Worksheet ws = exl.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
ws.Range["A1"].Value = "Whatever";
ws.Cells[2, 2] = "YoMama";
exl.Visible = true;
}
static object GetInstance(string appName)
{
return Marshal.GetActiveObject(appName);
}
static object CreateInstance(string appName)
{
return Activator.CreateInstance(Type.GetTypeFromProgID(appName));
}
static object GetOrCreateInstance(string appName)
{
try { return GetInstance(appName); }
catch { return CreateInstance(appName); }
}
[CommandMethod("OpenExcelLateBind")]
public void OpenExcelLateBind()
{
Type excelType = Type.GetTypeFromProgID("Excel.Application");
object exl = Activator.CreateInstance(excelType);
object wb = exl.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, exl, null);
object[] param = new object[1];
param[0] = @"C:\Test\ExcelTest.xlsx";
wb.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, wb, param);
param = new Object[1];
param[0] = true;
exl.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, exl, param);
}
[CommandMethod("OpenExcelEarlyBind")]
public void OpenExcelEarlyBind()
{
ExcelApp.Application exl;
try
{
exl = (ExcelApp.Application)Marshal.GetActiveObject("Excel.Application");
}
catch
{
exl = new Microsoft.Office.Interop.Excel.Application();
exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
}
if (exl.Workbooks[exl.Workbooks.Count].Name != "ExcelTest.xlsx")
{
exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
}
ExcelApp.Worksheet ws = exl.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
exl.Visible = true;
ws.Range["A1"].Value = DateTime.Now.ToLongTimeString();
Marshal.FinalReleaseComObject(exl);
}
Hey Bill,
Are you trying something like this?
Code: [Select][CommandMethod("OpenExcelEarlyBind")]
public void OpenExcelEarlyBind()
{
ExcelApp.Application exl;
try
{
exl = (ExcelApp.Application)Marshal.GetActiveObject("Excel.Application");
}
catch
{
exl = new Microsoft.Office.Interop.Excel.Application();
exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
}
if (exl.Workbooks[exl.Workbooks.Count].Name != "ExcelTest.xlsx")
{
exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
}
ExcelApp.Worksheet ws = exl.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
exl.Visible = true;
ws.Range["A1"].Value = DateTime.Now.ToLongTimeString();
Marshal.FinalReleaseComObject(exl);
}
I am assuming you mean me.da hell did I get Bill from?
Excel.Application xlApp;
bool xLopen = false;
try
{
xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
xLopen = true;
}
catch
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
}
Workbook wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet ws = wb.Sheets["Sheet1"] as Worksheet;
xlApp.Visible = true;
xlApp.UserControl = true;
[CommandMethod("PLog")]
public static void ProjectLog()
{
Document doc = acApp.DocumentManager.MdiActiveDocument;
Database db = doc.Database;
Editor ed = doc.Editor;
string DwgPath = Path.GetDirectoryName(acApp.DocumentManager.MdiActiveDocument.Name);
string ProjNum = DwgPath.Substring(DwgPath.LastIndexOf("\\") + 1);
PromptSelectionOptions pso = new PromptSelectionOptions();
TypedValue[] filter = new TypedValue[2] { new TypedValue((int)DxfCode.Start, "INSERT"), new TypedValue((int)DxfCode.BlockName, "VTEP-INFO,TEP-INFO,TEP-CIP,VTEP-CIP,VUES-INFO,UES-INFO,UES-CIP,VUES-CIP") };
SelectionFilter selFilter = new SelectionFilter(filter);
PromptSelectionResult psr = ed.SelectAll(selFilter);
if (psr.Status != PromptStatus.OK)
{
acApp.ShowAlertDialog("Invalid Titleblock in drawing!");
return;
}
string strDwgLine1 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE1");
string strDwgLine2 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE2");
string strDwgNum = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "NUM");
string strDwgVendor = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNAME");
string strDwgVendorNumber = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNUMBER");
string strUserPath = GetUser(System.Environment.UserName);
string strMyFile = string.Concat(strUserPath, "\\", ProjNum, "\\", ProjNum, ".xlsx");
string strFilename = string.Concat(ProjNum + ".xlsx");
Excel.Application xlApp;
bool xLopen = false;
try
{
xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
xLopen = true;
}
catch
{
xlApp = new Microsoft.Office.Interop.Excel.Application();
}
Workbook wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet ws = wb.Sheets["Sheet1"] as Worksheet;
xlApp.Visible = true;
xlApp.UserControl = true;
Excel.Range currentFind = null;
Excel.Range firstFind = null;
currentFind = (Excel.Range)ws.Columns["B", Type.Missing];
firstFind = currentFind.Find(strDwgNum, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, 0, 0, Type.Missing);
string foundAddress = firstFind.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
firstFind.Activate();
int rowNum = firstFind.Row;
if (!string.IsNullOrEmpty(strDwgVendor))
{
ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2 + "/" + strDwgVendor + "," + strDwgVendorNumber;
}
else
{
ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2;
}
ws.Cells[rowNum, 1] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "SHT");
ws.Cells[rowNum, 3] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "REV");
ws.Cells[rowNum, 4] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "CODE");
if (xLopen)
{
wb.Save();
}
else
{
wb.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
}
Marshal.FinalReleaseComObject(xlApp);
}
public static string GetAttributeValueFromBlock(Database db, ObjectId blockRefId, string attName)
{
string textString = null;
using (Transaction tr = db.TransactionManager.StartTransaction())
{
BlockReference blockRef = tr.GetObject(blockRefId, OpenMode.ForRead) as BlockReference;
if (blockRef != null)
{
foreach (ObjectId id in blockRef.AttributeCollection)
{
AttributeReference attRef = tr.GetObject(id, OpenMode.ForRead) as AttributeReference;
if ((attRef != null) && (attRef.Tag.ToUpper() == attName))
{
textString = attRef.TextString;
break;
}
}
}
tr.Commit();
}
return textString;
}
private static string GetUser(string User)
{
switch (User.ToUpper())
{
case "UA02038":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Hall";
default:
return @"No Path Defined";
}
Is there a test to see if the xlsx is open?Not sure if you mean excel(using a method instead try block?) or the file.
[CommandMethod("OpenExcelEarlyBind")]
public void OpenExcelEarlyBind()
{
ExcelApp.Application exl;
if (IsExcelRunning())
{
exl = (ExcelApp.Application)Marshal.GetActiveObject("Excel.Application");
}
else
{
exl = new Microsoft.Office.Interop.Excel.Application();
}
exl.Visible = true;
if (exl.Workbooks.Count < 1 )
{
exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
}
///////Will crash if you check and no workbooks are open and reason for precvious staement
if (exl.Workbooks[exl.Workbooks.Count].Name != "ExcelTest.xlsx")
{
exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
}
ExcelApp.Worksheet ws = exl.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
ws.Range["A1"].Value = DateTime.Now.ToLongTimeString();
Marshal.FinalReleaseComObject(exl);
}
bool IsExcelRunning()
{
Process[] pros = Process.GetProcesses();
foreach (var process in pros)
{
if (process.ProcessName == "EXCEL")
{
return true;
}
}
return false;
}
xlApp = (Excel.Application )GetOrCreateInstance("Excel.Application");
static object GetInstance(string appName)
{
return Marshal.GetActiveObject(appName);
}
static object CreateInstance(string appName)
{
return Activator.CreateInstance(Type.GetTypeFromProgID(appName));
}
static object GetOrCreateInstance(string appName)
{
try { return GetInstance(appName); }
catch { return CreateInstance(appName); }
}
if (!xLopen)
{
wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
wb = Microsoft.Office.Interop.Excel.Workbooks[strFilename + "xlsx"].Activate();
}
wb = xlApp.Workbooks[strFilename + "xlsx"];
wb.Activate();
[CommandMethod("TitleBlock")]
public void TitleBlock()
{
Document doc = Application.DocumentManager.MdiActiveDocument;
Database db = doc.Database;
Editor ed = doc.Editor;
using (Transaction trx = db.TransactionManager.StartTransaction())
{
ObjectId brefId = ed.GetEntity("\nSelect Block").ObjectId;
AttributedTextInfo atts = new AttributedTextInfo(brefId);
string val = atts["Test"];
Application.ShowAlertDialog(val);
string val2 = atts["Test2"];
Application.ShowAlertDialog(val2);
trx.Commit();
}
}
public class AttributedTextInfo : IEnumerable
{
private ObjectId _objectId;
private Dictionary<string, string> _attributes;
public AttributedTextInfo(ObjectId brefId)
{
_objectId = brefId;
_attributes = new Dictionary<string, string>();
getAttributes();
}
private void getAttributes()
{
using (Transaction tr = _objectId.Database.TransactionManager.StartTransaction())
{
BlockReference blockRef = tr.GetObject(_objectId, OpenMode.ForRead) as BlockReference;
if (blockRef != null)
{
foreach (ObjectId id in blockRef.AttributeCollection)
{
AttributeReference attRef = tr.GetObject(id, OpenMode.ForRead) as AttributeReference;
if ((attRef != null))
{
_attributes.Add(attRef.Tag, attRef.TextString);
}
}
}
tr.Commit();
}
}
public IEnumerator GetEnumerator()
{
return _attributes.GetEnumerator();
}
public string this[string tag]
{
get { return _attributes[tag.ToUpper()]; }
}
}
public static bool IsExcelRunning()
{
Process[] pros = Process.GetProcesses();
foreach (var process in pros)
{
if (process.ProcessName == "EXCEL")
{
return true;
}
}
return false;
}
LINK (http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.finalreleasecomobject.aspx)
The FinalReleaseComObject method releases the managed reference to a COM object. Calling this method is equivalent to calling the ReleaseComObject (http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.releasecomobject.aspx) method in a loop until it returns 0 (zero).
When the reference count on the COM object becomes 0, the COM object is usually freed, although this depends on the COM object's implementation and is beyond the control of the runtime. However, the RCW can still exist, waiting to be garbage-collected.
if (xLopen)
{
acApp.ShowAlertDialog("Excel will now close!");
}
wb.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
//GC.Collect(2);
//GC.Collect(2);
//GC.Collect(2);
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using acApp = Autodesk.AutoCAD.ApplicationServices.Application;
namespace ProjLog
{
public class Plog2012
{
[CommandMethod("PLog")]
public static void ProjectLog()
{
Document doc = acApp.DocumentManager.MdiActiveDocument;
Database db = doc.Database;
Editor ed = doc.Editor;
string DwgPath = Path.GetDirectoryName(acApp.DocumentManager.MdiActiveDocument.Name);
string ProjNum = DwgPath.Substring(DwgPath.LastIndexOf("\\") + 1);
PromptSelectionOptions pso = new PromptSelectionOptions();
TypedValue[] filter = new TypedValue[2] { new TypedValue((int)DxfCode.Start, "INSERT"), new TypedValue((int)DxfCode.BlockName, "VTEP-INFO,TEP-INFO,TEP-CIP,VTEP-CIP,VUES-INFO,UES-INFO,UES-CIP,VUES-CIP") };
SelectionFilter selFilter = new SelectionFilter(filter);
PromptSelectionResult psr = ed.SelectAll(selFilter);
if (psr.Status != PromptStatus.OK)
{
acApp.ShowAlertDialog("Invalid Titleblock in drawing!");
return;
}
string strDwgLine1 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE1");
string strDwgLine2 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE2");
string strDwgNum = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "NUM");
string strDwgVendor = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNAME");
string strDwgVendorNumber = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNUMBER");
string strUserPath = GetUser(System.Environment.UserName);
string strMyFile = string.Concat(strUserPath, "\\", ProjNum, "\\", ProjNum, ".xlsx");
string strFilename = string.Concat(ProjNum + ".xlsx");
Excel.Application xlApp;
bool xLopen = IsExcelRunning();
xlApp = (Excel.Application)GetOrCreateInstance("Excel.Application");
Workbook wb;
if (xlApp.Workbooks.Count < 1)
{
wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
{
wb = xlApp.Workbooks[strFilename];
}
Worksheet ws = wb.Sheets["Sheet1"] as Worksheet;
xlApp.Visible = true;
xlApp.UserControl = false ;
Excel.Range currentFind = null;
Excel.Range firstFind = null;
currentFind = (Excel.Range)ws.Columns["B", Type.Missing];
firstFind = currentFind.Find(strDwgNum, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, 0, 0, Type.Missing);
string foundAddress = firstFind.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
firstFind.Activate();
int rowNum = firstFind.Row;
if (!string.IsNullOrEmpty(strDwgVendor))
{
ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2 + "/" + strDwgVendor + "," + strDwgVendorNumber;
}
else
{
ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2;
}
ws.Cells[rowNum, 1] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "SHT");
ws.Cells[rowNum, 3] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "REV");
ws.Cells[rowNum, 4] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "CODE");
if (xLopen)
{
acApp.ShowAlertDialog("Excel will now close!");
}
wb.Close(true, Type.Missing, Type.Missing);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
//GC.Collect(2);
//GC.Collect(2);
//GC.Collect(2);
}
public static bool IsExcelRunning()
{
Process[] pros = Process.GetProcesses();
foreach (var process in pros)
{
if (process.ProcessName == "EXCEL")
{
return true;
}
}
return false;
}
static object GetOrCreateInstance(string appName)
{
try { return GetInstance(appName); }
catch { return CreateInstance(appName); }
}
static object GetInstance(string appName)
{
return Marshal.GetActiveObject(appName);
}
static object CreateInstance(string appName)
{
return Activator.CreateInstance(Type.GetTypeFromProgID(appName));
}
public static string GetAttributeValueFromBlock(Database db, ObjectId blockRefId, string attName)
{
string textString = null;
using (Transaction tr = db.TransactionManager.StartTransaction())
{
BlockReference blockRef = tr.GetObject(blockRefId, OpenMode.ForRead) as BlockReference;
if (blockRef != null)
{
foreach (ObjectId id in blockRef.AttributeCollection)
{
AttributeReference attRef = tr.GetObject(id, OpenMode.ForRead) as AttributeReference;
if ((attRef != null) && (attRef.Tag.ToUpper() == attName))
{
textString = attRef.TextString;
break;
}
}
}
tr.Commit();
}
return textString;
}
private static string GetUser(string User)
{
switch (User.ToUpper())
{
case "UA02038":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Hall";
case "UA50151":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Richardson";
case "UA00648":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Livingstone";
case "UA50270":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Dominguez";
case "UA00071":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Taylor";
case "UA50050":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Lopez";
case "UA00664":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Olivas";
case "UA50463":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Padilla";
case "UA50271":
return @"\\tuslpna01\autocad\Reproductions_Area\000-ALVIRA";
case "UA51273":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Heritage";
default:
return @"No Path Defined";
}
}
}
}