Excel PasteSpecial only text doesn't work

[frank10]

I have to copy an HTML table to an Excel.

Like this:

https://js.do/code/https-jsfiddle-net-dm7ltq6g-1

I have to copy & paste to preserve also the background colors and formatting bold etc

But it has some numbers like "1,200" or "10 / 28" that Excel transforms to 1,2 and ott-28...

So I thought to repaste again over the same cells with PasteSpecial only text (it works manually), but I can't get it on Autoit.

I use:

Local $oTarget = $oWB_KW.Worksheets( "FilterResults")
$oTarget.Range("G10").Select
$oTarget.Paste
$oTarget.PasteSpecial(-4163,Default,Default,Default)

Sometimes also Excel sheet crashes with PasteSpecial...

 

Or if you have other ideas as to get the pasted texts correctly formatted...

 

 

[water]

Did you try the _Excel_RangeCopyPaste function of the Excel UDF that comes with AutoIt?
It has builtin error handling and tells you what goes wrong by setting @error and @extended.

[frank10]

I didn't know it could copy from clipboard! good!

It works copying from oRange, but I get error from Clipboard:

_Excel_RangeCopyPaste($oWB_KW.Worksheets( "FilterResults"), Default , "B10" )

ConsoleWrite(@error & "_ext:" & @extended & @CRLF)
4_ext:-2147352567

 

Edited October 14, 2020 by frank10

[frank10]

It works if I put some range from excel sheet to clipboard first with

_Excel_RangeCopyPaste($oWB_KW.Activesheet, "A1:S2")

and THEN I copy from clipboard to Excel again.

But in my case I already have the data on the clipboard (from HTML table) and I must copy them to Excel directly.

And I cannot copy them with normal Paste (for the formatting) and then CopyPaste with this func from these cells to copy again with only text because they are already corrupted in the format.

[GokAy]

First post, so hello everyone!

I may have a solution (or a workaround) depending on how you use the data pasted to Excel. Do you make any calculations with the data?

If not, then try the method below (I added the whole script that worked for me with your example HTML table). The step you have missed is the .PasteSpecial("Text") part. While HTML table is in clipboard:

#include <Excel.au3>

Local $oWB = _excel_Open()

_Excel_BookNew($oWB)
sleep(2000)

$oWB.Activesheet.Range("A1").Select ;Select the appropriate cell in the worksheet you want
$oWB.Activesheet.Paste              ;Paste as usual
$oWB.Selection.NumberFormat = "@"   ;The paste method will select the effected cells, which can be referred as "selection". Change number format to "text".
$oWB.Selection.PasteSpecial("Text") ;Paste again to the "selection" as PasteSpecial("Text"), maybe "Unicode Text" would work too, haven't tried.

 

Note: You don't need to select cells/range in Excel (while using VBA) to paste stuff unless AutoIt requires it. I have only been using AutoIt for a week now and I haven't used Excel.au3 yet until this post. Hopefully, someone else will correct any inefficient steps in my script.

If you need calculations for the pasted data, maybe something else can be managed. Maybe a VBA macro to get things run smooth. I can try help with that side if you want.

Edited October 14, 2020 by GokAy

[frank10]

Thank you a lot GokAy!

That's exactly what I needed!

[frank10]

EDIT

It was a problem with the browser... all fine

Edited October 14, 2020 by frank10