topic Re: Export to Excel macro - issue exporting table text in QlikView

Export to Excel macro - issue exporting table text

jessica_webb — Thu, 24 Nov 2022 13:30:35 GMT

Hello,
I'm creating a series of macros to export many different charts, tables and titles into an Excel document.

With my macro below, everything is working exactly as it should, except the first three tables (table1, table2 and table3). With these, I get the value of 'title1' being pasted into the specified cells (B9, B23 and I23). As I said, everything else copies over exactly as it should, so why is the value of 'title1' being copied over?!

Any help appreciated because I am STUCK!

Public XLDoc
Public XLSheet1

sub openExcel

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

Set XLDoc = XLApp.Workbooks.Open("Excel report proto - macros.xlsx")
set XLSheet1 = XLDoc.Worksheets(1)

End Sub

sub ExportText

'Copy title
set title1 = ActiveDocument.GetSheetObject("TX324")
XLSheet1.Range("C2") = title1.GetText()

'Copy tables
set table1 = ActiveDocument.GetSheetObject("CH478")
table1.CopyTextToClipboard
XLSheet1.Range("B9").PasteSpecial

set table2 = ActiveDocument.GetSheetObject("CH393")
table2.CopyTextToClipboard
XLSheet1.Range("B23").PasteSpecial

set table3 = ActiveDocument.GetSheetObject("CH392")
table3.CopyTextToClipboard
XLSheet1.Range("I23").PasteSpecial

'Copy title
set title2 = ActiveDocument.GetSheetObject("TX329")
XLSheet1.Range("B36") = title2.GetText()

'Copy chart
set chart1 = ActiveDocument.GetSheetObject("CH374")
chart1.CopyBitmapToClipboard
XLSheet1.Range("B38").PasteSpecial DataType=wdPasteBitmap

'Copy tables
set table4 = ActiveDocument.GetSheetObject("CH375")
table4.CopyTextToClipboard
XLSheet1.Range("B56").PasteSpecial

'Copy chart
set chart2 = ActiveDocument.GetSheetObject("CH388")
chart2.CopyBitmapToClipboard
XLSheet1.Range("B78").PasteSpecial DataType=wdPasteBitmap

'Copy tables
set table5 = ActiveDocument.GetSheetObject("CH380")
table5.CopyTextToClipboard
XLSheet1.Range("B96").PasteSpecial

End Sub

Re: Export to Excel macro - issue exporting table text

marcus_sommer — Thu, 24 Nov 2022 13:51:17 GMT

I think the title-stuff isn't created as a copy-job else it's a writing-job whereby it's not specified which property of the cell-object should be addressed. Therefore, try it with the following adjustment:

XLSheet1.Range("C2").Value = title1.GetText()

- Marcus

Re: Export to Excel macro - issue exporting table text

jessica_webb — Thu, 24 Nov 2022 17:17:08 GMT

Hi Marcus,

Thanks for taking the time to help.

I tried putting .Value in as you suggested, and the macro just stopped running after that line. Then I tried taking .Value back out again and the macro still stops running after that line.

Somehow I've managed to completely break the macro 😑

If you have any other thoughts at all, please let me know 🙂

Re: Export to Excel macro - issue exporting table text

jessica_webb — Thu, 24 Nov 2022 17:32:02 GMT

Have somehow managed to fix this, so in case anyone else comes across this issue...

For some reason, in a couple of instances, QV macro didn't like 'table1.CopyTextToClipboard' (although didn't seem to have a problem in other places, weirdly).

Anyway, updated and currently working macro is:

Public XLDoc
Public XLSheet1

sub openExcel

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

Set XLDoc = XLApp.Workbooks.Open("Excel report proto - macros.xlsx")
set XLSheet1 = XLDoc.Worksheets(1)

End Sub

sub ExportText

'Copy title
set title1 = ActiveDocument.GetSheetObject("TX324")
XLSheet1.Range("C2") = title1.GetText()

'Copy tables
set table1 = ActiveDocument.GetSheetObject("CH478")
table1.CopyTableToClipboard (false)
XLSheet1.Range("B9").PasteSpecial Paste = xlPasteValues

set table2 = ActiveDocument.GetSheetObject("CH393")
table2.CopyTableToClipboard (false)
XLSheet1.Range("B23").PasteSpecial Paste = xlPasteValues

set table3 = ActiveDocument.GetSheetObject("CH392")
table3.CopyTableToClipboard (false)
XLSheet1.Range("I23").PasteSpecial Paste = xlPasteValues

'Copy title
set title2 = ActiveDocument.GetSheetObject("TX329")
XLSheet1.Range("B36") = title2.GetText()

'Copy chart
set chart1 = ActiveDocument.GetSheetObject("CH374")
chart1.CopyBitmapToClipboard
XLSheet1.Range("B38").PasteSpecial DataType=wdPasteBitmap

'Copy tables
set table4 = ActiveDocument.GetSheetObject("CH375")
table4.CopyTableToClipboard (false)
XLSheet1.Range("B56").PasteSpecial Paste = xlPasteValues

'Copy chart
set chart2 = ActiveDocument.GetSheetObject("CH388")
chart2.CopyBitmapToClipboard
XLSheet1.Range("B78").PasteSpecial DataType=wdPasteBitmap

'Copy tables
set table5 = ActiveDocument.GetSheetObject("CH380")
table5.CopyTableToClipboard (false)
XLSheet1.Range("B96").PasteSpecial Paste = xlPasteValues

End Sub

Re: Export to Excel macro - issue exporting table text

marcus_sommer — Fri, 25 Nov 2022 08:34:28 GMT

The clipboard is a complex matter and don't belong to Qlik else it's an OS feature which is administers from the OS and all tools and services could use it at any time. Therefore, conflicts between them and/or in regard to the number, size and the kind of the copied content are possible although in my experience it's rather seldom that this really happens.

Quite more common are timely conflicts caused from the calling tool itself. Means the tool might be already executing the third code-statement while the OS is further busy to execute the I/O task from the first code-statement ...

This leads quite often to the requirement to delay the code-execution and/or to check the successfully execution of the previous statements before going on. Within QlikView vbs-script it could be mostly handled with WaitForIdle statements to ensure that all QlikView calculations are finished before the next step comes and Sleep statements which just paused n milliseconds (here it's just a guessing which values are sensible - in our older environment I needed 4 seconds to ensure that exports where really finished and within the new one values of around 1 second are enough - maybe less would be sufficient but the run-times are fine for me and I don't need more optimization yet).

- Marcus