Process multiple Excel workbooks through one macro enabled workbook one at a time?

Question

OK, so I've got one workbook that processes data from all export workbooks. I need to take every new export workbook and run it through the processing workbook one at a time in chronological order of when they are submitted then rename the finished workbook to the original name.

I have a script to run the Export through the processing book and export it to a finished book. but I cant seem to figure out how to queue the uploaded sheets to run through one at a time and maintain the original sheet name in the finished book. here is the code for the gui and processing script.

If someone could point me in the right direction it would be much appreciated.

#gui
from tkinter import filedialog
from tkinter import *
import wx
import shutil

class MyFrame(wx.Frame):
    def __init__(self):
        super().__init__(parent=None, title='BIMgen Employee Portal')
        panel = wx.Panel(self)
        my_sizer = wx.BoxSizer(wx.VERTICAL)
        my_btn = wx.Button(panel, label='Select Export')
        my_btn.Bind(wx.EVT_BUTTON, self.on_press)
        my_sizer.Add(my_btn, 0, wx.ALL | wx.CENTER, 5)
        panel.SetSizer(my_sizer)
        self.Show()

    def on_press(self, event):
        root = Tk()
        root.filename =  filedialog.askopenfilename(initialdir = "/",title = "Select file",filetypes = (("jpeg files","*.jpg"),("all files","*.*")))
        print (root.filename)
        print(print (root.filename))
        shutil.copy(root.filename, "C:/Users/tyler/Desktop/Beef Web/Queue")
        wx.MessageBox(root.filename, 'Export Uploaded & Queued',
            wx.OK | wx.ICON_INFORMATION)

if __name__ == '__main__':
    app = wx.App()
    frame = MyFrame()
    app.MainLoop()

#Prossecing script
import win32com.client
import openpyxl

def CopyIn_run_macros():
    #Run macros
    xl=win32com.client.Dispatch('Excel.Application')
    BC=xl.Workbooks.Open('C:/Users/tyler/Desktop/Beef Web/Wall Types Live.xlsm')
    xl.Application.Run('Python2')
    EXP=xl.Workbooks.Open('C:/Users/tyler/Desktop/Beef Web/Export.xlsx')
    print("Importing Export To BeefCake....")
    EXP.Sheets('Export').Range('A2:K100000').Copy()
    BC.Sheets('Raw Export').Paste(BC.Sheets('Raw Export').Range('A12'))
    EXP.Close(True)
    print("Complete.")
    print("Running Macros....")
    xl.Application.Run('Python')
    print("Complete.")
    print("Exporting BeefCake Data....")
    FIN=xl.Workbooks.Open('C:/Users/tyler/Desktop/Beef Web/Finish.xlsx')
    BC.Sheets('E1 Final').Range('A1:T100000').Copy()
    FIN.Sheets('E1').Paste(FIN.Sheets('E1').Range('A1'))
    BC.Sheets('BIMgen 2.0').Range('A1:H100000').Copy()
    FIN.Sheets('BIMgen').Paste(FIN.Sheets('BIMgen').Range('A1'))
    BC.Sheets('Goodall Build').Range('A1:AG100000').Copy()
    FIN.Sheets('231').Paste(FIN.Sheets('231').Range('A1'))
    FIN.Close(True)
    print("Complete.")
    del xl


CopyIn_run_macros()

score 0 · Answer 1 · answered Feb 06 '20 at 17:32

Solved it. here is the code I used. just a simple for loop.

import win32com.client
import openpyxl
from openpyxl import load_workbook
import shutil
import os, subprocess

for filename in os.listdir(input):
   xl=win32com.client.Dispatch('Excel.Application')
   print("Getting file....")
   ini=xl.Workbooks.Open(input + '/' + filename)
   print("Complete.")
   print("Uploading Data To input sheet....")
   ini.Sheets('Sheet1').Range('A2:K100000').Copy()
   EXP.Sheets('Export').Paste(EXP.Sheets('Export').Range('A2'))
   print("Complete.")
   CopyIn_run_macros()
   print("Adding sheets to upload....")
   ini.Close(True)
   wb = load_workbook(input + '/' + filename)
   wb.create_sheet("E1")
   wb.create_sheet("231")
   wb.create_sheet("BIMgen")
   wb.save(input + '/' + filename)
   del xl
   xl=win32com.client.Dispatch('Excel.Application')
   ini=xl.Workbooks.Open(input + '/' + filename)
   print("Complete.")
   print("Exporting BeefCake data to upload....")
   FIN.Sheets('E1').Range('A1:T100000').Copy()
   ini.Sheets('E1').Paste(ini.Sheets('E1').Range('A1'))
   FIN.Sheets('BIMgen').Range('A1:H100000').Copy()
   ini.Sheets('BIMgen').Paste(ini.Sheets('BIMgen').Range('A1'))
   FIN.Sheets('231').Range('A1:AG100000').Copy()
   ini.Sheets('231').Paste(ini.Sheets('231').Range('A1'))
   ini.Close(True)
   print("Complete.")
   print("Moving Upload to Output")
   shutil.move(input + '/' + filename, output)
   print("Complete.")

Process multiple Excel workbooks through one macro enabled workbook one at a time?

1 Answers1