CODEWITHSUNDEEP
pythonexcelvba
DForge
DForge

Reputation: 11

Using Python to Call Excel Macro That Opens All Files in a Folder

I have a Python program (shown in block 1), to open an Excel file and call a macro.

The Excel macro (shown in block 2), opens all Excel files in a specific folder.

The Python program opens Excel files and calls macros, I've tested it with different files, but when I try this Excel file, it opens but the macro doesn't run.

Here is the Python

from win32com.client import Dispatch
xl = Dispatch('Excel.Application')
wb = xl.Workbooks.Open("K:\\updatebloomberg.xlsm")
xl.Visible = True
wb.Application.Run("'updatebloomberg.xlsm'!Module1.runfiles()")
wb.Close(True)

and here is the VBA

Sub runfiles()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim ws As Worksheet
Dim c As Range

  myPath = "K:\Bloomberg Data\"

  myExtension = "*.xlsx"

  myFile = Dir(myPath & myExtension)

  Do While myFile <> ""
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      myFile = Dir
  Loop

Application.OnTime (Now + TimeValue("00:00:02")), "refreshSheet"

End Sub

Upvotes: 1

Views: 1383

Answers (1)

Joran Beasley
Joran Beasley

Reputation: 113988

why on eartth would you do this????

import glob

for fnam in glob.glob("K:\Bloomberg Data\*.xlsx"):
    os.startfile(fname)

if that doesnt work have you checked that your network drive ((K i assume is a network drive) doesnt have an X over it when you look in file explorer)

Upvotes: 3

Related Questions