topic Re: CopyTableToClipboard & Paste into excel supress consecutive spaces in QlikView

CopyTableToClipboard & Paste into excel supress consecutive spaces

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello

I'm using a Macro to export a straight Table to excel with the CopyTableToClipboard method. I have one dimension that is calculated as some consecutive spaces + a text, to achieve indentation.

The problem I have is that CopyTableToClipboard & then Paste into an Excel sheet, deletes the consecutive spaces, losing the indentation in the resulting excel sheet.

I've tried to format the excel cells as text, before and after pasting, but the problem also happens.

Can someone help me to solve this?

thanks.

Re: CopyTableToClipboard & Paste into excel supress consecutive spaces

sasiparupudi1 — Thu, 09 Mar 2017 10:03:01 GMT

Try using the export method of the striaght table

Export("C:\Users\xxxx\Documents\Test1.csv", ",")

hth

Sas

Re: CopyTableToClipboard & Paste into excel supress consecutive spaces

Anonymous — Thu, 09 Mar 2017 16:32:19 GMT

Hi,

this is a sample table:

and the resulting excel sheet:

As you can see, cells A2 and B2 don't have the leading spaces.

This is the code:

Sub ExportTabla

Set obj = ActiveDocument.GetSheetObject("CH01")

Set XLApp = CreateObject("Excel.Application")

Set XLDoc = XLApp.Workbooks.Add

Set curSheet = XLDoc.WorkSheets(XLDoc.Sheets.Count)

curSheet.Range("A1").Select

obj.CopyTableToClipboard true

curSheet.Paste

end sub

(Sorry but I cannot attach the qvw file, I don't know why I don't have the "Advanced edit" option )

thanks.

Re: CopyTableToClipboard & Paste into excel supress consecutive spaces

sasiparupudi1 — Thu, 09 Mar 2017 17:36:08 GMT

Did you try the csv option?

ActiveDocument.GetSheetObject("CH32").Export "C:\Test\Test1.csv", ","

Regards

Sas

Re: CopyTableToClipboard & Paste into excel supress consecutive spaces

sasiparupudi1 — Thu, 09 Mar 2017 18:10:33 GMT

Please try the following chr.. It seems to be reataining the spaces after the export

=Repeat(chr(160),5)&' Test '

hth

Sas

Re: CopyTableToClipboard & Paste into excel supress consecutive spaces

Anonymous — Fri, 10 Mar 2017 07:19:01 GMT

Yes! It works. Thank you!!

Re: CopyTableToClipboard & Paste into excel supress consecutive spaces

oknotsen — Fri, 10 Mar 2017 08:25:08 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

Re: CopyTableToClipboard & Paste into excel supress consecutive spaces

Anonymous — Fri, 10 Mar 2017 09:57:59 GMT

Ok, done.

thanks