CopyTableToClipboard & Paste into excel supress consecutive spaces

[Anonymous]

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.

[sasiparupudi1]

Try using the export method of the striaght table

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

hth

Sas

[Anonymous]

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.

[sasiparupudi1]

Hi

Did you try the csv option?

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

Regards

Sas

[sasiparupudi1 · Accepted solution]

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

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

hth

Sas

[Anonymous]

Yes! It works. Thank you!!

[oknotsen]

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 .

[Anonymous]

Ok, done.

thanks