excel

How do I make a progress bar for loading pandas DataFrame from a large xlsx file?

by

The following is a one-liner solution utilizing tqdm: import pandas as pd from tqdm import tqdm df = pd.concat([chunk for chunk in tqdm(pd.read_csv(file_name, chunksize=1000), desc=”Loading data”)]) If you know the total lines to be loaded, you can add that information with the parameter total to the tqdm fuction, resulting in a percentage output.

How to put Excel VBA into git

by

If you use Rubberduck VBA, after clicking You can use the file menu to “Export Active Project”, which exports the form binaries and the code as BAS objects, which are just plain text. Then you can commit to git.

If Cell Starts with Text String… Formula

by

I’m not sure lookup is the right formula for this because of multiple arguments. Maybe hlookup or vlookup but these require you to have tables for values. A simple nested series of if does the trick for a small sample size Try =IF(A1=”a”,”pickup”,IF(A1=”b”,”collect”,IF(A1=”c”,”prepaid”,””))) Now incorporate your left argument =IF(LEFT(A1,1)=”a”,”pickup”,IF(LEFT(A1,1)=”b”,”collect”,IF(LEFT(A1,1)=”c”,”prepaid”,””))) Also note your usage of left, … Read more

How to add data validation to a cell using VBA

by

Use this one: Dim ws As Worksheet Dim range1 As Range, rng As Range ‘change Sheet1 to suit Set ws = ThisWorkbook.Worksheets(“Sheet1”) Set range1 = ws.Range(“A1:A5”) Set rng = ws.Range(“B1″) With rng.Validation .Delete ‘delete previous validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:=”='” & ws.Name & “‘!” & range1.Address End With Note that when you’re using Dim … Read more

how to write to a new cell in python using openpyxl

by

Try this: import openpyxl wb = load_workbook(filename=”xxxx.xlsx”) ws = wb.worksheets[0] ws[‘A1’] = 1 ws.cell(row=2, column=2).value = 2 This will set Cells A1 and B2 to 1 and 2 respectively (two different ways of setting cell values in a worksheet). The second method (specifying row and column) is most useful for your situation: import openpyxl wb … Read more

python-win32com excel com model started generating errors

by

I had the same issue and I resolved it by following the instructions here: https://mail.python.org/pipermail/python-win32/2007-August/006147.html Deleting the gen_py output directory and re-running makepy SUCCEEDS and subsequently the test application runs OK again. So the symptom is resolved, but any clues as to how this could have happened. This is a VERY long running application (think … Read more

Imitating the “IN” Operator

by

I don’t think there is a very elegant solution. However, you could try: If Not IsError(Application.Match(x, Array(“Me”, “You”, “Dog”, “Boo”), False)) Then or you could write your own function: Function ISIN(x, StringSetElementsAsArray) ISIN = InStr(1, Join(StringSetElementsAsArray, Chr(0)), _ x, vbTextCompare) > 0 End Function Sub testIt() Dim x As String x = “Dog” MsgBox ISIN(x, … Read more

tech