topic Macro to copy a table into excel keeping multiple spaces in QlikView

Macro to copy a table into excel keeping multiple spaces

Anonymous — Mon, 19 Mar 2018 11:19:46 GMT

Hi,

I have a table in qlikview and a macro to copy the table into excel. The macro works, but I would like it to keep all the spaces, (sometimes we have 3 or 4 consecutive spaces).

XLDoc.Sheets(1).Paste() has been replaced with XLDoc.Sheets(1).PasteSpecial(-4122), but when the macro runs, qlikview doesn’t respond and the only thing to do is end the activity.

Could anyone tell me how I can keep the spaces?

The macro is:

sub ExcelFileTab

set vExportPath = ActiveDocument.Variables("vExportPath")

set vFileName = ActiveDocument.Variables("vFileName")

Path = vExportPath.GetContent.String

FileName = vFileName.GetContent.String

set XLApp = CreateObject("Excel.Application")

XLApp.DisplayAlerts = False

XLApp.Visible = False

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("TB08").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

'XLDoc.Sheets(1).Paste (-4122)

XLDoc.SaveAs Path & FileName

XLApp.DisplayAlerts = True

XLApp.Quit

End sub

Thanks

Re: Macro to copy a table into excel keeping multiple spaces

tamilarasu — Tue, 20 Mar 2018 02:49:14 GMT

Hi Rachele,

Could you attach a sample file?

Re: Macro to copy a table into excel keeping multiple spaces

Anonymous — Tue, 20 Mar 2018 08:20:45 GMT

Hi Tamil,

I've solved with 'XLDoc.Sheets(1).PasteSpecial(-4163), the spaces are preserved.

But I don't understand why the Past Type (-4122) doesn't work and the application crashes down.

I've attached a sample file.

Thanks

Rachele

Re: Macro to copy a table into excel keeping multiple spaces

tamilarasu — Tue, 20 Mar 2018 17:58:24 GMT

Hi Rachele,

Great. Actually, it is not working for me as well. But If you use (-4163) as paste special type, you will lose the format and only numbers will be exported into the excel. Below solution may look silly but working like expected .

ActiveDocument.GetSheetObject("TB08").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).PasteSpecial (-4163)

So what I have done is just pasting the table into excel as it is i.e normal export (as you mentioned the spaces are not preserved). In the next step, I'm again pasting the table but this time only numbers. Note that the format was already pasted in the previous paste and only the values are pasted. That's all. I think we may have slight time difference in running of macro (because we are pasting the data twice) if you have larger data set. But I believe you will see only slight difference and worth to give a try. Let me know your thoughts.

Reference: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlpastetype-enumeration-excel

Re: Macro to copy a table into excel keeping multiple spaces

Anonymous — Wed, 21 Mar 2018 08:45:01 GMT

Hi Tamil,

it works! Thank you very much.

Rachele