Let’s say you have a bunch of Excel workbooks, and you need to combine them, or maybe just certain rows within them. Perhaps then you need to re-sort this data, and maybe look for duplicate rows and do something with them.
Here’s some VBA code that will help. I wrote something quite similar to this for work recently, to help massage some data produced by a product we’re using. I’m not posting the exact code because I probably don’t own it. But here’s the general idea:
' create an invisible excel object set objExcel = CreateObject("Excel.Application") objExcel.Visible = false objExcel.Interactive = false ' open a workbook and get a specific worksheet set workbook = objExcel.Workbooks.Open(input_file) set worksheet = objExcel.ActiveWorkbook.Worksheets("My Sheet") ' see how many rows are in use, select them, count them set worksheet_range = worksheet.UsedRange set worksheet_rows = worksheet_range.Rows worksheet_row_count = worksheet_rows.Count ' copy a range worksheet_range.copy ' paste a range worksheet.range("A1").select worksheet.pasteSpecial ' sort a worksheet by a particular column worksheet.range.sort("A:A") ' look for duplicated rows based on a cell value you know to be unique for i = 1 to worksheet_row_count if worksheet.range("A" & i).value = worksheet.range("A" & i + 1).value then ' found a duplicate end if next ' save as, with prompt if needed objExcel.DisplayAlerts = true workbook.SaveAs(output_file)
Re-arrange those blocks until you have something that does exactly what you want.
I use the clipboard for moving ranges around, which is fine when they’re relatively small, but it’s not the quickest or most efficient way of doing things; you might want to set the cell values directly instead of cutting and pasting. I used the clipboard because it was handy in this specific instance to have the values selected still on the clipboard once the script had finished running.