So I am not a programmer by any means, I honestly don't know how I got here. But I have this python script that I execute through Spyder that copies a range of data from one excel database file and pastes it into a simple excel file somewhere else on the drive.
I usually always set Visible = False but for some reason I get an error whenever it is set to False. When Visible = True, the code works perfectly fine.
THE ERROR:
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Select method of Worksheet class failed', 'xlmain11.chm', 0, -2146827284), None)
I've searched around on here and the closest I could find to my answer was this but it is in VBA which is similar to the python syntax so I usually just end up playing with it until I figure out the translation but this time I am stumped.
How do I fix this so that I can Select() Copy() and Paste while the app is not visible? Also, how do I keep my formatting? The line where I change the heading color fill wf.worksheets("wfsheetname").Range("A5", "jh5").Interior.ColorIndex = 35 is because it deletes the formatting.
If my code could be structured better also, please help.
THE CODE:
import win32com.client as win32
xlapp= win32.DispatchEx('Excel.Application')
xlapp.DisplayAlerts = False
xlapp.Visible = True
db = xlapp.Workbooks.Open(r'C:selectdatafromhere.xlsx')
wf = xlapp.Workbooks.Open(r'C:pastedatatohere.xlsx')
dbsh = db.Worksheets("dbsheetname")
dbsh.Activate()
dbsh.Range("a6:jh150").Select()
xlapp.Selection.Copy(Destination=wf.Worksheets("wfsheetname").Range("A5"))
wf.worksheets("wfsheetname").Range("A5", "jh5").Interior.ColorIndex = 35
db.Close()
wf.Save()
wf.Close()
xlapp.Quit()
