How to use the if statement for Macro coding?

[Eric]

IF( 'E:\file\[1.xls]Sheet1'!$A$1 = 1 THEN
{ / Processing following code
Workbooks.Open Filename:="E:\file\1.xls", UpdateLinks:=3
Workbooks("1.xls").Close savechanges:=True
} else
/ Processing nothing

Does anyone have any suggestions on how to code the if statement for Excel
Macro?
Thank in advance for any suggestions
Eric

 

[Mike H]

Eric,

Try this combination of Function and Sub:- Change you paths and workbook
names to suit:-

Sub marine()
x = TheValue("c:\", "Book2.xls", "Sheet1", "A1")
If x = 1 Then
Workbooks.Open Filename:="c:\book2.xls", UpdateLinks:=3
Workbooks("book2.xls").Close savechanges:=True
Else
MsgBox ("The value was " & x)
End If
End Sub

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets.Add
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet &
"'!" & Addr
TheValue = Range("A1").Value
ActiveSheet.Delete
Application.ScreenUpdating = True
End Function

Mike

 

[Mike H]

Simpler:-

Sub human()
Application.DisplayAlerts = False
Path = "c:\"
WorkbookName = "Book2.xls"
Sheet = "Sheet1"
Addr = "A1"
Worksheets.Add
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!"
& Addr
TheValue = Range("A1").Value
ActiveSheet.Delete
If TheValue = 1 Then
Workbooks.Open Filename:="c:\book2.xls", UpdateLinks:=3
Workbooks("book2.xls").Close savechanges:=True
Else
MsgBox ("The value was " & TheValue)
End If
End Sub


Mike

 

[Eric]

Thank you for your suggestions

For the function, can I use Addr instead of "A1" since this cell location
could be changed based on different files? I try to replace "A1" with Addr,
but this does not work. Could you please give me any suggestions?

Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet &
"'!" & Addr
TheValue = Range("A1").Value

Thank you for your suggestions
Eric

Eric,

Try this combination of Function and Sub:- Change you paths and workbook
names to suit:-

Sub marine()
x = TheValue("c:\", "Book2.xls", "Sheet1", "A1")
If x = 1 Then
Workbooks.Open Filename:="c:\book2.xls", UpdateLinks:=3
Workbooks("book2.xls").Close savechanges:=True
Else
MsgBox ("The value was " & x)
End If
End Sub

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets.Add
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet &
"'!" & Addr
TheValue = Range("A1").Value
ActiveSheet.Delete
Application.ScreenUpdating = True
End Function

Mike

IF( 'E:\file\[1.xls]Sheet1'!$A$1 = 1 THEN
{ / Processing following code
Workbooks.Open Filename:="E:\file\1.xls", UpdateLinks:=3
Workbooks("1.xls").Close savechanges:=True
} else
/ Processing nothing

Does anyone have any suggestions on how to code the if statement for Excel
Macro?
Thank in advance for any suggestions
Eric