title: Connecting from Python to Excel 2016(365) and perform tasks
body: <p>I have a problem connecting to Excel API in windows 10. I use Office365 and with it Excel2016. My goal is: to download CSV file from a client FTPS Server, merge it with the existing files,perfom some action on it(with pandas) and then load the whole data into excel and do reporting with it... Up to the point of loading it into Excel everything is fine.I managed to do all steps automatically with Python (sorry if my code looks a little cluttered - I am new to Python)</p>
  
  <pre><code>import subprocess
  import os
  import ftplib
  import fnmatch
  import sys
  from ftplib import FTP_TLS
  from win32com.client import Dispatch
  import pandas as pd
  
  filematch = '*.csv'
  target_dir = 'cannot tell you the path :-) '
  
  def loginftps(servername,user,passwort):
      ftps = FTP_TLS(servername)
      ftps.login(user=user,passwd=passwort)
      ftps.prot_p()
      ftps.cwd('/changes to some directory')
  
      for filename in ftps.nlst(filematch):
          target_file_name = os.path.join(target_dir,os.path.basename(filename))
          with open(target_file_name,'wb') as fhandle:
                  ftps.retrbinary('RETR %s' %filename, fhandle.write)
  
  def openExcelApplication():
      xl = Dispatch("Excel.Application")
      xl.Visible = True # otherwise excel is hidden
  
  def mergeallFilestoOneFile():
      subprocess.call(['prepareData_executable.bat'])
  
  def deletezerorows():
      rohdaten = pd.read_csv("merged.csv",engine="python",index_col=False,encoding='Latin-1',delimiter=";", quoting = 3)
      rohdaten = rohdaten.convert_objects(convert_numeric=True)
      rohdaten = rohdaten[rohdaten.UN_PY &gt; 0]
      del rohdaten['deletes something']
      del rohdaten['deletes something']
      rohdaten.to_csv('merged_angepasst.csv',index=False,sep=";")
  
  def rohdatenExcelAuswertung():
      csvdaten = pd.csv_read("merged.csv")
  
  servername = input("please enter FTPS serveradress:")
  user = input("Loginname:")
  passwort = input("Password:")
  
  loginftps(servername,user,passwort)
  mergeallFilestoOneFile()
  deletezerorows()
  </code></pre>
  
  <p>And here I am stuck somehow,.. I did extensive google research but somehow nobody has ever tried to perform Excel tasks from within Python?? </p>
  
  <p>I found this stackoverflow discussion: <a href="https://stackoverflow.com/questions/13720940/opening-running-excel-file-from-python">Opening/running Excel file from python</a> but I somehow cannot figure out where my Excel-Application is stored to run code mentioned in this thread.</p>
  
  <p>What I have is an Excel-Workbook which has a data connection to my CSV file. I want Python to open MS-Excel, refresh data connection and refresh a PivoTable &amp; then save and close the file.</p>
  
  <p>Has anybody here ever tried to to something similar and can provide some code to get me started?
  Thanks</p>