I have an Excel file containing some macros which I wish to run through VBA. Each time I have a different instance of Excel open, the python file opens up the excel workbook containing the macros when I run the code. If I don’t have Excel open at all, this isn’t an issue.
How do I stop Python from opening the Excel file with the macros when I have other instances of Excel open?
import os
import datetime
import win32com.client as win32
from openpyxl import load_workbook
import pandas as pd
from mailersend import *
<code># Paths to the files and directories
network_path_endfile = r"K:namefolder1endfile.xlsm"
network_folder_path_output = r"\networkfilesreports"
# Initialize Excel application
excel_app = win32.Dispatch('Excel.Application')
excel_app.Visible = False
excel_app.DisplayAlerts = False
# Function to run a macro
try:
def run_macro(workbook_path, macro_name):
workbook = excel_app.Workbooks.Open(workbook_path)
excel_app.Application.Run(f"'{workbook.Name}'!{macro_name}")
workbook.Save()
workbook.Close()
# Run the initial macros in endfile.xlsm
# Module 6 in Excel File
run_macro(network_path_endfile, 'Execute_first')
# Module 5 in Excel File
run_macro(network_path_endfile, 'Execute_second')
# Module 1 in Excel File
run_macro(network_path_endfile, 'Execute_third')
# Module 3 in Excel File
run_macro(network_path_endfile, 'Execute_fourth')
# Module 8 in Excel File
run_macro(network_path_endfile, 'Execute_fifth')
# Module 7 in Excel File
run_macro(network_path_endfile, 'Execute_sixth')
# Module 2 in Excel File
run_macro(network_path_endfile, 'Execute_seventh')
# Get the previous business day's date
today = datetime.datetime.today()
previous_business_day = today - datetime.timedelta(days=1)
if today.weekday() == 0: # If today is Monday
previous_business_day -= datetime.timedelta(days=2)
previous_business_day_str = previous_business_day.strftime('%Y%m%d')
# Find the latest file in the network folder
latest_file_name = f"reports_as_of_{previous_business_day_str}.csv"
latest_file_path = os.path.join(network_folder_path_output, latest_file_name)
# Load the latest CSV file
# latest_df = pd.read_csv(latest_file_path)
# Load the endfile.xlsm workbook
endfile_wb = load_workbook(network_path_endfile, data_only=True)
endfile_ws = endfile_wb.active
# Extract data from endfile.xlsm
data = []
for row in endfile_ws.iter_rows(min_row=1, max_col=88, values_only=True):
data.append(row)
# Convert the data to a DataFrame
data_df = pd.DataFrame(data)
# Append the new data to the existing data in the latest CSV file
data_df.to_csv(latest_file_path, index=False, mode='a', header=False)
# updated_df = pd.concat([latest_df, data_df], ignore_index=True)
# Run the final macro in endfile.xlsm -> Module 4
run_macro(network_path_endfile, 'Execute_last')
# Close Excel application
excel_app.Quit()
subject = 'No Errors'
to = ['[email protected]', '[email protected]']
content = "Finished successfully."
ol = win32.Dispatch("outlook.application")
mail = ol.CreateItem(0x0)
mail.Subject = subject
mail.To = ' ;'.join(to)
mail.Body = content
mail.Send()
except:
subject = 'Error'
to = ['[email protected]', '[email protected]']
content = "Failed"
ol = win32.Dispatch("outlook.application")
mail = ol.CreateItem(0x0)
mail.Subject = subject
mail.To = ' ;'.join(to)
mail.Body = content
mail.Send()
exit(1)
</code>
<code># Paths to the files and directories
network_path_endfile = r"K:namefolder1endfile.xlsm"
network_folder_path_output = r"\networkfilesreports"
# Initialize Excel application
excel_app = win32.Dispatch('Excel.Application')
excel_app.Visible = False
excel_app.DisplayAlerts = False
# Function to run a macro
try:
def run_macro(workbook_path, macro_name):
workbook = excel_app.Workbooks.Open(workbook_path)
excel_app.Application.Run(f"'{workbook.Name}'!{macro_name}")
workbook.Save()
workbook.Close()
# Run the initial macros in endfile.xlsm
# Module 6 in Excel File
run_macro(network_path_endfile, 'Execute_first')
# Module 5 in Excel File
run_macro(network_path_endfile, 'Execute_second')
# Module 1 in Excel File
run_macro(network_path_endfile, 'Execute_third')
# Module 3 in Excel File
run_macro(network_path_endfile, 'Execute_fourth')
# Module 8 in Excel File
run_macro(network_path_endfile, 'Execute_fifth')
# Module 7 in Excel File
run_macro(network_path_endfile, 'Execute_sixth')
# Module 2 in Excel File
run_macro(network_path_endfile, 'Execute_seventh')
# Get the previous business day's date
today = datetime.datetime.today()
previous_business_day = today - datetime.timedelta(days=1)
if today.weekday() == 0: # If today is Monday
previous_business_day -= datetime.timedelta(days=2)
previous_business_day_str = previous_business_day.strftime('%Y%m%d')
# Find the latest file in the network folder
latest_file_name = f"reports_as_of_{previous_business_day_str}.csv"
latest_file_path = os.path.join(network_folder_path_output, latest_file_name)
# Load the latest CSV file
# latest_df = pd.read_csv(latest_file_path)
# Load the endfile.xlsm workbook
endfile_wb = load_workbook(network_path_endfile, data_only=True)
endfile_ws = endfile_wb.active
# Extract data from endfile.xlsm
data = []
for row in endfile_ws.iter_rows(min_row=1, max_col=88, values_only=True):
data.append(row)
# Convert the data to a DataFrame
data_df = pd.DataFrame(data)
# Append the new data to the existing data in the latest CSV file
data_df.to_csv(latest_file_path, index=False, mode='a', header=False)
# updated_df = pd.concat([latest_df, data_df], ignore_index=True)
# Run the final macro in endfile.xlsm -> Module 4
run_macro(network_path_endfile, 'Execute_last')
# Close Excel application
excel_app.Quit()
subject = 'No Errors'
to = ['[email protected]', '[email protected]']
content = "Finished successfully."
ol = win32.Dispatch("outlook.application")
mail = ol.CreateItem(0x0)
mail.Subject = subject
mail.To = ' ;'.join(to)
mail.Body = content
mail.Send()
except:
subject = 'Error'
to = ['[email protected]', '[email protected]']
content = "Failed"
ol = win32.Dispatch("outlook.application")
mail = ol.CreateItem(0x0)
mail.Subject = subject
mail.To = ' ;'.join(to)
mail.Body = content
mail.Send()
exit(1)
</code>
# Paths to the files and directories
network_path_endfile = r"K:namefolder1endfile.xlsm"
network_folder_path_output = r"\networkfilesreports"
# Initialize Excel application
excel_app = win32.Dispatch('Excel.Application')
excel_app.Visible = False
excel_app.DisplayAlerts = False
# Function to run a macro
try:
def run_macro(workbook_path, macro_name):
workbook = excel_app.Workbooks.Open(workbook_path)
excel_app.Application.Run(f"'{workbook.Name}'!{macro_name}")
workbook.Save()
workbook.Close()
# Run the initial macros in endfile.xlsm
# Module 6 in Excel File
run_macro(network_path_endfile, 'Execute_first')
# Module 5 in Excel File
run_macro(network_path_endfile, 'Execute_second')
# Module 1 in Excel File
run_macro(network_path_endfile, 'Execute_third')
# Module 3 in Excel File
run_macro(network_path_endfile, 'Execute_fourth')
# Module 8 in Excel File
run_macro(network_path_endfile, 'Execute_fifth')
# Module 7 in Excel File
run_macro(network_path_endfile, 'Execute_sixth')
# Module 2 in Excel File
run_macro(network_path_endfile, 'Execute_seventh')
# Get the previous business day's date
today = datetime.datetime.today()
previous_business_day = today - datetime.timedelta(days=1)
if today.weekday() == 0: # If today is Monday
previous_business_day -= datetime.timedelta(days=2)
previous_business_day_str = previous_business_day.strftime('%Y%m%d')
# Find the latest file in the network folder
latest_file_name = f"reports_as_of_{previous_business_day_str}.csv"
latest_file_path = os.path.join(network_folder_path_output, latest_file_name)
# Load the latest CSV file
# latest_df = pd.read_csv(latest_file_path)
# Load the endfile.xlsm workbook
endfile_wb = load_workbook(network_path_endfile, data_only=True)
endfile_ws = endfile_wb.active
# Extract data from endfile.xlsm
data = []
for row in endfile_ws.iter_rows(min_row=1, max_col=88, values_only=True):
data.append(row)
# Convert the data to a DataFrame
data_df = pd.DataFrame(data)
# Append the new data to the existing data in the latest CSV file
data_df.to_csv(latest_file_path, index=False, mode='a', header=False)
# updated_df = pd.concat([latest_df, data_df], ignore_index=True)
# Run the final macro in endfile.xlsm -> Module 4
run_macro(network_path_endfile, 'Execute_last')
# Close Excel application
excel_app.Quit()
subject = 'No Errors'
to = ['[email protected]', '[email protected]']
content = "Finished successfully."
ol = win32.Dispatch("outlook.application")
mail = ol.CreateItem(0x0)
mail.Subject = subject
mail.To = ' ;'.join(to)
mail.Body = content
mail.Send()
except:
subject = 'Error'
to = ['[email protected]', '[email protected]']
content = "Failed"
ol = win32.Dispatch("outlook.application")
mail = ol.CreateItem(0x0)
mail.Subject = subject
mail.To = ' ;'.join(to)
mail.Body = content
mail.Send()
exit(1)