Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
bnelson111
Creator II
Creator II
‎2017-06-02 11:49 AM

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

1,225 Views
0 Likes
9 Replies
m_woolf
Master II
Master II
‎2017-06-02 12:33 PM

Are you getting an error with Excel 2010?

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

985 Views
0 Likes
el_aprendiz111
Specialist
Specialist
‎2017-06-02 04:02 PM

Hi,

set XLSheet = XLDoc.Worksheets(1)

MAYUSC COLUMN:

Range("S"& 2).Select

985 Views
0 Likes
fkeuroglian
Partner - Master
Partner - Master
‎2017-06-02 08:14 PM

Hi Brett

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

Thanks

985 Views
0 Likes
bnelson111
Creator II
Creator II
‎2017-06-03 04:58 AM
Author

In response to m_woolf

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.

985 Views
0 Likes
bnelson111
Creator II
Creator II
‎2017-06-03 04:58 AM
Author

In response to fkeuroglian

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.

985 Views
0 Likes
bnelson111
Creator II
Creator II
‎2017-06-05 04:36 AM
Author

In response to el_aprendiz111

?

MAYUSC COLUMN:

Range("S"& 2).Select

985 Views
0 Likes
bnelson111
Creator II
Creator II
‎2017-06-05 04:37 AM
Author

In response to fkeuroglian

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

985 Views
0 Likes
bnelson111
Creator II
Creator II
‎2017-06-05 04:37 AM
Author

In response to m_woolf

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

985 Views
0 Likes
bnelson111
Creator II
Creator II
‎2017-06-05 05:17 AM
Author

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

985 Views
0 Likes