GetValue=ExecuteExcel4Macro

[adamjones112182]

Hi all,

I was wondering if I could get some help with this

I keep on getting Run-Time Error'1004':



Private Function GetValue(Path, File, Sheet, Ref)
' Retrieve 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)
' Debug.Print arg
' Excute an XLM macro
GetValue = ExecuteExcel4Macro(arg) (Error occurs here)

End Function


I have run a debug, but I am still baffled

 

[DanteAmor]

Missing Apostrophe
arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(Ref).Range("A1").Address(, , xlR1C1)

 

[adamjones112182]

dang, you are right

thank you so much!

 

[adamjones112182]

So I put the apostrophe in and it still has an error

 

[adamjones112182]

When I debug it I get this

C:\Users\Kami\Desktop\VBA BOOK\Excel 2016 VBA Book\Chapter 7\[Goku2.xlsm]sheet1'!R1C1

 

[DanteAmor]

You should have:
'C:\Users\Kami\Desktop\VBA BOOK\Excel 2016 VBA Book\Chapter 7\[Goku2.xlsm]sheet1'!R1C1

Check the code again.
Check that the path exists, the name of the file, the name of the sheet

 

[adamjones112182]

Can do

thank you for the help

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[adamjones112182]

Here is everything

Paths exist, and the names are correct. I tried another document just in case.


Private Function GetValue(Path, File, Sheet, Ref)
' Retrieve 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)

' Excute an XLM macro
GetValue = ExecuteExcel4Macro(arg)

End Function



Sub TestGetValue2()
Dim p As String, f As String
Dim s As String, a As String
Dim r As Long, c As Long

p = "C:\Users\Kami\Desktop\Excel Courses\Excel Reporting and Dashboards\dashboard (1)\files\Section 4"
f = "Table Sample with Dates.xlsx"
s = "SourceData"
a = "A1"
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 12
a = Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r

MsgBox GetValue(p, f, s, a)
End Sub


When I run debug.print arg

I get this

'C:\Users\Kami\Desktop\Excel Courses\Excel Reporting and Dashboards\dashboard (1)\files\Section 4\[Table Sample with Dates.xlsx]SourceData'!R1C1

I have tried adding the apostrophe to the path code, but now I get an error related to this section of the function

If Dir(Path & File) = "" Then

 

[DanteAmor]

but now I get an error related to this section of the function

What the message says?

p = "C:\Users\Kami\Desktop\Excel Courses\Excel Reporting and Dashboards\dashboard (1)\files\Section 4"
f = "Table Sample with Dates.xlsx"

Check that you have a folder with that name and a file with that name.

a = Cells(r, c) = GetValue(p, f, s, a)

I don't think that works for you.
Suppose you want the result in a cell.
I guess you want to get several cells. Then it should be something like this:
Cells(r, c).value = GetValue(p, f, s, Cells(r, c).Address(0,0))