Forum Discussion
Syntax for referencing cells in another workbook
Sue-
You are getting results for the current workbook because you have not fully qualified your reference:
Test2=Range("'Fun&Games"!SoF")
If you don't specify the workbook you will get the range in the current workbook....
In addition you typically need to open the workbook to read the values from it... you can't read values from a closed workbook.
Option Explicit
Sub Test()
Dim PathName As String
Dim FileName As String
Dim wkbk As Workbook
Dim sht As Worksheet
Dim strTest As String
PathName = "C:\Users\mmickle1\Documents\"
FileName = "Test1.xlsx"
Set wkbk = Workbooks.Open(PathName & FileName) 'Open WkBk
Set sht = wkbk.Worksheets("Fun&Games") 'Define Worksheet in case Named Range Scope is just on the worksheet
strTest = sht.Range("SoF") 'Pass the cell value to Test variable
MsgBox strTest 'Print in msgbox
wkbk.Close False 'Close file don't save changes
End Sub
I very much appreciate that you have replied.
However you have done what people always do.
You have twisted part of what I said to make it look like I am more stupid than I am.
I stated the fully qualified reference that I am trying to use and the fact that it does not work. I stated that I had confirmed what it was (ie the syntax that I have) by means of a msgbox.
I also stated that the last part of it did work - it was just that it did not work when fully qualified as to the path and workbook. I stated that I tested the end part of the expression on the current workbook (to make sure that bit at least was correct).
I don't need to be told that when I test part of an expression on the current workbook that I am testing part of an expression on the current workbook. That is what I said I did to try to confirm part of the syntax - successfully. I am totally aware that any reference is to the current workbook unless fully qualified. My problem is full path syntax.
I can also add I have references to other workbooks for the update of information and the referred workbook DOES NOT need to be open. One is asked on opening the current workbook if one wants to update and if one answers "No" one can update later with a command button I have in the code called "Update".
The difference between the above and what I am trying to do now is that I want the material from the other workbook to be displayed in a form and not put directly in a cell (in case I don't like it ie data is not what I want due to some error). That reading into a form may have something to do with the problem. I don't seem to be able to get either an example or meaningful help from the error messages produced.
Since I do not want a link (there is a final point after which values must not be able to be changed and a link could cause accidental changes - eg the old file was moved) reading all the info (about 30 lines via a do loop) into a form seemed like a good idea and selecting those that needed updating by placing values not a links.
It seems strange that you can read from a closed workbook to the current workbook but not to a form in the current workbook but there are stranger things in life!
I will try your suggestion of opening the old workbook and reading it to hopefully the form in the new workbook. I will let you know if I get it to work to do what is needed.
This is the first time I have used the new community area. The old area when one used a user name made me a lot more comfortable with opening remarks like you started with. They did not bother me then but using names is like the local computer associations "Have you turned on the computer at the wall?" Somehow I don't like it recorded against my name. I doubt if I am the only person feeling that way.
I apologize if I came off as short or if my answer was not a valid solution, I can assure you that I was just trying to help. After further reviewing your initial question you are correct. I misread your post regarding the testing of the qualified reference. This is certainly a mistake on my part. I can see that you tested the values properly. It is my understanding that VBA does not contain a native method to read values from another workbook, but it looks like there is a caveat to this, which can be taken advantage of by using Legacy Excel 4.0 (1992) functionality. As you have referenced you can read values from a closed workbook if an absolute path is referenced. This being said, I believe you have at least two feasible options (there may be more).
1. Use a worksheet to hold a temporary calculation
a. Insert a fully qualified path formula in a cell via VBA code.
b. Read the value from the cell
c. Remove the value
Sub Test() 'Pass value to range Range("A1") = "='C:\Users\mmickle1\Documents\[Test1.xlsm]Fun&Games'!SoF" 'This also seems to work in my test ---> Range("A1") = "='[Test1.xlsm]Fun&Games'!SoF" 'Pass value to variable MyVar = Range("A1")
'Clear Range Range("A1") = vbNullString End Sub2. Use John Walkenbach's UDF which takes advantage of ExecuteExcel4Macro (Legacy Excel 4.0 XLM Macro)
Caveat: I tested this and it doesn't seem to allow for Named Ranges. It appears it will only take a value like A1. However, if the workbook is open it will accept a Named Range.
Private Function GetValue(path, file, sheet, ref) ' Retrieves a value from a closed workbook Dim arg As String ' Make sure the file exists If Right(path, 1) <> "\" Then path = path & "\" If Dir(path & file) = "" Then GetValue = "File Not Found" Exit Function End If ' Create the argument arg = "'" & path & "[" & file & "]" & sheet & "'!" & _ Range(ref).Range("A1").Address(, , xlR1C1) ' Execute an XLM macro GetValue = ExecuteExcel4Macro(arg) End FunctionSub TestGetValue() p = "C:\Users\mmickle1\Documents" f = "Test1.xlsm" s = "Fun&Games" a = "A1" MsgBox GetValue(p, f, s, a) End SubRegarding using your real name on the forums. I did not realize this was a new requirement. I typically use a handle (mrmmickle1) as you referenced was your preference. I answer questions on many different forums and this requirement did not stand out to me. I can definitely understand why using your name is not appealing and how posting under a username would be preferable for greater privacy. You certainly make a good point. Thanks for the refreshing point of view.
I hope my answer will help solve your inquiry. My aim is only to help you resolve your issue. Please let me know if these options do not work for your particular situation. I'm happy to look into the issue further if these solutions aren't valid for your workbook.