PDA

View Full Version : Correct workbook name



pcsparky
02-07-2009, 02:27 AM
I'm trying to get this snippet of code to work (which updates a cell in the first workbook from the second workbook using the variable thisWbName instead of the actual name of of the workbook) but I don't know the correct way to get Excel to understand the variable thisWbName instead of the word.

Workbooks("~Dog Training Reports & Handler Information").Sheets("Dog and Handler Data").Range(cell).Offset(0, 5).Formula = "='[(thisWbName)]Overview'!$E$28"

Any ideas please.

Andy Pope
02-07-2009, 03:42 AM
Current the variable is being treated as text because it is contained within the quotes. You need to break the syntax so it can recognise the variable.



Workbooks("~Dog Training Reports & Handler Information").Sheets("Dog and Handler Data").Range(cell).Offset(0, 5).Formula = _
"='[" & thisWbName &"]Overview'!$E$28"

p45cal
02-07-2009, 03:46 AM
try
~~.Formula= "='[" & thisWbName & "]Sheet1'!$E$17" if thisWbName is a string.
p45cal

ha.. beaten to it!

pcsparky
02-07-2009, 04:22 AM
Thanks very much, both of you. Works a treat.