class Excel(): def __init__(self, excelFile): ''' Parameters file: str, excel file ''' CoInitialize() self.excelCOM = DispatchEx('Excel.Application') self.excelBook = self.excelCOM.Workbooks.Open(excelFile) def get_cell(self, sheet, loc=None, row=None, col=None): ''' Parameters sheet: str, sheet name loc: cell location, equivalent to col+row row: int or str, row index col: str, column index Returns cellValue: num or str, cell value (after calculation) ''' sht = self.excelBook.Worksheets(sheet) if loc != None: cellValue = sht.Evaluate(loc).Value elif row != None and col != None: cellValue = sht.Cells(row, col).Value return cellValue def set_cell(self, value, sheet, loc=None, row=None, col=None): ''' Parameters value: num or str, value to set sheet: str, sheet name loc: cell location, equivalent to col+row row: int or str, row index col: str, column index ''' sht = self.excelBook.Worksheets(sheet) if loc != None: sht.Evaluate(loc).Value = value elif row != None and col != None: sht.Cells(row, col).Value = value def load_aspenModel(self, aspenFile): ''' Parameters aspenFile: str, aspen file ''' self.set_cell(aspenFile, 'Set-up', 'B1') self.run_macro('sub_ClearSumData_ASPEN') self.run_macro('sub_GetSumData_ASPEN') def run_macro(self, macro): ''' Parameters macro: str, macro ''' self.excelCOM.Run(macro) def close(self): self.excelBook.Close(SaveChanges=0)
Sub Insert_Sales() Application.ScreenUpdating = False Dim EndRow As Long EndRow = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row Sheets(3).Range("J3:J" & EndRow).Formula = "=VLOOKUP(A3,'GM GateKeeper'!C:Q,15,FALSE)" Sheets(3).Range("K3:K" & EndRow).Formula = "=HLOOKUP(J3,M:O,P3,FALSE)" End Sub ''' mod = wb.VBProject.VBComponents.Add(1) mod.CodeModule.AddFromString(salesCode) xl.Run("Insert_Sales") gkCode = ''' Sub Insert_Formula() Dim LastRow As Long Application.ScreenUpdating = False LastRow = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row Sheets(1).Range("B2:B" & LastRow).Formula = "=CONCAT(G2,""|"",N2,""|"",Q2)" Sheets(1).Range("R2:R" & LastRow).Formula = "=ROUND(S2*(T2+U2*.6)/5,0)*5" Sheets(1).Range("S2:S" & LastRow).Formula = 1 Sheets(1).Range("T2:T" & LastRow).Formula = "=IFERROR(VLOOKUP(B2,'GM HISTORY'!A:AG,33,FALSE),"""")"