Using Python to Call Excel Macro That Opens All Files in a Folder
<p>I have a simple python program (shown here in block 1), that I am using to open an Excel file and call a macro (shown here in block 2).  The macro opens all excel files in a specific folder. The Python I have works to open the Excel files and call macros (I've tested it with different files), but for some reason when I try to call this file with this macro it doesn't work (opens the excel file, but the macro doesn't run to open all xlsx files in the folder). Has anyone encountered this before? If so, have you been able to resolve the issue?
<strong><em>Here is the Python</em></strong></p>

<pre><code>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)
</code></pre>

<p><strong>and here is the VBA</strong></p>

<pre><code>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 &amp; myExtension)

  Do While myFile &lt;&gt; ""
      Set wb = Workbooks.Open(Filename:=myPath &amp; myFile)
      myFile = Dir
  Loop

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

End Sub
</code></pre>

<p>why on eartth would you do this????</p>

<pre><code>import glob

for fnam in glob.glob("K:\Bloomberg Data\*.xlsx"):
    os.startfile(fname)
</code></pre>

<p>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)</p>

python excel vba macros