Run Time 1004

[Rpettis31]

I have the following code that states that the macro/sub cannot be found yet,
and I am not sure why, the workbook opens and I simply copied the path in my
code and then added the macro call. Not sure why I am getting this error.

Workbooks.Open Filename:= _
"G:\Purchasing\Robert's Stuff\Inventory Studies\Inventory Study9.xls"

Workbooks("Inventory Study9.xls").Activate

Application.Run ("'G:\Purchasing\Robert's Stuff\Inventory
Studies\Inventory Study9.xls'!Inventory")

 

[r]

Application.Run ("Inventory Study9.xls!Inventory")

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Barb Reinhardt]

I've tweaked it a bit. Let me know if it works

Option Explicit

Sub Test()
Dim myWB As Excel.Workbook
Dim myFilePath As String

myFilePath = "G:\Purchasing\Robert's Stuff\Inventory Studies\Inventory
Study9.xls"

On Error Resume Next
Set myWB = Workbooks.Open(Filename:=myFilePath)
On Error GoTo 0

If myWB Is Nothing Then
MsgBox ("Workbook not opened")
End
End If

Application.Run (myWB.Name & "!Inventory")

End Sub

 

[Tim Zych]

On the right track...just take out the directory name, but keep the single
quotes.

Application.Run "'Inventory Study9.xls'!Inventory"

 

[Tim Zych]

That won't work. You need single quotes around the workbook name due to the
fact that it has a space in it.

This modification will work with any workbook name, no matter whether it has
spaces in it or apostrophes or not. Both are considerations when using Run.

Application.Run ("'" & Replace(myWB.Name,"'","''") & "'!Inventory")

 

[Barb Reinhardt]

Thanks for the catch. I know all too well that anything with a blank needs
single quotes around it.