Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sunny_talwar

Macro for Border

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

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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

View solution in original post

20 Replies
sunny_talwar
Author

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

Anil_Babu_Samineni

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")

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
marcus_sommer

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

sunny_talwar
Author

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.

marcus_sommer

Hi Sunny,

the sheet-reference is further missing:

XLSheet.Range("C12").Select

- Marcus

sunny_talwar
Author

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

Capture.PNG

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?

tamilarasu
Champion
Champion

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.

tamilarasu
Champion
Champion

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

sunny_talwar
Author

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.