I am beating my head against a wall. I have a python script that calls an SAP GUI script through a shell command. That part works great, once the returns.xlsx is generated, I use openpyxl to create a list from the values in column A. From there I try to append the list to the clipboard so I can paste it via a second SAP GUI script. For some reason when I try to paste in excel after the script completes, Excel throws an error window:
Microsoft Excel cannot paste data
Full script below:
from subprocess import call
import warnings
import win32gui
import os
import time
from openpyxl import load_workbook
from Tkinter import Tk
import wx
import pyautogui
warnings.filterwarnings('ignore')
filePath = os.path.expanduser("~\Desktop\\")
app = wx.App()
frame = wx.Frame(None, -1)
############## Start and stop dates
dlg = wx.TextEntryDialog(frame, 'Enter Start Date','Start Date')
dlg.SetValue("mm/dd/YYYY")
if dlg.ShowModal() == wx.ID_OK:
startDate = dlg.GetValue()
dlg.Destroy()
dlg2 = wx.TextEntryDialog(frame, 'Enter End Date','End Date')
dlg2.SetValue("mm/dd/YYYY")
if dlg2.ShowModal() == wx.ID_OK:
endDate = dlg2.GetValue()
dlg2.Destroy()
#############
############################Functions for getting the window names and copying data to the clip board
def GetWindowName():
winName=win32gui.GetWindowText (win32gui.GetForegroundWindow())
return winName
def CopytoClip(clipList):
r = Tk()
r.withdraw()
r.clipboard_clear()
for item in clipList:
r.clipboard_append(item)
r.clipboard_append("\n")
r.update()
r.destroy()
############################
###################SAP GUI Shell Commands
command = 'guixt.exe "Input=V[MYDATE]:%s;V[END_DATE]:%s;OK: process=C:\guixt\scripts\\RetReport2.txt"' %(startDate, endDate)
call(command, shell = True, cwd = 'C:\Program Files (x86)\SAP\FrontEnd\SAPgui\\')
###################
################################Waiting for the save as window and saving the file as retuns.xlsx on the desktop
winName = "nothing"
while winName != "Save As":
winName = GetWindowName()
time.sleep(1)
retFile = filePath + 'returns.XLSX'
pyautogui.typewrite(retFile)
pyautogui.press('enter')
################################
###############Waiting for the file to auto open
winName = "nothing"
while winName != "returns.XLSX - Excel":
winName = GetWindowName()
time.sleep(2)
################################
##############################Create the list from column A and append to clipboard
salesOrders = []
wbSales = load_workbook(filename = retFile)
wsSales = wbSales['Sheet1']
for row in wsSales.iter_rows('A:A', row_offset = 1):
for cell in row:
try:
salesOrders.append(int(cell.value))
except TypeError:
pass
CopytoClip(salesOrders)
#################################
Now if I use the "returns.xlsx" created above in the following short script that I cut from the above script... the clipboard is full.
Can anyone help figure out why the clipboard will not work in the full script?
from openpyxl import load_workbook
from tkinter import *
import time
import os
filePath = os.path.expanduser("~\Desktop\\")
retFile = filePath + 'returns.XLSX'
def CopytoClip(clipList):
r = Tk()
r.withdraw()
r.clipboard_clear()
for item in clipList:
r.clipboard_append(item)
r.clipboard_append("\n")
r.update()
r.destroy()
salesOrders = []
wbSales = load_workbook(filename = retFile)
wsSales = wbSales['Sheet1']
for row in wsSales.iter_rows('A:A', row_offset = 1):
for cell in row:
try:
salesOrders.append(int(cell.value))
except TypeError:
pass
CopytoClip(salesOrders)
