0

UPDATE

see original question below.

Code is now:

Function Getvalue(CellRange As String) As Variant

Dim path As String
Dim file As String
Dim sheet As String
Dim str As String 
set rng = Range(Cell Range)

    yr = Format(CStr(Now), "yy")
monthn = Format(CStr(Now), "mmmm")

path = "C:\Folder\Subfolder\"
file = "File-" & monthn & ", FY''" & yr & ".xls"
sheet = "Sheet1"
rng = Range(CellRange)

str = "'" & path & "[" & file & "]" & sheet & "'!" & rng.Address( , ,xlR1C1)

GetValue = Application.ExecuteExcel4Macro(str)
End Function


Sub Routine()
Call GetValue("A1")
End Sub

When I use the subroutine in VBA and go through the function with F8, with "GetValue" being watched, The value of GetValue returns what I want. It pulls the data from the closed workbook. However when I put =GetValue("A1") into a cell on my worksheet, it returns #VALUE!

old:

I have a worksheet that references data from one workbook in multiple columns. My issue is that the workbook is updated and renamed every month (and every year). I could go through and change the link every month but I feel like there should be an automated way to do it.

Let's say the filename is (File-August, FY''20) so every month/year I would be changing the month/year in the filename.

I don't want to use the indirect function as I would like to be able to keep the reference workbook closed.

But basically I want an alternative to:

=INDIRECT("'C:\Folder\Subfolder\[File-" & TEXT(TODAY(),"mmmm") & ", FY''" & TEXT(TODAY(),"yy") & ".xls]Sheet1'!" & A1)

(Full path is 'C:\Folder\Subfolder[File-August, FY''20.xls]Sheet1'!A1)

Month, year, and cell are dynamic.

I am a beginner with VBA, so I've pieced together some code from research, but I know there are probably many things wrong with it. I tried to make it so that I could use a UDF and just call the cell, but I don't know if that is even possible.

Function Getvalue(CellRange As String) As Variant

    Dim path As String
    Dim file As String
    Dim sheet As String
    Dim str As String 
    Dim rng As Range
    
        yr = Format(CStr(Now), "yy")
    monthn = Format(CStr(Now), "mmmm")
    
    path = "C:\Folder\Subfolder\"
    file = "File-" & monthn & ", FY''" & yr & ".xls"
    sheet = "Sheet1"
    rng = Range(CellRange)
    
    str = "'" & path & "[" & file & "]" & sheet & "'!" & rng
    
  Result = ExecuteExcel4Macro(str)
    
End Function

This returns #VALUE when entered.

thank you!

Lisa Smith
  • 1
  • 2
  • 1
    How exactly are you calling this? What is the argument you are passing? – BigBen Aug 06 '20 at 13:22
  • Great question, I guess the answer is I don't think I am? maybe that's what I'm missing. How exactly would I do that? – Lisa Smith Aug 06 '20 at 14:32
  • What are you typing in a cell? Something like `=Getvalue("A1")`? – BigBen Aug 06 '20 at 14:33
  • yes, also tried GetValue(A1) without quotation marks and both returned #VALUE – Lisa Smith Aug 06 '20 at 14:35
  • `& rng` might need to be `& rng.Address`. – BigBen Aug 06 '20 at 14:36
  • still getting #VALUE! – Lisa Smith Aug 06 '20 at 14:39
  • Try debugging from the VB Editor by creating a subroutine that calls this function and stepping through with F8. – BigBen Aug 06 '20 at 14:40
  • but I only changed the third to last line(str="...) . should I replace it in all other instances? – Lisa Smith Aug 06 '20 at 14:40
  • wrote the subroutine as sub Routine() ; Call GetValue("A1") ; end sub ; Just want to make sure thats right. then I went though it with F8: When it gets to line "rng = Range(CellRange)" I get Run-time error '91': Object variable or With clock variable not set" – Lisa Smith Aug 06 '20 at 14:49
  • 1
    range variable need to be written with `Set rng = Range(CellRange)` unless (you can look at local variable pannel) but its most likely store as variant. Add `Option Explicit` on top of your code to spot any other mistake like this – Patates Pilées Aug 06 '20 at 14:54
  • 1
    @PatatesPilées - good catch on the missing `Set`, but note that OP does have `Dim rng as Range` already. Nice to see you contributing! – BigBen Aug 06 '20 at 14:57
  • I tried deleting the `Dim rng as Range` line and adding "set" to the `rng=Range(CellRange)` line. It lets me go through the whole code with F8 now, but then I get "Run-time error '1004': There's an error in the formula you entered. make sure[...]" – Lisa Smith Aug 06 '20 at 15:08
  • I think the issue now is with the second to last line `Result=ExecuteExcel4Macro(str)` – Lisa Smith Aug 06 '20 at 15:12
  • I think Im getting closer. I added "xlR1C1" to the Address and "Application" to the last line so that it looks like this. Based on some googling about using the ExecuteExcelMacro function......... `str = "'" & path & "[" & file & "]" & sheet & "'!" & rng.Address(, , xlR1C1) result = Application.ExecuteExcel4Macro(str)` Now when I go though with F8 and a watch on "result", the value is able to pull the cell from the other workbook and displays what I want. BUT when I use the GetValue function in my sheet, I'm still getting #VALUE! – Lisa Smith Aug 06 '20 at 15:29
  • `Getvalue = ExecuteExcel4Macro(str)` Assigning a value to `Result` doesn't return a value from your functon. – Tim Williams Aug 06 '20 at 17:28
  • I actually did correct that, but it still returns #Value. Everything seems to run correctly but the function isnt working in the sheet – Lisa Smith Aug 06 '20 at 17:47
  • I added the updated code to the original question for clarification – Lisa Smith Aug 06 '20 at 18:00
  • Seems like `ExecuteExcel4Macro` is not permitted in a UDF See https://stackoverflow.com/questions/29521245/a-function-within-a-function-in-vba for discussion – Tim Williams Aug 07 '20 at 21:30

0 Answers0