Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linked workbook cell returns 0 is source workbook cell is blank? | Excel Worksheet Functions | |||
Refresh pivot table in workbook A when changing a cell in workbook | Excel Worksheet Functions | |||
Find cell in Excel2000 workbook with link from another workbook? | Excel Discussion (Misc queries) | |||
automatically move cell data in 1 workbook to another workbook | Excel Worksheet Functions |