topic Re: VB Script. in QlikView

topic Re: VB Script. in QlikView https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304104#M834100 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-size: 13.3333px;">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.</SPAN></P></BODY></HTML> Sat, 03 Jun 2017 08:58:29 GMT bnelson111 2017-06-03T08:58:29Z VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304099#M834095 <HTML><HEAD></HEAD><BODY><P>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.</P><P></P><P>It works 100% in excel 2013 however 2010 seems to fall over. Sorry for the spacing.</P><P></P><P></P><P>Sub f()</P><P>Dim vSheet</P><P>Set XLApp = CreateObject("Excel.Application")</P><P>XLApp.Visible = TRUE</P><P>Set XLDoc = XLApp.Workbooks.Add</P><P></P><P></P><P></P><P></P><P>set XLSheet = XLDoc.Worksheets("Sheet1") 'sheet to have name changed  </P><P>  </P><P>XLSheet.Name = "ScoreCard Export" </P><P></P><P></P><P></P><P></P><P>vSheet = ""</P><P>vSheet = "ScoreCard Export"</P><P></P><P></P><P></P><P>ActiveDocument.GetSheetByID("SH02").Activate</P><P>ActiveDocument.GetApplication.WaitForIdle</P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 1).Select</P><P>ActiveDocument.GetSheetObject("TX46").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("f" & 2).Select</P><P>ActiveDocument.GetSheetObject("TX42").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("s" & 2).Select</P><P>ActiveDocument.GetSheetObject("TX41").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 13).Select</P><P>ActiveDocument.GetSheetObject("CH14").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P>'XLDoc.Sheets(vSheet).Range("A" & 19).Select</P><P>'ActiveDocument.GetSheetObject("CH55").CopyBitmapToClipboard 'Copy the charts</P><P>'XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 21).Select</P><P>ActiveDocument.GetSheetObject("CH18").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 23).Select</P><P>ActiveDocument.GetSheetObject("CH62").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 24).Select</P><P>ActiveDocument.GetSheetObject("CH22").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 25).Select</P><P>ActiveDocument.GetSheetObject("CH31").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 27).Select</P><P>ActiveDocument.GetSheetObject("CH28").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 29).Select</P><P>ActiveDocument.GetSheetObject("CH36").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 30).Select</P><P>ActiveDocument.GetSheetObject("CH43").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 31).Select</P><P>ActiveDocument.GetSheetObject("CH46").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 33).Select</P><P>ActiveDocument.GetSheetObject("CH25").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 36).Select</P><P>ActiveDocument.GetSheetObject("CH37").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Range("A" & 38).Select</P><P>ActiveDocument.GetSheetObject("CH42").CopyBitmapToClipboard 'Copy the charts</P><P>XLDoc.Sheets(vSheet).PasteSpecial DataType=wdPasteBitmap</P><P></P><P></P><P></P><P></P><P></P><P></P><P></P><P></P><P></P><P></P><P>  set Selection =   XLDoc.Sheets(vSheet).Rows("22")</P><P>With Selection</P><P>.EntireRow.RowHeight = 12.0</P><P></P><P></P><P>End With</P><P></P><P></P><P></P><P></P><P>set Selection =   XLDoc.Sheets(vSheet).Rows("18")</P><P>With Selection</P><P>.EntireRow.RowHeight = 8.25</P><P></P><P></P><P>End With</P><P></P><P></P><P></P><P></P><P>set Selection =   XLDoc.Sheets(vSheet).Rows("20")</P><P>With Selection</P><P>.EntireRow.RowHeight = 13.5</P><P></P><P></P><P>End With</P><P></P><P></P><P></P><P></P><P>set Selection =   XLDoc.Sheets(vSheet).Rows("26")</P><P>With Selection</P><P>.EntireRow.RowHeight = 15.75</P><P></P><P></P><P>End With</P><P></P><P></P><P></P><P></P><P>set Selection =   XLDoc.Sheets(vSheet).Rows("28")</P><P>With Selection</P><P>.EntireRow.RowHeight = 12.0</P><P></P><P></P><P>End With</P><P></P><P></P><P></P><P></P><P>  set Selection =   XLDoc.Sheets(vSheet).Rows("32")</P><P>With Selection</P><P>.EntireRow.RowHeight = 15.0</P><P></P><P></P><P>End With</P><P></P><P></P><P></P><P></P><P> set Selection =   XLDoc.Sheets(vSheet).Rows("35")</P><P>With Selection</P><P>.EntireRow.RowHeight = 20.50</P><P></P><P></P><P>End With</P><P></P><P></P><P></P><P></P><P> set Selection =   XLDoc.Sheets(vSheet).Rows("37")</P><P>With Selection</P><P>.EntireRow.RowHeight = 12.75</P><P></P><P></P><P>End With</P><P></P><P></P><P></P><P></P><P></P><P></P><P></P><P>XLDoc.Sheets(vSheet).Shapes.SelectAll</P><P></P><P></P><P></P><P></P><P></P><P></P><P>Set XLDoc = Nothing</P><P>Set XLApp = Nothing</P><P></P><P></P><P>End Sub</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304099#M834095 bnelson111 2020-11-25T16:16:04Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304100#M834096 <HTML><HEAD></HEAD><BODY><P>Are you getting an error with Excel 2010? </P><P>Is the script failing? If so, on what line?</P></BODY></HTML> Fri, 02 Jun 2017 16:33:51 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304100#M834096 m_woolf 2017-06-02T16:33:51Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304101#M834097 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P>set XLSheet = XLDoc.Worksheets(<SPAN style="color: #ff0000;">1</SPAN>)</P><P></P><P>MAYUSC COLUMN:</P><P>Range("<SPAN style="color: #0000ff;">S</SPAN>"& 2).Select</P></BODY></HTML> Fri, 02 Jun 2017 20:02:55 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304101#M834097 el_aprendiz111 2017-06-02T20:02:55Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304102#M834098 <HTML><HEAD></HEAD><BODY><P>Hi Brett</P><P></P><P>What kind of error do you have? any print or message?</P><P></P><P>Thanks</P></BODY></HTML> Sat, 03 Jun 2017 00:14:57 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304102#M834098 fkeuroglian 2017-06-03T00:14:57Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304103#M834099 <HTML><HEAD></HEAD><BODY><P>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.</P></BODY></HTML> Sat, 03 Jun 2017 08:58:11 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304103#M834099 bnelson111 2017-06-03T08:58:11Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304104#M834100 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-size: 13.3333px;">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.</SPAN></P></BODY></HTML> Sat, 03 Jun 2017 08:58:29 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304104#M834100 bnelson111 2017-06-03T08:58:29Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304105#M834101 <HTML><HEAD></HEAD><BODY><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">?</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">MAYUSC COLUMN:</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Range("<SPAN style="font-weight: inherit; font-style: inherit; font-family: inherit; color: #0000ff;">S</SPAN>"& 2).Select</P></BODY></HTML> Mon, 05 Jun 2017 08:36:33 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304105#M834101 bnelson111 2017-06-05T08:36:33Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304106#M834102 <HTML><HEAD></HEAD><BODY><P>Object required: 'ActiveDocument.GetSheetObject(...)'</P></BODY></HTML> Mon, 05 Jun 2017 08:37:33 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304106#M834102 bnelson111 2017-06-05T08:37:33Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304107#M834103 <HTML><HEAD></HEAD><BODY><P>Object required: 'ActiveDocument.GetSheetObject(...)'</P></BODY></HTML> Mon, 05 Jun 2017 08:37:44 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304107#M834103 bnelson111 2017-06-05T08:37:44Z Re: VB Script. https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304108#M834104 <HTML><HEAD></HEAD><BODY><P>Sorry found issue a chart had renamed?? My fault thanks everyone.</P></BODY></HTML> Mon, 05 Jun 2017 09:17:25 GMT https://community.qlik.com/t5/QlikView/VB-Script/m-p/1304108#M834104 bnelson111 2017-06-05T09:17:25Z