Macro to copy a table into excel keeping multiple spaces

[Anonymous]

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

[tamilarasu]

Hi Rachele,

Could you attach a sample file?

[Anonymous]

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

[tamilarasu · Accepted solution]

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

[Anonymous]

Hi Tamil,

it works! Thank you very much.

Rachele