Jump to content
wendyT

Pasting clipboard content to Excel

Recommended Posts

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!

 

#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)

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@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 :)


Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

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)

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...