Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Read about Qlik’s Response to COVID-19 Read the Letter, Join the Group.
Contributor

VB Script.

Please see below a VB script i wrote to copy charts into a excel sheet so that we could then copy into a png or slide.

It works 100% in excel 2013 however 2010 seems to fall over. Sorry for the spacing.

Sub f()

Dim vSheet

Set XLApp = CreateObject("Excel.Application")

XLApp.Visible = TRUE

Set XLDoc = XLApp.Workbooks.Add

set XLSheet = XLDoc.Worksheets("Sheet1") 'sheet to have name changed 

 

XLSheet.Name = "ScoreCard Export"

vSheet = ""

vSheet = "ScoreCard Export"

ActiveDocument.GetSheetByID("SH02").Activate

ActiveDocument.GetApplication.WaitForIdle

XLDoc.Sheets(vSheet).Range("A" & 1).Select

ActiveDocument.GetSheetObject("TX46").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("f" & 2).Select

ActiveDocument.GetSheetObject("TX42").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("s" & 2).Select

ActiveDocument.GetSheetObject("TX41").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 13).Select

ActiveDocument.GetSheetObject("CH14").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

'XLDoc.Sheets(vSheet).Range("A" & 19).Select

'ActiveDocument.GetSheetObject("CH55").CopyBitmapToClipboard 'Copy the charts

'XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 21).Select

ActiveDocument.GetSheetObject("CH18").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 23).Select

ActiveDocument.GetSheetObject("CH62").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 24).Select

ActiveDocument.GetSheetObject("CH22").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 25).Select

ActiveDocument.GetSheetObject("CH31").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 27).Select

ActiveDocument.GetSheetObject("CH28").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 29).Select

ActiveDocument.GetSheetObject("CH36").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 30).Select

ActiveDocument.GetSheetObject("CH43").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 31).Select

ActiveDocument.GetSheetObject("CH46").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 33).Select

ActiveDocument.GetSheetObject("CH25").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 36).Select

ActiveDocument.GetSheetObject("CH37").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

XLDoc.Sheets(vSheet).Range("A" & 38).Select

ActiveDocument.GetSheetObject("CH42").CopyBitmapToClipboard 'Copy the charts

XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap

  set Selection =   XLDoc.Sheets(vSheet).Rows("22")

With Selection

.EntireRow.RowHeight = 12.0

End With

set Selection =   XLDoc.Sheets(vSheet).Rows("18")

With Selection

.EntireRow.RowHeight = 8.25

End With

set Selection =   XLDoc.Sheets(vSheet).Rows("20")

With Selection

.EntireRow.RowHeight = 13.5

End With

set Selection =   XLDoc.Sheets(vSheet).Rows("26")

With Selection

.EntireRow.RowHeight = 15.75

End With

set Selection =   XLDoc.Sheets(vSheet).Rows("28")

With Selection

.EntireRow.RowHeight = 12.0

End With

  set Selection =   XLDoc.Sheets(vSheet).Rows("32")

With Selection

.EntireRow.RowHeight = 15.0

End With

set Selection =   XLDoc.Sheets(vSheet).Rows("35")

With Selection

.EntireRow.RowHeight = 20.50

End With

set Selection =   XLDoc.Sheets(vSheet).Rows("37")

With Selection

.EntireRow.RowHeight = 12.75

End With

XLDoc.Sheets(vSheet).Shapes.SelectAll

Set XLDoc = Nothing

Set XLApp = Nothing

End Sub

9 Replies
Highlighted
Honored Contributor II

Re: VB Script.

Are you getting an error with Excel 2010?

Is the script failing? If so, on what line?

Highlighted
Valued Contributor

Re: VB Script.

Hi,

set XLSheet = XLDoc.Worksheets(1)

MAYUSC COLUMN:

Range("S"& 2).Select

Highlighted
Partner
Partner

Re: VB Script.

Hi Brett

What kind of error do you have? any print or message?

Thanks

Highlighted
Contributor

Re: VB Script.

It usually pops up with unexpected error but on different lines. Sometimes it will also run to the end but the excel sheet will not be populated. Its never the same. I was wondering was it a speed issue on PC and excel. Or maybe my script could be improved.

Highlighted
Contributor

Re: VB Script.

It usually pops up with unexpected error but on different lines. Sometimes it will also run to the end but the excel sheet will not be populated. Its never the same. I was wondering was it a speed issue on PC and excel. Or maybe my script could be improved.

Highlighted
Contributor

Re: VB Script.

?

MAYUSC COLUMN:

Range("S"& 2).Select

Highlighted
Contributor

Re: VB Script.

Object required: 'ActiveDocument.GetSheetObject(...)'

Highlighted
Contributor

Re: VB Script.

Object required: 'ActiveDocument.GetSheetObject(...)'

Highlighted
Contributor

Re: VB Script.

Sorry found issue a chart had renamed?? My fault thanks everyone.