Excel Export Border

[#1]

Hi,

does anyone know how I can set the borderstyle and color of an range?

Excel Macro Recorder returned this:

Range("A1:O22").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With

Using this I get a selection is required error.

I also tried this:

With xlSheet
.Cells.Borders.LineStyle = 1
.PageSetup.Orientation = 2
.AutoFilterMode = False
End With

-> looks good so far but makes border around all cells 😞

Another problem I have is that sometimes a second excel document .

This file has no filename (e.g. sheet1) and . Seems as somehting is cached?

does somebody know what's the reason for this?

My Macro looks like this:

function ExcelCopy(path)

set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False
set XLDoc = XLApp.Workbooks.Add
set XLSheet = XLDoc.Worksheets(1)

XLSheet.Range("A1").select

'Copy First Table and paste it to xcel sheet
set obj = ActiveDocument.getsheetobject("CH25")
objname = obj.getCaption.name.v
obj.CopyTableToClipboard true


XLSheet.paste


XLSheet.cells.select

'Resize Excel Rows and Columns
XLSheet.Cells.EntireColumn.AutoFit
XLSheet.Cells.EntireRow.AutoFit


'Set Header Row height
XLSheet.Rows("1:1").RowHeight = 25.5

'add Autofilter

XLSheet.Range("A1").AutoFilter

'set to print landscape

'With XLSheet.PageSetup
'.Orientation = 2 'xlLandscape
'.FitToPagesWide = 1
'.FitToPagesTall = False
'.PaperSize = 9 ' xlApp.xlPaperA4

With xlSheet
.Cells.Borders.LineStyle = 1
.PageSetup.Orientation = 2
.AutoFilterMode = False
End With



'XLSheet.Rows("2:2").Select
'XLApp.ActiveWindow.FreezePanes = True

'XLSheet.Range("E2").Select
'XLApp.ActiveWindow.FreezePanes = True


XLDoc.SaveAs path
XLApp.Quit
End function

Thanks in advance.

Aloah

[#2]

I'm a little confused as to what you're trying to do and what is happening.

You seem to have a solid setup there in your macro. I don't see anything obvious.

You mention all cells getting borders and using Cells will apply to all cells.. Are you trying to get borders around only certain cells?

You also mention a selection required error. I usually select the cells I want to edit:

.Cells.Select
.Selection.Borders(10).LineStyle = 1

That would give every cell a right border.

To make a specific selection:

.Range("A1").Select
.Selection.Borders(10).LineStyle = 1

This would give only A1 a right border.

Also, the problem you describe where you're getting an extra sheet sounds like what happens when the macro crashed in the middle of the process and can't close out the file correctly. Sometimes, I'll see another document or have problems opening or deleting the Excel document.

[#3]

Hi,

thanks for your help.
I tried with:

With xlSheet
.Range("A1").Select
.Selection.Borders(10).LineStyle = 1
End With

I'm getting an error: Object dosen't support this property or method: 'Selection''

Sorry I' quite new to macros.

[#4]

Ciao!

Begins by declaring these constants

'*********************************************
' Constants for the Document.
'*********************************************
'
' For EXCEL
'
Const xlContinuous = 1
Const xlDiagonalDown = 5
Const xlDiagonalUp = 6
Const xlEdgeLeft = 7
Const xlEdgeTop = 8
Const xlEdgeBottom = 9
Const xlEdgeRight = 10
Const xlInsideVertical = 11
Const xlInsideHorizontal = 12
Const xlHairLine = 1
Const xlSolid = 1
Const xlThin = 2
Const xlAutomatic = -4105
Const xlCenter = -4108
Const xlNone = -4142
Const xlDown = -4121
Const xlUp = -4162
Const xlMedium = -4138
Const xlLeft = -4131
Const xlRight = -4152
Const xlToLeft = -4159
Const xlToRight = -4161
Const xlShiftToLeft = -4159
Const xTop = -4160
Const xlLastCell = 11
Const xlAscending = 1
Const xlGuess = 0
Const xlTopToBottom = 1
Const xlSortNormal = 0

Now look what you can come in handy (excuse my English .... is that of Google)

Function BordiExcel(XLsheet, sRange, bVert, bOrr)
XLsheet.Range(sRange).Borders(xlEdgeLeft).LineStyle = xlContinuous
XLsheet.Range(sRange).Borders(xlEdgeTop).LineStyle = xlContinuous
XLsheet.Range(sRange).Borders(xlEdgeBottom).LineStyle = xlContinuous
XLsheet.Range(sRange).Borders(xlEdgeRight).LineStyle = xlContinuous
if bVert ="S" then
XLsheet.Range(sRange).Borders(xlInsideVertical).LineStyle = xlContinuous
end if
if bOrr ="S" then
XLsheet.Range(sRange).Borders(xlInsideHorizontal).LineStyle = xlContinuous
end if
end Function

XLSheet.Range("E1") = "DANIELA"
XLSheet.Range("E1").Font.Bold = True
XLsheet.Range("E1").Font.Size = 13
XLsheet.Range("E1:K1").MergeCells = True

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).Weight = xlThin
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).ColorIndex = xlAutomatic
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).WrapText = True
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).HorizontalAlignment = xlCenter
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).VerticalAlignment = xlCenter

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Interior.ColorIndex = 40

XLsheet.Cells.EntireColumn.AutoFit
XLsheet.Cells.EntireRow.AutoFit
XLsheet.name = vChi

XLsheet.PageSetup.Orientation = 2 'Landscape
XLsheet.PageSetup.LeftMargin = xlApp.CentimetersToPoints(1)
XLsheet.PageSetup.RightMargin = xlApp.CentimetersToPoints(1)
XLsheet.PageSetup.HeaderMargin = xlApp.CentimetersToPoints(0.5)
XLsheet.PageSetup.TopMargin = xlApp.CentimetersToPoints(1.3)
XLsheet.PageSetup.BottomMargin = xlApp.CentimetersToPoints(1.3)
XLsheet.PageSetup.FooterMargin = xlApp.CentimetersToPoints(0.5)
XLsheet.PageSetup.PrintArea = "$A$1:$" &sLett&"$"&iRighe+2
XLsheet.PageSetup.PrintTitleRows = "$1:$5"
XLsheet.PageSetup.PrintTitleColumns = "$A:$" &sLett
XLsheet.PageSetup.CenterHeader = vSheetObj.GetCaption.Name.v
XLsheet.PageSetup.RightFooter = "Pag. &P di &N"
XLsheet.PageSetup.LeftFooter = "Generato il " & Date & " alle " & Time
XLsheet.PageSetup.LeftHeader = "&G"

Ciao! Ciao!

Daniela

[#5]

Oh, sorry, it looks like QlikView does not like the Selections. Instead, you can just continue to use the range:

With xlSheet.Range("A5").Borders(7)
 .LineStyle = 1
 .Weight = 2
 .ColorIndex = -4105
 End With

Basically, instead of using .Range().Select and then .Selection, just use .Range()...

[#6]

Hello!
Begins with the state these constants!
(excuse my English ..... coming from google)

'*********************************************
' Constants for the Document.ShowPopup options.
'*********************************************
'
' For EXCEL
'
Const xlContinuous = 1
Const xlDiagonalDown = 5
Const xlDiagonalUp = 6
Const xlEdgeLeft = 7
Const xlEdgeTop = 8
Const xlEdgeBottom = 9
Const xlEdgeRight = 10
Const xlInsideVertical = 11
Const xlInsideHorizontal = 12
Const xlHairLine = 1
Const xlSolid = 1
Const xlThin = 2
Const xlAutomatic = -4105
Const xlCenter = -4108
Const xlNone = -4142
Const xlDown = -4121
Const xlUp = -4162
Const xlMedium = -4138
Const xlLeft = -4131
Const xlRight = -4152
Const xlToLeft = -4159
Const xlToRight = -4161
Const xlShiftToLeft = -4159
Const xTop = -4160
Const xlLastCell = 11
Const xlAscending = 1
Const xlGuess = 0
Const xlTopToBottom = 1
Const xlSortNormal = 0

Now look what you can come in handy (excuse my English .... is that of Google)

Function BordiExcel(XLsheet, sRange, bVert, bOrr)
XLsheet.Range(sRange).Borders(xlEdgeLeft).LineStyle = xlContinuous
XLsheet.Range(sRange).Borders(xlEdgeTop).LineStyle = xlContinuous
XLsheet.Range(sRange).Borders(xlEdgeBottom).LineStyle = xlContinuous
XLsheet.Range(sRange).Borders(xlEdgeRight).LineStyle = xlContinuous
if bVert ="S" then
XLsheet.Range(sRange).Borders(xlInsideVertical).LineStyle = xlContinuous
end if
if bOrr ="S" then
XLsheet.Range(sRange).Borders(xlInsideHorizontal).LineStyle = xlContinuous
end if
end Function

XLSheet.Range("E1") = "DANIELA"
XLSheet.Range("E1").Font.Bold = True
XLsheet.Range("E1").Font.Size = 13
XLsheet.Range("E1:K1").MergeCells = True

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).Weight = xlThin
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).ColorIndex = xlAutomatic
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).WrapText = True
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).HorizontalAlignment = xlCenter
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).VerticalAlignment = xlCenter

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Interior.ColorIndex = 40

XLsheet.Cells.EntireColumn.AutoFit
XLsheet.Cells.EntireRow.AutoFit
XLsheet.name = vChi

XLsheet.PageSetup.Orientation = 2 'Landscape
XLsheet.PageSetup.LeftMargin = xlApp.CentimetersToPoints(1)
XLsheet.PageSetup.RightMargin = xlApp.CentimetersToPoints(1)
XLsheet.PageSetup.HeaderMargin = xlApp.CentimetersToPoints(0.5)
XLsheet.PageSetup.TopMargin = xlApp.CentimetersToPoints(1.3)
XLsheet.PageSetup.BottomMargin = xlApp.CentimetersToPoints(1.3)
XLsheet.PageSetup.FooterMargin = xlApp.CentimetersToPoints(0.5)
XLsheet.PageSetup.PrintArea = "$A$1:$" &sLett&"$"&iRighe+2
XLsheet.PageSetup.PrintTitleRows = "$1:$5"
XLsheet.PageSetup.PrintTitleColumns = "$A:$" &sLett
XLsheet.PageSetup.CenterHeader = vSheetObj.GetCaption.Name.v
XLsheet.PageSetup.RightFooter = "Pag. &P di &N"
XLsheet.PageSetup.LeftFooter = "Generato il " & Date & " alle " & Time
XLsheet.PageSetup.LeftHeader = "&G"

Ciao! Ciao!

Daniela

[#7]

Hello!

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).Weight = xlThin
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).ColorIndex = xlAutomatic
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeLeft).LineStyle = xlContinuous
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeLeft).Weight = xlMedium
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeTop).LineStyle = xlContinuous
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeTop).Weight = xlMedium
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeBottom).LineStyle = xlContinuous
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeBottom).Weight = xlMedium
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeRight).LineStyle = xlContinuous
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeRight).Weight = xlMedium
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).LineStyle = xlContinuous

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).WrapText = True
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).HorizontalAlignment = xlCenter
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).VerticalAlignment = xlCenter

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Interior.ColorIndex = 40

Ciao!

Daniela

[#8]

Do you know the number of Cons xlDouble= ?

[prat1507]

Hi Daniela

I've been looking for something similar but the code you've written does not seem to work, if it worked for you, please share a sample .qvw.

Regards

Pratyush