Get cell value from other workbook
 

I am using: Question is below the code:

Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

myPath = "C:\ThisDirectory\" '<- include that backslash!
myFileName = "File001.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen
If Wkbk.HasPassword = False Then
Wkbk.Close savechanges:=False
MsgBox "Error occured in opening the file."
End If
Else
MsgBox "The file is already open!"
End If

My question is: Is there a way to get the value of cell "L1" form the Wkbk.
And also is there a way to change the value of cell "L1" in the Wkbk. And
do this within the flow of the macro. I would really like to do it using the
Workbook "Wkbk" method. If not possible, what is the method to do this?

Thank you,

Steven

Get cell value from other workbook
 

Suppose L1 is on a worksheet named Sheet1:

Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

myPath = "C:\ThisDirectory\" '<- include that backslash!
myFileName = "File001.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen

'do you want to retrieve the value after the auto_open procedure runs?
'do you want to put that value in a cell in the workbook that holds the
'code?
thisworkbook.worksheets("sheet9999").range("a1").v alue _
= wkbk.worksheets("sheet1").range("L1").value

wkbk.worksheets("sheet1").range("l1").value = "someothervaluehere"

'but after changing the value, I would think you'd want to
'save the changed workbook.
If Wkbk.HasPassword = False Then
Wkbk.Close savechanges:=False
MsgBox "Error occured in opening the file."
End If
Else
MsgBox "The file is already open!"
End If

Steven wrote:

I am using: Question is below the code:

Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

myPath = "C:\ThisDirectory\" '<- include that backslash!
myFileName = "File001.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen
If Wkbk.HasPassword = False Then
Wkbk.Close savechanges:=False
MsgBox "Error occured in opening the file."
End If
Else
MsgBox "The file is already open!"
End If

My question is: Is there a way to get the value of cell "L1" form the Wkbk.
And also is there a way to change the value of cell "L1" in the Wkbk. And
do this within the flow of the macro. I would really like to do it using the
Workbook "Wkbk" method. If not possible, what is the method to do this?

Thank you,

Steven

--

Dave Peterson

Get cell value from other workbook
 

Dave,

Thank you for answering both my question. You gave me the Wkbk earlier and
now this. This is such incredibly valuable knowledge and you answer it with
such ease. There should be a user group conference where all the users chip
in and we award something very nice to you and Tom Ogilvy. There may be some
other gurus, but I cant think of them right now.

Thank you so much for your help.

Steven

"Dave Peterson" wrote:

Suppose L1 is on a worksheet named Sheet1:

Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

myPath = "C:\ThisDirectory\" '<- include that backslash!
myFileName = "File001.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen

'do you want to retrieve the value after the auto_open procedure runs?
'do you want to put that value in a cell in the workbook that holds the
'code?
thisworkbook.worksheets("sheet9999").range("a1").v alue _
= wkbk.worksheets("sheet1").range("L1").value

wkbk.worksheets("sheet1").range("l1").value = "someothervaluehere"

'but after changing the value, I would think you'd want to
'save the changed workbook.
If Wkbk.HasPassword = False Then
Wkbk.Close savechanges:=False
MsgBox "Error occured in opening the file."
End If
Else
MsgBox "The file is already open!"
End If

Steven wrote:

I am using: Question is below the code:

Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

myPath = "C:\ThisDirectory\" '<- include that backslash!
myFileName = "File001.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen
If Wkbk.HasPassword = False Then
Wkbk.Close savechanges:=False
MsgBox "Error occured in opening the file."
End If
Else
MsgBox "The file is already open!"
End If

My question is: Is there a way to get the value of cell "L1" form the Wkbk.
And also is there a way to change the value of cell "L1" in the Wkbk. And
do this within the flow of the macro. I would really like to do it using the
Workbook "Wkbk" method. If not possible, what is the method to do this?

Thank you,

Steven

--

Dave Peterson

Get cell value from other workbook
 

You hang around the newsgroups for a little bit (just lurking if you want) and
you'll see that these newsgroups are filled with helpful people.

But thanks for the kind words.

Steven wrote:

Dave,

Thank you for answering both my question. You gave me the Wkbk earlier and
now this. This is such incredibly valuable knowledge and you answer it with
such ease. There should be a user group conference where all the users chip
in and we award something very nice to you and Tom Ogilvy. There may be some
other gurus, but I cant think of them right now.

Thank you so much for your help.

Steven

"Dave Peterson" wrote:

Suppose L1 is on a worksheet named Sheet1:

Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

myPath = "C:\ThisDirectory\" '<- include that backslash!
myFileName = "File001.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen

'do you want to retrieve the value after the auto_open procedure runs?
'do you want to put that value in a cell in the workbook that holds the
'code?
thisworkbook.worksheets("sheet9999").range("a1").v alue _
= wkbk.worksheets("sheet1").range("L1").value

wkbk.worksheets("sheet1").range("l1").value = "someothervaluehere"

'but after changing the value, I would think you'd want to
'save the changed workbook.
If Wkbk.HasPassword = False Then
Wkbk.Close savechanges:=False
MsgBox "Error occured in opening the file."
End If
Else
MsgBox "The file is already open!"
End If

Steven wrote:

I am using: Question is below the code:

Dim Wkbk As Workbook
Dim myFileName As String
Dim myPath As String

myPath = "C:\ThisDirectory\" '<- include that backslash!
myFileName = "File001.xls"

Set Wkbk = Nothing
On Error Resume Next
Set Wkbk = Workbooks(myFileName)
On Error GoTo 0

If Wkbk Is Nothing Then
'it's not open
Set Wkbk = Workbooks.Open(Filename:=myPath & myFileName, _
UpdateLinks:=False, ReadOnly:=False, Password:="aoaoao")
Wkbk.RunAutoMacros which:=xlAutoOpen
If Wkbk.HasPassword = False Then
Wkbk.Close savechanges:=False
MsgBox "Error occured in opening the file."
End If
Else
MsgBox "The file is already open!"
End If

My question is: Is there a way to get the value of cell "L1" form the Wkbk.
And also is there a way to change the value of cell "L1" in the Wkbk. And
do this within the flow of the macro. I would really like to do it using the
Workbook "Wkbk" method. If not possible, what is the method to do this?

Thank you,

Steven

--

Dave Peterson


--

Dave Peterson