BORDER CELLS CANNOT BE MODIFIED WITH THE MACRO

[#1]

Hi everyone! I'm totally desparate...I've just wrote a macro to export and format the qlikview report

sub Export5

    nomefile = "C:\Evolve\Export\File5.xlsx"

    set FSO = CreateObject("Scripting.FileSystemObject")

    If FSO.FileExists(nomefile) = True Then

        FSO.DeleteFile nomefile

    End If

    Set FSO = Nothing

set XLApp = CreateObject("Excel.Application")

  XLapp.Visible = true

  set XLDOC = XLApp.Workbooks.Add

  XLDoc.Sheets(1).name = "Export"

  set XLSheet = XLDoc.Worksheets(1)

  set myTable = ActiveDocument.GetSheetObject("CH702")

  set XLSheet = XLDoc.Worksheets(1)

  Mytable.CopyTableToClipboard true

  XLSheet.Paste XLSheet.Range("A1")

XLSheet.Columns("A:AZ").EntireColumn.NumberFormat = "[>=1000000]#,###,##0;[>=1000]#,##0;#"

if XLSheet.Cells.Font.ColorIndex <> 0 Then cell.Borders.LineStyle = xlContinuous

if XLSheet.Cells.Font.ColorIndex <> 0 Then cell.Borders.ColorIndex=0

    XLSheet.Cells.Entirerow.AutoFit

  XLSheet.saveas nomefile

  set XLSheet = nothing

  XLDOC.close

  set XLDOC = nothing

  XLApp.quit

  Set XLApp = nothing

end sub

I need that the table exported in excel has its own formatting! the code in bold works perfectly, while the codes underlined and in italics don't!

I've tried them in excel and they work fine...is there a reason why qlikview doesn't allow to format tables' lines????

[jerrysvensson]

Hi, I can't see a definition of xlContinuous in your code.

[Gysbert_Wassenaar]

Vbscript doesn't know the constants like xlContinuous. You need to define these first (xlContinuous = 1;) or just use the actual value 1 instead.

See here for more information: nerds-central.blogspot.nl/2007/01/setting-excel-borders-from-vbscript-or.html

[#4]

thanks Gysbert for your link, but it hasn't solved my problem! even if I set xlContinuous =1, still when I export from QV my macro doesn't draw any border!

[jerrysvensson]

Can you give us the code you are using now?

[#6]

Jerry Svensson here is the code. I've tried to rearrange the xlContinuous as you suggested, but the main problem persists! I cannot draw a black border around my cells

sub Export5

    nomefile = "C:\Evolve\Export\File5.xlsx"

    set FSO = CreateObject("Scripting.FileSystemObject")

    If FSO.FileExists(nomefile) = True Then

        FSO.DeleteFile nomefile

    End If

    Set FSO = Nothing

  set XLApp = CreateObject("Excel.Application")

  XLapp.Visible = true

  set XLDOC = XLApp.Workbooks.Add

  XLDoc.Sheets(1).name = "Export"

  set XLSheet = XLDoc.Worksheets(1)

  set myTable = ActiveDocument.GetSheetObject("CH702")

  set XLSheet = XLDoc.Worksheets(1)

  Mytable.CopyTableToClipboard true

  XLSheet.Paste XLSheet.Range("A1")

  XLSheet.Columns("A:AZ").EntireColumn.NumberFormat = "[>=1000000]#,###,##0;[>=1000]#,##0;#"

  if XLSheet.Cells.Font.ColorIndex <> 0 Then cell.Borders.LineStyle.xlContinuous=1

    if XLSheet.Cells.Font.ColorIndex <> 0 Then cell.Borders.ColorIndex=-4105

    XLSheet.Cells.Entirerow.AutoFit

  XLSheet.saveas nomefile

  set XLSheet = nothing

  XLDOC.close

  set XLDOC = nothing

  XLApp.quit

  Set XLApp = nothing

end sub

[Gysbert_Wassenaar]

Try cell.Borders.LineStyle=1

[jerrysvensson]

Old:

if XLSheet.Cells.Font.ColorIndex <> 0 Then cell.Borders.LineStyle = xlContinuous

New:

if XLSheet.Cells.Font.ColorIndex <> 0 Then cell.Borders.LineStyle.xlContinuous=1

Shouldn't it be:

if XLSheet.Cells.Font.ColorIndex <> 0 Then cell.Borders.LineStyle=1

[#9]

still not working and it also gives me the same result...

[#10]

no difference...as said to Gysbert, it doesn't change anything if I write cell.Borders.LineStyle=1