I need some help with Excel VBA, currently I have the following line
of code:

PeakCPU = Application.VLookup(ServerName, Workbooks(Workbook).Sheets
("Server_Utilisation").Range("$C$25:$L$8100"), 7, False)

This looks for a string in an already open spreadsheet but I'm running
out of memory due to the number of spreadsheets I am working with.
What I want to do is amend this so it will look for the data in a
spreadsheet that is not open but stored on my C drive.

Please advise how I need to amend the formula, I believe this is
possible but can't find any examples.

Thanks.

david,

You can't Vlookup a closed workbook in Vb but it works OK as a worksheet
formula. use a worksheet Vlookup on the closed workbook and capture the cell
value in your VB code.

Mike

"David" wrote:

I need some help with Excel VBA, currently I have the following line
of code:

PeakCPU = Application.VLookup(ServerName, Workbooks(Workbook).Sheets
("Server_Utilisation").Range("$C$25:$L$8100"), 7, False)

This looks for a string in an already open spreadsheet but I'm running
out of memory due to the number of spreadsheets I am working with.
What I want to do is amend this so it will look for the data in a
spreadsheet that is not open but stored on my C drive.

Please advise how I need to amend the formula, I believe this is
possible but can't find any examples.

Thanks.

You can put in a formula from a closed wb and use that. However, be advised
that Excel does not like large external fields such as your 8100. Makes it
very slow to say the least. Perhaps your field is smaller or you can break
it up into blocks.

Sub lookupinclosedwb()
With Range("i1")'use an unused cell
.Formula = "=vlookup(""value"",[wb.xls]sheet!c25:L8100,7,0)"
MsgBox .Value
PeakCPU =.value
.ClearContents
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David" wrote in message
...
I need some help with Excel VBA, currently I have the following line
of code:

PeakCPU = Application.VLookup(ServerName, Workbooks(Workbook).Sheets
("Server_Utilisation").Range("$C$25:$L$8100"), 7, False)

This looks for a string in an already open spreadsheet but I'm running
out of memory due to the number of spreadsheets I am working with.
What I want to do is amend this so it will look for the data in a
spreadsheet that is not open but stored on my C drive.

Please advise how I need to amend the formula, I believe this is
possible but can't find any examples.

Thanks.

Or, you may like to make a defined name in your destination workbook
referring to the source.
insertnamedefinename it sourcebookin the refers to box. Here you DO need
the $
=[wb.xls]sheet!$c$25:$L$8100
then
=VLookup(value,sourcebook,7,0)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
You can put in a formula from a closed wb and use that. However, be
advised that Excel does not like large external fields such as your 8100.
Makes it very slow to say the least. Perhaps your field is smaller or you
can break it up into blocks.

Sub lookupinclosedwb()
With Range("i1")'use an unused cell
.Formula = "=vlookup(""value"",[wb.xls]sheet!c25:L8100,7,0)"
MsgBox .Value
PeakCPU =.value
.ClearContents
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David" wrote in message
...
I need some help with Excel VBA, currently I have the following line
of code:

PeakCPU = Application.VLookup(ServerName, Workbooks(Workbook).Sheets
("Server_Utilisation").Range("$C$25:$L$8100"), 7, False)

This looks for a string in an already open spreadsheet but I'm running
out of memory due to the number of spreadsheets I am working with.
What I want to do is amend this so it will look for the data in a
spreadsheet that is not open but stored on my C drive.

Please advise how I need to amend the formula, I believe this is
possible but can't find any examples.

Thanks.