Excel border style

Revit
, ,
1

Hello

I’m trying to manipulate the borderlines of a cellrange in excel. Is it possible to alter the bottom borderline only? If it’s not possible with Dynamo could someone help me to get the code for Python.

image

2

Do you know how to use .NET codes in Python?
I could do it but now I don’t have much time…
You should open a workbook and then worksheet and then define range with .NET functions. After that use for example
ActiveSheet.Range(“A7:H7”).Borders = 9

3

I don’t have experience with that, but it’s great to see that it’s probably possible :clap:
I’ll try to figure out what .NET functions are.

4 
import clr
import sys
import System
from System import Array
from System.Collections.Generic import *

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo("en-US")
from System.Runtime.InteropServices import Marshal
		

excel = Excel.ApplicationClass()
excel.Visible = False
path , c_range = IN
wb = excel.Workbooks.Open(path)
ws = wb.ActiveSheet

wb.ActiveSheet.Range("A7:H7").Borders.Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous


excel.ActiveWorkbook.Save()
excel.ActiveWorkbook.Close(True)

Excel.Application.Quit


OUT= "Alles OK"
3 Likes
5

Thanks for your help!!

I have little experience with Python nodes, so what do I need to make it work.
I’ve created a Python node and replaced the code with your code and assumed that for the IN part a path node was needed.

Is that correct?

Knipsel
Knipsel1258×866 61.8 KB

6

Ok I figure that you have to create a custom node, so I did:
Knipsel2

What’s the next step?

7

I had also as input the range so add another input.
c_range and path I added as tuple. You can also add like that:
path = IN[0]
c_range = IN[1]
You can assign the range via INPUT.

1 Like
8

c_rrange
c_rrange1027×676 40.8 KB

Do like that and edit code Range("A7:H7") instead of “A7:H7” type c_range.

2 Likes
9

Thanks for your great help. It works!! :beers:

1 Like
11

I want to fead the python code with a list of ranges instead of one range only. What do I need to change on the Python code to make it work? :face_with_monocle:

12 
import clr
import sys
import System
from System import Array
from System.Collections.Generic import *

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo("en-US")
from System.Runtime.InteropServices import Marshal
		

excel = Excel.ApplicationClass()
excel.Visible = False
path , c_range = IN
wb = excel.Workbooks.Open(path)
ws = wb.ActiveSheet

for rng in c_range:

	wb.ActiveSheet.Range(rng).Borders.Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous


excel.ActiveWorkbook.Save()
excel.ActiveWorkbook.Close(True)

Excel.Application.Quit


OUT= "Alles OK"
1 Like
13

Great, thanks! … I really need to learn Python.

1 Like