Forum Discussion

Sue's avatar
Sue
Copper Contributor
Apr 25, 2018

Syntax for referencing cells in another workbook

Syntax for referencing cells in another workbook

I have a PathName : C:\Users\MyFiles 2

I have a FileName: ABC16-17.xls

I have a sheet name: Fun&Games

I have a cell name: SoF

I put these together like this: "'" & PathName & "\[" & FileName & ]Fun" & "&" & "Games'!SoF"

A messagebox shows this gives me ' C:\Users\MyFiles 2\[ABC16-17.xls]Fun&Games'!SoF

But (woe is me!) Test2=Range(Test) creates an error.

Test2=Range("'Fun&Games"!SoF") produces the word "Code" which is correct but that is reading the current book not last years which is what I am trying to do.

I am doing something wrong when trying to use the full address. I seem to have the full address but when I want to use "Range" (and will need offsets as well) clearly something is wrong with my use of the full address. Can you help?

 

Macros & VBA
Need Help

3 Replies

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Apr 25, 2018

    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

     

     

     

    • Sue's avatar
      Sue
      Copper Contributor
      Apr 25, 2018

      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.

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Apr 26, 2018

         

        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.

         

         

         

         

Resources