Workbook Path

[Todd Huttenstine]

I need a code that will say:

If Workbook located in P:\QTD\Stats Manager.xls Worksheets
(4).Range("A1")=1 then msgbox "Upgrade" else msgbox "No
Upgrade"


How would I do this?

Thanx

Todd Huttenstine

 

[Ron de Bruin]

Hi Todd

You can open the file with VBA and check the value.
With screenupdating to false it is fast and you don't see the file open and close.

Or use John's example to check a value in a closed file
http://j-walk.com/ss/excel/tips/tip82.htm

 

[Rob van Gelder]

Sub testit()
Dim wkb As Workbook

Set wkb = Workbooks.Open(Filename:="P:\QTD\Stats Manager.xls",
ReadOnly:=True)
If wkb.Worksheets(4).Range("A1") = 1 Then MsgBox "Upgrade" Else MsgBox
"No Upgrade"
wkb.Close SaveChange:=False
End Sub

 

[Todd Htutenstine]

The workbook cannot be opened to check it.

-----Original Message-----
Sub testit()
Dim wkb As Workbook

Set wkb = Workbooks.Open(Filename:="P:\QTD\Stats Manager.xls",
ReadOnly:=True)
If wkb.Worksheets(4).Range("A1") = 1 Then MsgBox "Upgrade" Else MsgBox
"No Upgrade"
wkb.Close SaveChange:=False
End Sub





.

 

[J.E. McGimpsey]

Take a look at John Walkenbach's

A VBA Function to Get a Value From a Closed File
http://j-walk.com/ss/excel/tips/tip82.htm

it should be able to be adapted for your needs.

 

[Harald Staff]

Hi Todd

Another approach might be to use a free cell, enter a formula in it pointing to the
file/sheet/cell, calculate, remove the formula. Something like:

Sub GetTheThing()
On Error GoTo None
Sheets(1).Cells(1, 1).FormulaR1C1 = _
"='P:\QTD\[Stats Manager.xls]Sheet4'!R1C1"
Sheets(1).Cells(1, 1).Calculate
Sheets(1).Cells(1, 1).Value = _
Sheets(1).Cells(1, 1).Value
If Sheets(1).Cells(1, 1).Value = 1 Then
MsgBox "Upgrade"
Exit Sub
End If
None:
MsgBox "Never upgrade"
End Sub