Solved: Insert wb2 filename into wb1 cell

[cdbrown]

I'm trying to get my macro to put the filename of wb2 (well a specific portion of it) into Sheet(1) Cells(4,2) of wb1 but I'm not sure of how to call it
wb2.Name I know provides just the name of the workbook and not the full path because I can get the worksheet name to change using it. The wb2 filename is "Stuff - I04X-G.xls" and I just want the I04X-G

Cells(4,2).Select
ActiveCell.FormulaR1C1 = "B4-" & Right(Left$(wb2.Name, Len(wb2.Name) - 4), Len(wb2.Name) - 8)

Comes back with an automation error.

Cheers
-cdbrown

[mvidas]

Hi cdbrown,

Rather than use a combination of right/left there, the Mid function should work a little nicer. Also, the automation error could be because you're entering a formula into a cell without beginning the formula with the equals sign though it doesn't look like an actual formula. I take it your wb2 will always begin with "stuff - " and you want what comes after that and before .xls? What about using:WB1.ActiveSheet.Cells(4, 2).Value = "B4-" & Mid(WB2.Name, 9, Len(WB2.Name) - 12)Matt

[cdbrown]

Thank you very much mvidas, can see this coming in handy on some other areas of the code.

Cheers
-cdbrown