Macro for Border

[sunny_talwar]

Hi Team -

Working on a Macro to assign a border to a particular cell. The Macro is as follows

Sub Macro1()

    Range("C12").Select

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlDouble

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThick

    End With

End Sub

This seems to work in Excel, but fails to work in QlikView's VBScript.

Will try to create a sample to show my issue.

marcus_sommer‌, tamilarasu‌, phaneendra.kunche‌

Best,

Sunny

[sunny_talwar]

Look at my response below

[tamilarasu]

Are you seeing this Module window everytime you run the macro? Usually, Qlikview will show this window only when the code try to access excel or other programs (If you select "Allow System Access" , you will not see it again) outside and whenever you make some changes in the macro.

[sunny_talwar]

Yes, everytime I click on the button Export, it creates the Excel file, but opens up the Edit Module also. But I do want to clarify that this doesn't happen when I use just this

Public Sub Export

  Dim objExcelApp

  Dim objExcelDoc

  Set objExcelApp = CreateObject("Excel.Application")

  objExcelApp.Visible = true

  objExcelApp.DisplayAlerts = false

  Set objExcelDoc = objExcelApp.Workbooks.Add

  Set n = SetExportArray("CH01", "Sheet1")

  Set n = SetExportArray("CH02", "Sheet2")

    Set ignored = CopyObjectsToExcelSheet(ActiveDocument, gExportArray, objExcelApp, objExcelDoc, "N", "N")

  'Format numbers in Excel

  Set XLSheet = objExcelApp.WorkSheets("Sheet1")

  XLSheet.Columns(1).EntireColumn.Delete

  XLSheet.Cells.EntireColumn.AutoFit

    XLSheet.Columns("B:Z").NumberFormat = "_($#,##0.00_);_(($#,##0.00);_(* ""-""??_);_(@_)"

'    Range("C12").Select

'    With Selection.Borders(8)

'        .LineStyle = 1

'        .ColorIndex = 0

'        .TintAndShade = 0

'        .Weight = 2

'    End With

'    With Selection.Borders(9)

'        .LineStyle = -4119

'        .ColorIndex = 0

'        .TintAndShade = 0

'        .Weight = 4

'    End With

    Set XLSheet = objExcelApp.WorkSheets("Sheet2")

  XLSheet.Columns(1).EntireColumn.Delete

  XLSheet.Cells.EntireColumn.AutoFit

    XLSheet.Columns("B:Z").NumberFormat = "_($#,##0.00_);_(($#,##0.00);_(* ""-""??_);_(@_)"

  'Set SetExportArray = nothing

  'Finally select sheet and delete blank sheets

  objExcelDoc.Sheets(1).Select

  Call Excel_DeleteBlankSheets(objExcelDoc)

  msgbox("Sheet1 & Sheet2 Created!")

End Sub

So, basically when border part is commented out, Macro runs like a charm without Edit Module opening up. It is only when I add formatting to borders is when I run into issues.

Also, I do have the 'Allow System Access' selected

[marcus_sommer]

Within Tamil's example these lines cause an error and open the modul-window:

objExcelDoc.Worksheets("Sheet4").Name ="Sheet1"

objExcelDoc.Worksheets("Sheet5").Name ="Sheet2"

but the border-formatting worked.

In an older application from me I used this:

for iFormatTyp = 1 to RowThree - 1
     msgstr = msgstr & iObject & ": " & CellMatrixTwo(iObject)(1).Text & chr(10) & CellMatrixTwo(iObject)(11).Text & " vs. " & SheetCounter & chr(10)
      if CellMatrixTwo(iObject)(10).Text = CellMatrixThree(iFormatTyp)(0).Text and int(CellMatrixTwo(iObject)(11).Text) = SheetCounter then
           arrFormatValues = split(CellMatrixThree(iFormatTyp)(3).Text, ",")
           for iFormatValues = 0 to ubound(arrFormatValues)
                if CellMatrixThree(iFormatTyp)(2).Text = "ColumnWidth" then
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Columns(iFormatValues + 1).ColumnWidth = arrFormatValues(iFormatValues)
                elseif CellMatrixThree(iFormatTyp)(2).Text = "MergeCells" then
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Select
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).MergeCells = true
                elseif CellMatrixThree(iFormatTyp)(2).Text = "Font.Bold" then
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Select
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Font.Bold = true
                elseif CellMatrixThree(iFormatTyp)(2).Text = "Font.Color" then
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Select
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Font.Color = rgb(255,0,0)
                elseif CellMatrixThree(iFormatTyp)(2).Text = "MergeCells" then
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Select
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).MergeCells = rgb(200,200,200)
                elseif CellMatrixThree(iFormatTyp)(2).Text = "Border" then
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Select
                     objExcelDoc.sheets(CellMatrixTwo(iObject)(1).Text).Range(arrFormatValues(iFormatValues)).Borders.LineStyle = 1'-4118
                end if
             next
      end if
next

whereby it could be more elegant because iFormatTyp)(2).Text corresponds to the vba-statement and could be therefore applied within another array - but after it worked I didn't want to change it again (the macro itself isn't very beautiful either).

- Marcus

[tamilarasu]

Strange. It shows me only the first time. Have you tried the below method. This is working fine in my system.

.

[sunny_talwar]

In fact after the 1st time it not only opens the Edit Module window, but it also doesn't populate the Excel file.

[sunny_talwar]

Marcus -

Would I be able to copy paste this into my application or do you expect me to make changes here? The reason I ask is because I don't understand the vbscript very well and would require your help in areas where I would need to make modifications in the above code.

Thanks,

Sunny

[marcus_sommer]

Hi Sunny,

I don't think that a copy + paste from the snippet would be very helpful because it used several nested loops through arrays to access the right sheets and cells and apply various formattings on them. The meaning was more to give ideas which could be adapted and to show how such border-formatting could be applied whereby it here worked without a sheet-reference else with a document-reference and sheet and cell-area are directly added to them.

Have you tried Tamil's example without these lines?

objExcelDoc.Worksheets("Sheet4").Name ="Sheet1"

objExcelDoc.Worksheets("Sheet5").Name ="Sheet2"

This should be working otherwise it would be useful if you prepared a small example of your app.

- Marcus

[sunny_talwar]

Testing it now

[sunny_talwar]

Yes that did remove the error, now I have figure out how to implement this in my code. Thanks Marcus and Tamil, I think I should be able to carry this forward from here for the border thing. Although, I do have another issue for which I might open another thread once I get this resolved.

Best,

Sunny