Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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")
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
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.
Hi Sunny,
the sheet-reference is further missing:
XLSheet.Range("C12").Select
- Marcus
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?
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.
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
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.