topic Re: Macro for Border in QlikView

Macro for Border

sunny_talwar — Wed, 04 Jan 2017 01:19:29 GMT

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

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 01:23:20 GMT

Just to add, this is a small piece of a macro that I have been working on. The whole Macro looks like 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(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

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

Re: Macro for Border

Anil_Babu_Samineni — Wed, 04 Jan 2017 06:19:11 GMT

I am not expert in Macro. But, It might tempo error can you check this part please

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

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

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

Re: Macro for Border

marcus_sommer — Wed, 04 Jan 2017 07:59:30 GMT

Hi Sunny,

like Anil mentioned here might be an issue:

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

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

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

but this isn't the problem with the borders. I think here is vbs missing the sheet-reference like:

XLSheet.Range("C12").Select

Further I'm not sure that vbs will handle the excel-constantes like xlEdgeTop properly and replace them through their numerical value:

With Selection.Borders(8)

Here are various examples for such constantes and their numerical values: Office Space: Putting Borders Around Spreadsheet Cells

Another thing which I would comment out or setting to true (for the testings) is:

objExcelApp.DisplayAlerts

which might prevent useful messages (and it should be set to true before finishing this routine).

- Marcus

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 09:58:38 GMT

Hi Marcus -

Thanks for your response. I have tried making all the changes you have suggested, but it is still not doing the border for me for some reason. Here is a new code

Public Sub Export

Dim objExcelApp

Dim objExcelDoc

Set objExcelApp = CreateObject("Excel.Application")

objExcelApp.Visible = true

objExcelApp.DisplayAlerts = TRUE

Set objExcelDoc = objExcelApp.Workbooks.Add

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

Set n2 = 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

Although, I did make the other changes as you suggested, my problem is just this part

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

If I remove this, the Macro works flawlessly, but if I add it back any code after that won't execute and the Edit Module windows open up without any errors.

Re: Macro for Border

marcus_sommer — Wed, 04 Jan 2017 10:01:43 GMT

Hi Sunny,

the sheet-reference is further missing:

XLSheet.Range("C12").Select

- Marcus

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 10:07:08 GMT

Marcus, in order to avoid some of the sheet names in my original code, I copy pasted the code from above and made the same changes I made in my actual code. I did make this change in my original code

This is/was still not working.... Seems like something is wrong here. I don't see any error showing which might help me resolve this. Do you think a sample might help you find the issue?

Re: Macro for Border

tamilarasu — Wed, 04 Jan 2017 10:20:16 GMT

Hi Sunny,

Sorry for the late reply. Jus now saw your question and attached is my version of solution. Markus has suggested the right way. We need to mention (VB Script) the constant values directly instead of constant name like xlContinuous, xlThin. If you find any issues, kindly post the full code. We will look into it.

Re: Macro for Border

tamilarasu — Wed, 04 Jan 2017 10:26:58 GMT

Hi Sunny,

Try like this,

Set Rng = XLSheet.Range("C12")

With Rng.Borders(8)

.LineStyle = 1

.ColorIndex = 0

.TintAndShade = 0

.Weight = 2

End With

With Rng.Borders(9)

.LineStyle = -4119

.ColorIndex = 0

.TintAndShade = 0

.Weight = 4

End With

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 10:27:22 GMT

Tamil -

Although your code does end up showing the borders, I wonder why it still opens up the Edit Module window when I click on Export? Are you seeing the same problem when you click on Export button? Also, the original code is still not working for some reason. I am trying to work on a sample and will see if I can post it soon.

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 10:28:13 GMT

Look at my response below

Re: Macro for Border

tamilarasu — Wed, 04 Jan 2017 10:39:48 GMT

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.

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 10:45:23 GMT

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

Re: Macro for Border

marcus_sommer — Wed, 04 Jan 2017 10:50:09 GMT

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

Re: Macro for Border

tamilarasu — Wed, 04 Jan 2017 10:55:49 GMT

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

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 11:41:52 GMT

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

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 11:43:51 GMT

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

Re: Macro for Border

marcus_sommer — Wed, 04 Jan 2017 12:10:02 GMT

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

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 12:29:01 GMT

Testing it now

Re: Macro for Border

sunny_talwar — Wed, 04 Jan 2017 12:31:23 GMT

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