Prev: Range Defined names loop
Next: 1004 error when using ATPVBAEN.XLAM!Regress
From: David on 23 Sep 2009 05:44
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.
From: Mike H on 23 Sep 2009 06:53
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.
>
From: Don Guillett on 23 Sep 2009 09:25
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
dguillett1(a)austin.rr.com
"David" <skulkrinbait(a)googlemail.com> wrote in message
news:d2bc8ca7-22de-4127-84f7-9e6db8f1b409(a)p9g2000vbl.googlegroups.com...
>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.

From: Don Guillett on 23 Sep 2009 09:42
Or, you may like to make a defined name in your destination workbook
referring to the source.
insert>name>define>name it sourcebook>in 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
dguillett1(a)austin.rr.com
"Don Guillett" <dguillett1(a)austin.rr.com> wrote in message
news:%23j7RlFFPKHA.1796(a)TK2MSFTNGP02.phx.gbl...
> 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
> dguillett1(a)austin.rr.com
> "David" <skulkrinbait(a)googlemail.com> wrote in message
> news:d2bc8ca7-22de-4127-84f7-9e6db8f1b409(a)p9g2000vbl.googlegroups.com...
>>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.
>

 | 
Pages: 1
Prev: Range Defined names loop
Next: 1004 error when using ATPVBAEN.XLAM!Regress