Excel VLOOKUP function

PowerShell Help
1

Hi Folks,

I’m writing quite a lengthy script to combine the data from three spreadsheets. It’s going fairly well so far, but I’m stuck on a little bit that I’ll probably end up kicking myself for…

Column E Header: User ID
Column F Header: DisplayName

I’ve entered the following formula for cell F2 (which is a vlookup of the contents of cell E2 against the ‘AD users’ spreadsheet)

$sheet.cells.item(2,6).Formula = ('=VLOOKUP(E2,[ADUsers.csv]ADUsers!$A$2:$B$3291,2,false')

It works fine.

However, I don’t want to use a fixed value in my script for the search area in the second spreadsheet (i.e. $A$2:$B$3291) as the number of rows (which represent AD accounts) change on a daily basis. I suppose I could substitute 10000 or 20000 for the $B value, but I was wondering if there was a way of specifying just the populated cells in that column?

Also - I need to copy that formula down so that it goes all the way down column F (I would ordinarily double-click the tiny black square in the bottom right corner of the cell to copy down the formula using the GUI). I’ve tried messing with the ‘EntireColumn’ property, but that interferes with the column heading.

Cheers.

ThickGit

2

hi,

Interesting solution. I don’t know your overall goal of the solution, however if I may make a suggestion:

If you “convert” your excel files to csv, you could import them in powershell and handle all the logic there and then export the results in powershell to csv and open that in excel? Downside is you only get one sheet for each file if you save it as csv, however you can still use formulas in excel if you need them.

Cheers

3

It’s been a while since I did any Excel automation, but I think there’s a property that gives you the range of non-blank cells (Range.CurrentRegion, if I remember right). You could use that to figure out the row number to put in your formula.

4

Many thanks to you both.

Tore - I didn’t think about doing that. However I’ll bear that idea in mind for future tasks.

Dave - yes - Range.CurrentRegion did exactly what I needed.

I ended up using this:

$rows = $sheet.range("A1").currentregion.rows.count
$sheet.range("F2:F$rows").formula = $sheet.range("F2").formula

Thanks again!