0

I have a python script that pastes values from one excel to another excel called 'Automate'. I have pasted the values to specific cells in sheet 1 of Automate so that sheet 2 in Automate can read the values and apply a formula. In Automate, I have a macro that uploads the values in the sheet 2 to SQL before saving. I have used openpyxl to work with excel and the function wb.save(Automate.xlsxm) doesn't run the macro.

I am able to run the below code that refreshes qand saves Automate and it runs the macro to upload the values to SQL. However, I have to manually run the script and when I use task scheduler to run the script the values do not upload values from Automate to SQL.

import win32com.client                                                                                     
                                                                                                           
xlapp = win32com.client.gencache.EnsureDispatch("Excel.Application")                                       
wb = xlapp.Workbooks.Open('Automate.xlsm')             
wb.RefreshAll()                                                                                            
xlapp.CalculateUntilAsyncQueriesDone()                                                                     
wb.Save()                                                                                                  
wb.Close()                                                                                                 
wb = None                                                                                                  
xlapp.Quit()                                                                                               
xlapp = None

help will be very much appreciated

j_alternative
  • 1
  • I assume that `wb.save(Automate.xlsxm)` is a typo? – jezza_99 Nov 30 '21 at 19:02
  • At what point is the SQL upload taking place? Is the macro in the ThisWorkbook BeforeSave event handler, or a separate macro? (As in your script you don't explicitly call a macro). Can we see the macro code?. If the code sample above runs and does what you want when YOU run it, but not when the Task Scheduler runs it, then it could be a permissions issue with the SQL upload. eg. If the Task Scheduler runs under a System account, it may not have access to any database sources that are available under your User account. – DS_London Dec 01 '21 at 08:32
  • jezza_99, correct it was a typo, it should have read wb.save(Automate.xlsm) – j_alternative Dec 01 '21 at 09:38
  • DS_London. the macro, called SUpload, is set to upload values to SQL before the excel is saved (i.e before the saving process in excel is completed, the SQL upload occurs). The BeforeSave event handler calls the SUpload macro, however I believe I need to run the macro SUpload for the SQL upload to take place. Are there any ways you can suggest changing permissions? I currently work from a work PC – j_alternative Dec 01 '21 at 10:17
  • Look in the Task Scheduler and the settings for your task. There is a field which specifies which credentials/user account is used. If this is your account, then perhaps the problem lies elsewhere. If it is not your account, then Excel will not be using your settings. You could get the macro to do something visible (eg write some logging info to a file), so you can see if it is actually being called when the scheduler runs the task. Perhaps also post the VBA code of SUpload? – DS_London Dec 01 '21 at 16:45

1 Answers1

0

Openpyxl doesn't evaluate excel formulas and macros, as it doesn't actually use excel and doesn't have that functionality built into it. Instead, I'd recommend using xlwings, which opens excel and will evaluate all formula on opening (if they are set up to automatically reevaluate). You can also use xlwings to run your macro, with some examples here. A rough outline of your code would then be:

import openpyxl as op
import xlwings as xw

# Openpyxl open and write
wb_path = "Your_workbook.xlsm"
wb = op.load_workbook(wb_path)
ws = wb["Sheet1"]

# Write your values etc

# Save
wb.save(wb_path)
wb.close()

# Open with xlwings
wb = xw.Book(wb_path)
app = xw.apps.active

# Run macro
macro = wb.macro("YourMacro")
macro()

# Save, close
wb.save(wb_path)
app.quit()
jezza_99
  • 1,074
  • 1
  • 5
  • 17
  • I read the OP's question as asking why their Python script works when run manually, but not when run under the Task Scheduler. This answer just re-writes their already-working code, and I can't quite see what `xlwings` brings to the party, given that the OP is already successfully automating Excel directly with `win32com` (and performing some extra refresh actions beyond formula re-calculation). – DS_London Dec 01 '21 at 09:10
  • Unfortunately, the xlwings script doesnt help, appreciate the help nonetheless. DS_London you read correct. – j_alternative Dec 01 '21 at 10:20
  • 1
    Ah apologies @j_alternative, I obviously misread part of your question and thought your excel was having issues running the macro. I'll delete this answer after a suitable time – jezza_99 Dec 01 '21 at 18:13