Forum Discussion
Syntax for referencing cells in another workbook
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 Sub
2. 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 Function
Sub TestGetValue()
p = "C:\Users\mmickle1\Documents"
f = "Test1.xlsm"
s = "Fun&Games"
a = "A1"
MsgBox GetValue(p, f, s, a)
End Sub
Regarding 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.