Call a function from another workbook

[superchao]

the purpose is to call a function (name is repeat) from another workbook wth path（'D:\EXcel VBA\practice\trial.xls'）. I find the following is successful.


Private Sub CommandButton1_Click()

Workbooks.Open("D:\EXcel VBA\practice\trial.xls")
Application.Run "'D:\EXcel VBA\practice\trial.xls'!repeat"

End Sub

However, When I try to use a string "file" to replace the long name 'D:\EXcel VBA\practice\trial.xls' It fails as shown below. error is with the last statement Application.Run 'file!repeat'
Help needed. thanks!!!

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim file, As String

file = "D:\EXcel VBA\practice\trial.xls"
Workbooks.Open file
Application.Run 'file!repeat'

End Sub

 

[Norie]

Well that's probably because you've not actually used the variable file you've used the word file.

Application.Run file & "!repeat"

 

[Jonmo1]

Try

Application.Run file & "!repeat"

 

[Zack Barresse]

First do a check if the file is open...

Function WbOpen(wbName As String) As Boolean
'Originally found by Jake Marx
    On Error Resume Next
    WbOpen = Len(Workbooks(wbName).Name)
End Function

Use like this ...

dim wb as workbook, blnOpened as boolean
if wbopen("trial.xls") = true then set wb = workbooks("trial.xls")
if wb is nothing then
    set wb = workbooks.open(file)
    blnopened = true
end if

'.. other code, i.e.
application.run file & "!ProcName", "Arg1"

if blnopened = true then wb.close

HTH

 

[superchao]

I Tried Application.Run file & "!repeat"
The error msg is as following
The macro 'D:\EXcel VBA\practice\trial.xls!repeat' cannot be found

"repeat" is a function stored in modules in "trial" And I tried again"repeat" can be called in the following method. Application.Run "'D:\EXcel VBA\practice\trial.xls'!repeat”

Any idea??? help!!!!

 

[Zack Barresse]

Try adding the module name...

...file & "!trial.repeat"

Use an exclamation after the filename, use a period to separate the module and routine name.