VBA calling a remote marco?

[Shippwreck]

Hey Guys,

I'm writing a program in Excel and it needs to use a macro written in a different Excel spreadsheet. Now the problem is how to call that other Macro. If i set a reference to the other sheet and call it like this:

Record_Count = CountNumberOfRecords(varFile2)

CountNumberOfRecords being the remote macro.

It works fine and i get the correct result. However i don't want to set a reference to the other sheet as it will not always be in the same place and the people using it wont know how to change the reference. So looking on the net i have found this:

Record_Count = Application.Run(CountNumberOfRecords, varFile2)

(before this line i obviously open and activate the remote file)

It doesn't throw up any errors, however the value assigned to Record_Count, rather than being a number is "Error 2015" which when i look up i think is a syntax error, however as i said i don't get any error messages, the program just runs.

Now i have been searching the forums here (in fact i have never done this before and is how i came up with the two solutions above) however it is solution two that i need to work and i can't seem to get it too, so any help would be much appreciated.

Thanks

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[Shippwreck]

Oops, sorry slight error in the second code box above, it should have read: (which still doesn't work)

Record_Count = Application.Run("Excel SUS Loader v1.0.xls!CountNumberOfRecords", varFile)

Thanks

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[HughLerwill]

Have you tried;

Application.Run("Excel SUS Loader v1.0.xls!CountNumberOfRecords", Record_Count)

MsgBox "Number of recs " & Record_Count

 

[Shippwreck]

Ahh, no, that would be using Record_Count as the variable sent to the macro... i'll explain it a little more in depth.

CountNumberOfRecords is a function that returns the number of records in a file. varFile contains the path to the file and Record_Count is the variable in the "main" macro that needs to end up with the figure.

As i said in the first post, it runs perfectly if i set a reference to the other file and call it as i would any other macro... but i can't use references due to end user limitations!

Thnaks anyway

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[Shippwreck]

Ok, i've got a little way with it and using the syntax specified in my second post it is now giving me the error of:

'the macro "Excel SUS Loader v1.0.xls!CountNumberOfRecords" cannot be found'

The file "Excel SUS Loader v1.0.xls" is definetely open and it still says it cannot be found even if i specifically activate the "Excel SUS Loader v1.0.xls" window???

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[HughLerwill]

I hope you have been following thread707-1332457.