def list_names(cls, workbook, sheets):
list_sheet = workbook.Worksheets.Add()
#list_sheet.Name = "BKT LISTE NAMEN"
xllib.rename_sheet(list_sheet, "BKT LISTE NAMEN")
cls._create_list_header(list_sheet, ["Typ", "Name", "Bezug", "Bereich"])
#list_sheet.Range("A2").ListNames()
cur_row = 2
for name in iter(workbook.Names):
if not name.Visible:
continue
ident = name.NameLocal.split("!",1)
list_sheet.Cells(cur_row,1).Value = "Name"
list_sheet.Cells(cur_row,2).Value = "'" + ident[-1] #last element
list_sheet.Cells(cur_row,3).Value = "'" + name.RefersToLocal
list_sheet.Cells(cur_row,4).Value = "Arbeitsmappe" if len(ident) == 1 else ident[0].strip("'")
cur_row += 1
for sheet in sheets:
for obj in iter(sheet.ListObjects):
list_sheet.Cells(cur_row,1).Value = "Tabelle"
list_sheet.Cells(cur_row,2).Value = "'" + obj.Name #FIXME: use DisplayName instead???
list_sheet.Cells(cur_row,3).Value = "'=" + xllib.get_address_external(obj.Range, True, True)
list_sheet.Cells(cur_row,4).Value = "Arbeitsmappe"
cur_row += 1
list_sheet.UsedRange.Columns.AutoFit()
def loop(worker):
application.StatusBar = "Erstelle Dateiliste"
worker.ReportProgress(0, "Erstelle Dateiliste")
sheet = workbook.Worksheets.Add()
xllib.rename_sheet(sheet, "BKT DATEILISTE")
if folders_only:
cls._create_list_header(sheet, ["Name", "Anz. Ordner", "Anz. Dateien", "Erstellt", "Geändert", "Tiefe", "Übergeordneter Ordner", "Relativer Pfad", "Voller Pfad"], 2)
else:
cls._create_list_header(sheet, ["Name", "Typ", "Größe", "Erstellt", "Geändert", "Tiefe", "Übergeordneter Ordner", "Relativer Pfad", "Voller Pfad"], 2)
total = cls._create_file_list(worker, application, folder, sheet, 3, recursive, folders_only)
total -= 3
worker.ReportProgress(100, "Fertigstellen...")
application.StatusBar = False
application.ActiveWindow.ScrollRow = 1
sheet.Outline.SummaryRow = 0 #xlAbove
sheet.Range("A3").Select()
application.ActiveWindow.FreezePanes = True
sheet.UsedRange.Columns.AutoFit()
sheet.UsedRange.AutoFilter()
if worker.CancellationPending:
sheet.Cells(1,1).Value = "ABBRUCH der Dateiliste nach " + str(total) + " Dateien für Ordner: " + os.path.normpath(folder)
else:
sheet.Cells(1,1).Value = "Dateiliste mit " + str(total) + " Dateien für Ordner: " + os.path.normpath(folder)
def list_properties(cls, workbook):
doctypes = {
1: "Zahl",
2: "Ja/Nein",
3: "Datum",
4: "Text",
5: "Zahl",
}
list_sheet = workbook.Worksheets.Add()
xllib.rename_sheet(list_sheet, "BKT LISTE DOKU. EIG.")
cls._create_list_header(list_sheet, ["Typ", "Name", "Wert", "Datentyp"])
cur_row = 2
for prop in iter(workbook.BuiltinDocumentProperties):
list_sheet.Cells(cur_row,1).Value = "Standard"
try:
list_sheet.Cells(cur_row,3).Value = prop.Value()
list_sheet.Cells(cur_row,2).Value = prop.Name()
list_sheet.Cells(cur_row,4).Value = doctypes[prop.Type()]
cur_row += 1
except:
pass
for prop in iter(workbook.CustomDocumentProperties):
list_sheet.Cells(cur_row,1).Value = "Benutzerdefiniert"
try:
list_sheet.Cells(cur_row,3).Value = prop.Value()
list_sheet.Cells(cur_row,2).Value = prop.Name()
list_sheet.Cells(cur_row,4).Value = doctypes[prop.Type()]
cur_row += 1
except:
pass
list_sheet.UsedRange.Columns.AutoFit()
def list_comments(cls, sheet, workbook):
list_sheet = workbook.Worksheets.Add()
xllib.rename_sheet(list_sheet, "BKT LISTE KOMMENTARE")
cls._create_list_header(list_sheet, ["Zelle", "Autor", "Text"])
cur_row = 2
list_sheet.Range("C:C").ColumnWidth = 30
for comment in iter(sheet.Comments):
list_sheet.Cells(cur_row,1).Value = comment.Parent.AddressLocal(False, False)
list_sheet.Cells(cur_row,2).Value = comment.Author
list_sheet.Cells(cur_row,3).Value = comment.Text()
cur_row += 1
list_sheet.UsedRange.Columns.AutoFit()
def list_cond_formats(cls, sheet, workbook):
def _dict_by_value(input_dict, search_value):
for key, value in input_dict.iteritems():
if value == search_value:
return key
def _getattr(obj, name, default=None):
try:
return getattr(obj, name, default)
except:
return default
def _copy_values(from_obj, to_obj, attribute_list):
for attr in attribute_list:
val = _getattr(from_obj, attr, None)
if val is not DBNull and val is not None:
setattr(to_obj, attr, val)
list_sheet = workbook.Worksheets.Add()
xllib.rename_sheet(list_sheet, "BKT LISTE BEN. FORMAT.")
cls._create_list_header(list_sheet, ["Priorität", "Typ", "Formel 1", "Formel 2", "Text", "Operator", "Format", "Bereich", "Anhalten"])
cur_row = 2
# IMPORTANT LINE! For some reason excel crashs when accessing border/font color if sheet is not active
sheet.Activate()
for fcond in iter(sheet.Cells.FormatConditions):
list_sheet.Cells(cur_row,1).Value = fcond.Priority
list_sheet.Cells(cur_row,2).Value = _dict_by_value(xlcon.XlFormatConditionType, fcond.Type)
list_sheet.Cells(cur_row,3).Value = "'" + _getattr(fcond, "Formula1", '')
list_sheet.Cells(cur_row,4).Value = "'" + _getattr(fcond, "Formula2", '')
list_sheet.Cells(cur_row,5).Value = "'" + _getattr(fcond, "Text", '')
operator = _getattr(fcond, "Operator", None)
list_sheet.Cells(cur_row,6).Value = None if operator is None else _dict_by_value(xlcon.XlFormatConditionOperator, operator)
#Format
list_sheet.Cells(cur_row,7).Value = "AaBbCcYyZz"
_copy_values(fcond.Interior, list_sheet.Cells(cur_row,7).Interior, ["Color", "Pattern", "PatternColor"])
_copy_values(fcond.Borders, list_sheet.Cells(cur_row,7).Borders, ["Color", "LineStyle", "Weight"])
_copy_values(fcond.Font, list_sheet.Cells(cur_row,7).Font, ["Color", "FontStyle"])
list_sheet.Cells(cur_row,8).Value = "'=" + xllib.get_address_external(fcond.AppliesTo, True, True)
list_sheet.Cells(cur_row,9).Value = "X" if fcond.StopIfTrue else None
cur_row += 1
list_sheet.Activate()
list_sheet.UsedRange.Columns.AutoFit()
def list_workbooks(cls, workbook, application):
list_sheet = workbook.Worksheets.Add()
#list_sheet.Name = "BKT LISTE ARBEITSMAPPEN"
xllib.rename_sheet(list_sheet, "BKT LISTE ARBEITSMAPPEN")
cls._create_list_header(list_sheet, ["Name", "Ordner", "Pfad", "Anz. Blätter", "Liste Blätter"])
cur_row = 2
for wb in list(iter(application.Workbooks)):
list_sheet.Cells(cur_row,1).Value = wb.Name
list_sheet.Cells(cur_row,2).Value = wb.Path
if wb.FullName:
# list_sheet.Cells(cur_row,3).Value = wb.FullName
list_sheet.Hyperlinks.Add(list_sheet.Cells(cur_row,3), wb.FullName, "", "", wb.FullName) #anchor, address, subaddress, screentip, texttodisplay
list_sheet.Cells(cur_row,4).Value = wb.Worksheets.Count
list_sheet.Cells(cur_row,5).Value = ", ".join([sh.Name for sh in wb.Worksheets if sh.Visible != xlcon.XlSheetVisibility["xlSheetVeryHidden"]])
cur_row += 1
list_sheet.UsedRange.Columns.AutoFit()
def sheets_base_list(cls, workbook, sheets):
list_sheet = workbook.Worksheets.Add()
# explanation = list_sheet.Range("A1:C1")
# explanation.MergeCells = True
# explanation.WrapText = True
# explanation.Value = "Umbenennen: XXX\nSortieren: XXX\nErstellen: XXX"
xllib.rename_sheet(list_sheet, "BKT MULTIEDIT")
cls._create_list_header(list_sheet, ["#", "Alter Name", "Neuer Name"], row=1)
cur_row = 2
for i, sheet in enumerate(sheets, start=1):
if sheet.Visible != xlcon.XlSheetVisibility["xlSheetVisible"] or sheet.Type != xlcon.XlSheetType["xlWorksheet"]:
continue
list_sheet.Cells(cur_row,1).Value = i
list_sheet.Cells(cur_row,2).Value = sheet.Name
cur_row += 1
list_sheet.UsedRange.Columns.AutoFit()
def list_sheets(cls, workbook, sheets):
list_sheet = workbook.Worksheets.Add()
#list_sheet.Name = "BKT LISTE BLÄTTER"
xllib.rename_sheet(list_sheet, "BKT LISTE BLÄTTER")
cls._create_list_header(list_sheet, ["Name", "Genutzter Bereich", "Zeilen", "Spalten", "Tab-Farbe", "Sichtbar", "Geschützt"])
cur_row = 2
for sheet in sheets:
if sheet.Visible == xlcon.XlSheetVisibility["xlSheetVeryHidden"]:
continue
if sheet.Type == xlcon.XlSheetType["xlWorksheet"]:
list_sheet.Hyperlinks.Add(list_sheet.Cells(cur_row,1), "", "'" + sheet.Name + "'!A1", "", sheet.Name) #anchor, address, subaddress, screentip, texttodisplay
list_sheet.Cells(cur_row,2).Value = "'=" + xllib.get_address_external(sheet.UsedRange, True, True)
list_sheet.Cells(cur_row,3).Value = sheet.UsedRange.Rows.Count
list_sheet.Cells(cur_row,4).Value = sheet.UsedRange.Columns.Count
if sheet.Tab.Color:
list_sheet.Cells(cur_row,5).Interior.Color = sheet.Tab.Color
list_sheet.Cells(cur_row,6).Value = "X" if sheet.Visible == xlcon.XlSheetVisibility["xlSheetVisible"] else None
list_sheet.Cells(cur_row,7).Value = "X" if sheet.ProtectContents else None
else:
list_sheet.Cells(cur_row,1).Value = sheet.Name
cur_row += 1
list_sheet.UsedRange.Columns.AutoFit()
def consolidate_worksheets(cls, workbook, sheet, sheets, selected_sheets, application):
dropdown = ["[UsedRange]", "[Selection]", sheet.UsedRange.AddressLocal(False, False)]
#TODO: [TableRange] einfügen mit automatischer Erkennung der Tabellen in einem Sheet inkl. Kopfzeile und Ergebniszeile
#if area selected, take address address as default
selection = application.ActiveWindow.RangeSelection
if selection and selection.Cells.Count > 1:
default_range = selection.AddressLocal(False, False)
default_skip = 0
dropdown.append(default_range)
else:
default_range = "[UsedRange]"
default_skip = 1
#Add ranges of defined names to dropdown
for name in list(iter(workbook.Names)):
try:
dropdown.append(name.RefersToRange.AddressLocal(False, False))
except:
pass
pastemode_list = ["Alles einfügen", "Werte", "Werte und Zahlenformate", "Werte und Quellformatierung", "Formeln", "Formeln und Zahlenformate", "Formeln und Quellformatierung", "Referenzen", "Referenzen und Quellformatierung"]
pastemode_values = [
[xlcon.XlPasteType["xlPasteAll"]],
[xlcon.XlPasteType["xlPasteValues"]],
[xlcon.XlPasteType["xlPasteValuesAndNumberFormats"]],
[xlcon.XlPasteType["xlPasteValues"], xlcon.XlPasteType["xlPasteFormats"]],
[xlcon.XlPasteType["xlPasteFormulas"]],
[xlcon.XlPasteType["xlPasteFormulasAndNumberFormats"]],
[xlcon.XlPasteType["xlPasteFormulas"], xlcon.XlPasteType["xlPasteFormats"]],
["PASTE_LINK"],
["PASTE_LINK", xlcon.XlPasteType["xlPasteFormats"]]
]
#generate list for checked listbox, if multiple sheets are selected mark them as checked, otherwise all are checked
sel_worksheets = cls._get_worksheet_list(sheets, selected_sheets)
#TODO: allow re-ordering ot sheets
user_form = bkt.ui.UserInputBox("Diese Funktion kopiert die Zellen mehrerer Arbeitsblätter in ein Blatt. Bitte die Arbeitsblätter zur Konsolidierung auswählen:", "Arbeitsblätter konsolidieren")
user_form._add_checked_listbox("sel_worksheets", sel_worksheets)
user_form._add_label("Bereich zum Konsolidieren eingeben, d.h. eine benannter Bereich oder eine Adresse wie A1:D5. [UsedRange] ermittelt automatisch den genutzten Bereich je Arbeitsblatt. [Selection] nimmt den jeweils im Sheet ausgewählten Bereich.")
user_form._add_combobox("range", default_range, dropdown)
user_form._add_label("Zeilen überspringen, z.B. für Titelzeilen:")
user_form._add_spinner("skip_rows", default_skip, max_value=sheet.Cells.Rows.Count-1)
user_form._add_checkbox("insert_skip_rows", "Übersprungene Zeilen aus erstem Blatt einfügen (bspw. Überschriften)", True)
user_form._add_label("Zeilen abtrennen, z.B. für Ergebnis-/Summenzeilen:")
user_form._add_spinner("cut_rows", 0, max_value=sheet.Cells.Rows.Count-1)
user_form._add_checkbox("insert_sheet_names", "Jeweiligen Blattnamen als erste Spalte einfügen")
user_form._add_label("Einfügemodus:")
user_form._add_combobox("pastemode", dropdown=pastemode_list, selected_index=0, editable=False, return_value="SelectedIndex")
form_return = user_form.show()
if len(form_return) == 0:
return
#worksheets to be consolidated
sel_worksheets = form_return["sel_worksheets"]
if len(sel_worksheets) == 0:
bkt.helpers.message("Keine Blätter ausgewählt.")
return
#Number of skipped rows
try:
skip_rows = form_return["skip_rows"]
skip_rows = 0 if skip_rows == '' else int(skip_rows)
cut_rows = form_return["cut_rows"]
cut_rows = 0 if cut_rows == '' else int(cut_rows)
except:
bkt.helpers.message("Fehler, Eingabe ist keine Zahl!")
return
err_counter = 0
insert_skip_rows = form_return["insert_skip_rows"]
insert_column = 1 if not form_return["insert_sheet_names"] else 2
insert_row = 1 if insert_skip_rows else skip_rows+1
xllib.freeze_app(disable_display_alerts=True)
application.StatusBar = "Konsolidiere Blätter"
paste_types = pastemode_values[form_return["pastemode"]]
new_sheet = workbook.Worksheets.Add()
#new_sheet.Name = "BKT KONSOLIDIERUNG"
xllib.rename_sheet(new_sheet, "BKT KONSOLIDIERUNG")
cur_cell = new_sheet.Cells(insert_row, insert_column)
for sheet in sheets:
if sheet.Name not in sel_worksheets:
continue
application.StatusBar = "Konsolidiere Blatt " + sheet.Name
try:
#Determine range to copy
if form_return["range"] == "[UsedRange]":
rng_to_copy = sheet.UsedRange
elif form_return["range"] == "[Selection]":
sheet.Activate()
rng_to_copy = application.ActiveWindow.RangeSelection
new_sheet.Activate()
else:
rng_to_copy = sheet.Range(form_return["range"])
#FIXME: Rows.Count does not return correct value for multiple areas (max of all area rows)
rows_to_insert = rng_to_copy.Rows.Count
rows_to_skip = skip_rows
#Reduce rows if rows should be cut
if cut_rows > 0:
rows_to_insert -= cut_rows
#Reduce rows if rows should be skipped
if skip_rows > 0:
if insert_skip_rows:
#Insert skipped rows in first iteration => no shift in first iteration
insert_skip_rows = False
rows_to_skip = 0
else:
rows_to_insert -= skip_rows
#If no rows to insert continue
if rows_to_insert <= 0:
continue
#FIXME: Resize does not work for multiple areas! This is a workaround until method can handle multiple areas.
if rng_to_copy.Areas.Count == 1:
rng_to_copy = rng_to_copy.Offset(rows_to_skip,0).Resize(rows_to_insert)
#Copy action
rng_to_copy.Copy()
#Paste values/formats on current cell
for ptype in paste_types:
if ptype == "PASTE_LINK":
cur_cell.Select()
cur_cell.Parent.Paste(Link=True)
else:
cur_cell.PasteSpecial(ptype)
rows_pasted = new_sheet.UsedRange.Row + new_sheet.UsedRange.Rows.Count - cur_cell.Row
#Insert sheet name as first column
if form_return["insert_sheet_names"]:
cur_cell.Offset(0,-1).Resize(rows_pasted).Value = sheet.Name
#cur_cell.Offset(0,-1).Resize(rng_to_copy.Rows.Count).Value = sheet.Name
cur_cell = new_sheet.Cells(cur_cell.Row + rows_pasted, insert_column)
except:
#bkt.helpers.exception_as_message()
err_counter += 1
new_sheet.UsedRange.Columns.AutoFit()
new_sheet.Range("A1").Select()
new_sheet.Activate()
application.StatusBar = False
xllib.unfreeze_app()
if err_counter > 0:
bkt.helpers.message("Fehler! " + str(err_counter) + " Blatt/Blätter konnte(n) nicht konsolidiert werden.")
def _consolidate_workbooks(workbooks, sheets, application):
user_form = bkt.ui.UserInputBox("Diese Funktion kopiert die Blätter mehrerer Arbeitsmappen in eine Mappe. Bitte die Arbeitsmappen zur Konsolidierung auswählen:", "Arbeitsmappen konsolidieren")
user_form._add_checked_listbox("sel_workbooks", workbooks)
user_form._add_label("Komma-getrennte Liste von Blattnamen, die ausschließlich konsolidiert werden:")
user_form._add_combobox("include_sheets", dropdown=sheets)
user_form._add_label("Komma-getrennte Liste von Blattnamen, die nicht konsolidiert werden:")
user_form._add_combobox("exclude_sheets", dropdown=sheets)
user_form._add_checkbox("deduplicate", "Doppelte Blätter bzw. Blattnamen nur einmal kopieren")
user_form._add_checkbox("include_hidden", "Versteckte Blätter kopieren", True)
user_form._add_checkbox("add_wb_name", "Name der Arbeitsmappe vor Blattnamen schreiben")
user_form._add_checkbox("add_report", "Neues Blatt mit Zusammenfassung der Konsolidierung einfügen", True)
form_return = user_form.show()
if len(form_return) == 0:
return
#sel_workbooks = list(form_return["sel_workbooks"].Item)
sel_workbooks = form_return["sel_workbooks"]
if len(sel_workbooks) == 0:
bkt.helpers.message("Keine Arbeitsmappen ausgwählt.")
return
if form_return["exclude_sheets"] == '':
exclude_sheets = []
else:
exclude_sheets = form_return["exclude_sheets"].split(',')
exclude_sheets = map(str.strip, exclude_sheets)
if form_return["include_sheets"] == '':
include_sheets = []
else:
include_sheets = form_return["include_sheets"].split(',')
include_sheets = map(str.strip, include_sheets)
xllib.freeze_app(disable_display_alerts=True)
application.StatusBar = "Konsolidiere Mappen"
#Create new workbook and store created default sheets
new_wb = application.Workbooks.Add()
new_wb_sheets = list(iter(new_wb.Sheets))
#Rename created default sheets
for i, sheet in enumerate(new_wb_sheets):
sheet.Name = "BKT_TEMP_" + str(i)
err_counter = 0
report = []
all_sheets = set()
for wb_name in sel_workbooks:
application.StatusBar = "Konsolidiere Mappe " + wb_name
#Test if workbook is open, otherwise open it in read-only mode
close = False
try:
cur_wb = application.Workbooks[os.path.basename(wb_name)]
except:
try:
cur_wb = application.Workbooks.Open(wb_name, 0, True, IgnoreReadOnlyRecommended=True) #Open( FileName , UpdateLinks , ReadOnly , Format , Password , WriteResPassword , IgnoreReadOnlyRecommended , Origin , Delimiter , Editable , Notify , Converter , AddToMru , Local , CorruptLoad )
close = True
except:
err_counter +=1
report.append((wb_name, "", "", "FEHLER BEIM ÖFFNEN"))
#bkt.helpers.exception_as_message()
continue
err_counter_sheets = 0
sheets_to_copy = []
orig_sheet_names = []
#Iterate sheets and determine which one to copy and save original name
for cur_sh in list(iter(cur_wb.Sheets)):
if cur_sh.Name in exclude_sheets or \
(len(include_sheets) > 0 and cur_sh.Name not in include_sheets) or \
(cur_sh.Visible != xlcon.XlSheetVisibility["xlSheetVisible"] and not form_return["include_hidden"]):
continue
if form_return["deduplicate"] and cur_sh.Name in all_sheets:
report.append((cur_wb.Name, cur_sh.Name, "", "DUPLIKAT ÜBERSPRUNGEN"))
continue
all_sheets.add(cur_sh.Name)
sheets_to_copy.append(cur_sh.Index)
orig_sheet_names.append(cur_sh.Name)
### OLD METHOD (copy sheets individually):
# try:
# #Copy sheet, store original name, add workbook name to original name if required
# orig_sheet_name = cur_sh.Name
# cur_sh.Copy(After=new_wb.Sheets(new_wb.Sheets.Count))
# new_sh = new_wb.Sheets(new_wb.Sheets.Count)
# if(form_return["add_wb_name"]):
# new_name = cur_wb.Name.rsplit('.', 1)[0] + " " + orig_sheet_name
# xllib.rename_sheet(new_sh, new_name)
# #new_sh.Name = new_name[:31] #max sheet name length is 31 characters
# report.append((cur_wb.Name, orig_sheet_name, new_sh.Name, "OK"))
# except:
# err_counter_sheets += 1
# report.append((cur_wb.Name, cur_sh.Name, "", "FEHLER"))
# #bkt.helpers.exception_as_message()
#Copy and rename sheets
if len(sheets_to_copy) > 0:
cur_index = new_wb.Sheets.Count
cur_wb_name = cur_wb.Name.rsplit('.', 1)[0] #filename without ending
try:
#New window as workaround to copy multiple sheets with tables
tmp_window = cur_wb.NewWindow()
cur_wb.Sheets(System.Array[int](sheets_to_copy)).Copy(After=new_wb.Sheets(cur_index))
tmp_window.Close()
#Rename sheets
for i in range(cur_index+1, cur_index+len(sheets_to_copy)+1):
orig_sheet_name = orig_sheet_names[i-1-cur_index]
new_sh = new_wb.Sheets(i)
if(form_return["add_wb_name"]):
new_name = cur_wb_name + " " + orig_sheet_name
else:
new_name = orig_sheet_name
xllib.rename_sheet(new_sh, new_name)
report.append((cur_wb.Name, orig_sheet_name, new_sh.Name, "OK"))
except:
err_counter_sheets += 1
report.append((cur_wb.Name, "", "", "FEHLER BEIM KOPIEREN"))
#bkt.helpers.exception_as_message()
if err_counter_sheets > 0:
err_counter +=1
if close:
cur_wb.Close(False)
#Delete created default sheets
for sheet in new_wb_sheets:
sheet.Delete()
#Generate report sheet
if(form_return["add_report"]):
list_sheet = new_wb.Worksheets.Add(Before=new_wb.Worksheets(1))
#list_sheet.Name = "BKT KONSOLIDIERUNG"
xllib.rename_sheet(list_sheet, "BKT KONSOLIDIERUNG")
list_sheet.Cells(1,1).Value = "Arbeitsmappe"
list_sheet.Cells(1,2).Value = "Blattname (alt)"
list_sheet.Cells(1,3).Value = "Blattname (neu)"
list_sheet.Cells(1,4).Value = "Status"
list_sheet.Range("A1:D1").Font.Bold = True
cur_row = 2
for wb, sh_old, sh_new, status in report:
new_wb.Sheets(1).Cells(cur_row, 1).Value = wb
new_wb.Sheets(1).Cells(cur_row, 2).Value = sh_old
new_wb.Sheets(1).Cells(cur_row, 3).Value = sh_new
new_wb.Sheets(1).Cells(cur_row, 4).Value = status
cur_row += 1
list_sheet.UsedRange.Columns.AutoFit()
application.StatusBar = False
xllib.unfreeze_app()
if err_counter > 0:
bkt.helpers.message("Fehler! " + str(err_counter) + " Arbeitemappe(n) konnte(n) nicht oder nur teilweise konsolidiert werden.")
