1

I'm using the following code to run an Excel macro from Python:

import pymysql
import datetime
import csv
import math
import os
import glob
import sys
import win32com.client
import numpy
from tkinter import *
from tkinter import ttk
import tkinter.messagebox

def run_macro():
    print('macro')

    #this if is here because if an executable is created, __file__ doesn't work
    if getattr(sys, 'frozen', False):
        name = (os.path.dirname(sys.executable) + '\\Forecast template.xlsm')

    else:
        name = str(os.path.dirname(os.path.realpath(__file__)) + '\\Forecast template.xlsm')

    print(name)

    #this part runs the macro from excel
    if os.path.exists(name):
        xl=win32com.client.Dispatch("Excel.Application")
        xl.Workbooks.Open(Filename=name, ReadOnly=1)
        xl.Application.Run("ThisWorkbook.LoopFilesInFolder")
        xl.Application.Quit() # Comment this out if your excel script closes
        del xl

    print('File refreshed!')

I seem to be be having a certain issue with this, after running this, I go to open any excel file and I only get a grey window:

enter image description here

Any idea of why this happens? Also how do I add to the code something to just open a file in Excel? (not to get the information, but to just open that file in Excel)

Extra question: How do I get this not to close all open Excel files?

EDIT: I just checked the macro, and that works just fine, the problem seems to come just from when I run the code.

NEW EDIT:

This is the code from the macro:

Sub LoopFilesInFolder()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim path As String
    Dim file As String
    Dim extension As String
    Dim myFileName As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set wb1 = ActiveWorkbook

    path = ActiveWorkbook.path & "\csvs\"

    extension = "*.csv"
    file = Dir(path & extension)

    Do While file <> ""

        Set wb2 = Workbooks.Open(Filename:=path & file)
        wb2.Activate

        'this section is for the avail heads file, basically it just opens it and copies the info to the template
        If wb2.Name = "avail_heads.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B88").PasteSpecial xlPasteValues
        End If

        'this section is for the forecast file, basically it just opens it and copies the info to the template
        If wb2.Name = "forecast.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B74").PasteSpecial xlPasteValues
        End If

        'this section is for the income file, basically it just opens it and copies the info to the template
        If wb2.Name = "income volume.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B3").PasteSpecial xlPasteValues
        End If

        'this section is for the outgoing volume file, basically it just opens it and copies the info to the template
        If wb2.Name = "outgoing_volume.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B36").PasteSpecial xlPasteValues
        End If

        'this section is for the required heads file, basically it just opens it and copies the info to the template
        If wb2.Name = "required_heads.csv" Then
            Range("A1").Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Copy

            wb1.Activate

            Worksheets("raw data").Range("B102").PasteSpecial xlPasteValues
        End If

        wb2.Close
        file = Dir

    Loop

    'myFileName = ActiveWorkbook.path & "\forecast_for_w" & Format(Now, "ww") + 1

    myFileName = ActiveWorkbook.path & "\yoda_forecast"

    ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal

    'MsgBox "Done!"

    Application.DisplayAlerts = True

End Sub
Improve this question

3 Answers 3

Reset to default
4

I have had this on bounty for almost a week and I really don know how many people have had this issue before. After tearing my hair out for over a week and considering multiple times to jump out the office window, I figured what the problem was.

Thee problem is not in python (sort of) but mostly on the VBA code, I just added 

Application.ScreenUpdating = True

At the very end and the problem stopped. Not sure if an excel bug for not updating when the macro is done or a python bug for not allowing the screen to update once the macro has finished. However after doing that everything is fine now.

Thanks!!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You need to set the Visibility to True after dispatching:

xl = win32com.client.Dispatch("Excel.Application")
xl.Application.Visible = True

Also, calling the macro shouldn't need the ThisWorkbook:

xl.Application.Run("LoopFilesInFolder")

To be able to close a workbook again, you need to assign it to a variable:

wb = xl.Workbooks.Open(Filename=name, ReadOnly=1)
wb.Close(SaveChanges=False)

I wrote the package xlwings to make things easier:

from xlwings import Workbook, Range
wb = Workbook(r'C:\path\to\workbook.xlsx')  # open a workbook if not open yet
Range('A1').value = 123  # Write a value to cell A1
wb.close()  # close the workbook again

Running a Macro hasn't been implemented yet, but there's an issue open for that. In the meantime, you can workaround this by doing (following from above):

wb.xl_app.Run("macro_name")
Improve this answer

2 Comments

Hi! I tried to add xl.Application.Visible = True and I keep running into the same issue. In order to get rid of the grey screen I need to run another macro and it will look normal again. Any thoughts?
Actually I can't post the entire code, but I added all my imports and the code from the macro, if i run only the code above, i get the same behavior.
0

Running Excel Macro from Python

To Run a Excel Marcro from python, You don't need almost nothing. Below a script that does the job. The advantage of Updating data from a macro inside Excel is that you immediatly see the result. You don't have to save or close the workbook first. I use this methode to update real-time stock quotes. It is fast and stable. This is just an example, but you can do anything with macros inside Excel.

from os import system, path
import win32com.client as win32
from time import sleep

def isWorkbookOpen(xlPath, xlFileName):
    SeachXl = xlPath + "~$" + xlFileName
    if path.exists(SeachXl):
        return True
    else:
        return False

def xlRunMacro(macroLink):
    PathFile = macroLink[0]
    xlMacro = macroLink[1]
    isLinkReady = False

    # Create the link with the open existing workbook
    win32.pythoncom.CoInitialize()
    xl = win32.Dispatch("Excel.Application")
    try:
        wb = win32.GetObject(PathFile)
        isLinkReady = True
    except:
        NoteToAdd = 'Can not create the link with ' + PathFile
        print(NoteToAdd)
    if isLinkReady:
        # If the link with the workbook exist, then run the Excel macro
        try:
            xl.Application.Run(xlMacro)
        except:
            NoteToAdd = 'Running Excel Macro ' + xlMacro + ' failed !!!'
            print(NoteToAdd)
    del xl

def mainProgam(macroSettings):
    FullMacroLink = []
    PathFile = macroSettings[0] + macroSettings[1]
    FullMacroLink.append(PathFile)
    FullModuleSubrout = macroSettings[1] + '!' + macroSettings[2] + '.' + macroSettings[3]
    FullMacroLink.append(FullModuleSubrout)
    if isWorkbookOpen(macroSettings[0], macroSettings[1]) == False:
        # If the workbook is not open, Open workbook first.
        system(f'start excel.exe "{PathFile}"')
        # Give some time to start up Excel
        sleep(2)
    xlRunMacro(FullMacroLink)

def main():
    macroSettings = []
    # The settings below will execute the macro example
    xlPath = r'C:\test\\'               # Change add your needs
    macroSettings.append(xlPath)
    workbookName = 'Example.xlsm'       # Change add your needs
    macroSettings.append(workbookName)
    xlModule = "Updates"                # Change add your needs
    macroSettings.append(xlModule)
    xlSubroutine = "UpdateCurrentTime"  # Change add your needs
    macroSettings.append(xlSubroutine)
    mainProgam(macroSettings)

if __name__ == "__main__":
    main()
    exit()

VBA Excel Macro

Option Explicit

Sub UpdateCurrentTime()
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets("Current-Time")
    With sht
        sht.Cells(2, 1).Value = Format(Now(), "hh:mm:ss")
    End With
End Sub

Screen print from module and macro inside Visual Basic Editor

You can use it also as a dynamic module too. Save the module above as RunExcelMacro.py in Your python project. After just use the following lines:

from RunExcelMacro import mainProgam
mainProgram(macroSettings)

It will do the job, succes ...

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.