Pasting clipboard content to Excel

[wendyT]

Hi,

Hope everyone's day is going well. I am new here and new to AutoIt. I am running into some issues with working with Excel. Any help you can provide is greatly appreciated.

I am trying to automate some Tableau report and I need to export some data to Excel. After I copy the data from Tableau, I issued a Send command of ^v (Ctrl-v) to paste the content to Excel and it worked. However, the BookSaveAs Excel function won't work after the Send command. If I use the RangWrite command to paste the content, it all goes into one column instead of multiple columns like it should with the ^v (Ctrl-v) command. Is there any other Excel command that will allow me to paste content from clipboard?

Thanks for all your help!

 

expandcollapse popup

#include <Excel.au3>
#include <MsgBoxConstants.au3>


Local $tableaufile = "Tableau - COMBINED Results"

Local $coachingreport = "C:\Users\wendy\OneDrive\Documents\Wendy\Xencall\Daily Processing\Results\Email Reports\Coaching Reports\Coaching Report 2020-03-03"

; Make Tableau desktop active and maximize window
WinActivate($tableaufile); Activate Tableau window
WinSetState($tableaufile, "", @SW_MAXIMIZE)
WinWaitActive($tableaufile); waiting for Tableau window to show up
MouseClick("Left",1794,1030)
Sleep(100)

; Click on Coaching Report tab
MouseClick("Left",725,1001)
Sleep(100)

; Copy Crosstab report to clipboard
Send("!w")
Send("c")
Send("c")
Sleep(300)

; Create excel application object
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Create a new workbook
Local $oBook = _Excel_BookNew($oExcel)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;Paste clipboard content to Excel
_Excel_RangeWrite($oBook, Default, ClipGet(), "A1")

;Paste clipboard to Excel using Send command
;Sleep(300)
;Send("^v")
;Sleep(300)

$FilePath = "C:\Users\wendy\Coaching Report 2020-03-05.xlsx"
_Excel_BookSaveAs($oBook, $FilePath, Default, True)
If @error Then
    MsgBox(48, "_Excel_BookSaveAs", "There was a error while saving the Excel file" & @CRLF & @error)
Else
    MsgBox(0, "_Excel_BookSaveAs", "File was successfully saved as " & $filepath)
EndIf
_Excel_Close($oExcel)

 

[Subz]

Can you post a snippet of the Tableau data?

[wendyT]

Hi @Subz,

Attached is a sample of the Tableau data. Basically, the program will click on the appropriate Tableau tab and the Alt-W, C, C keystrokes just put the report data into the clipboard. Thanks for any suggestions you might have.

 

Coaching data sample.xlsx

[FrancescoDiMuro]

@wendyT

Did you try to use AutoItWindowInfoTool on this Tableau application (I suppose) instead of use mouse clicks and send commands?

Maybe you can automate it in a different reliabler way

[Subz]

The following should work (it worked for me anyhow.

#include <Excel.au3>

Local $oExcel = _Excel_Open()
    If @error Then Exit

Local $oBook = _Excel_BookNew($oExcel)
    If @error Then Exit

_Excel_RangeCopyPaste($oBook.ActiveSheet, Default, "A1")

Local $sFilePath = @ScriptDir & "\Coaching Report 2020-03-05.xlsx"

_Excel_BookSaveAs($oBook, $sFilePath, Default, True)

 

[wendyT]

Hi @FrancescoDiMuro. I didn't try AutoItWindowInfoTool. That's a great idea. I am new to AutoIt so I have a lot to learn. Thanks for the suggestion.

 

@Subz, thanks for the suggestion and testing it. I will give that a try.

[wendyT]

Hi @Subz. I tried using RangeCopyPaste and it didn't paste anything. The BookSaveAs function works fine. Did you copy from Excel to Excel?

[wendyT]

Hi @Subz. I tried using RangeCopyPaste and it didn't paste anything. The BookSaveAs function works fine. Did you copy from Excel to Excel?

[Subz]

I just copied the data from the spreadsheet you posted into clipboard and then ran the script above.

[wendyT]

Hi @Subz. I wonder if it's because our copy source is also in Excel. Let me try again. Thanks.

[wendyT]

I found a solution!!! I use this and it works!!! I thought I tried this. I must have typed something slightly wrong.

$oExcel.ActiveSheet.Range("A1").select
$oExcel.ActiveSheet.Paste