VBA + VB Script fails to open the workbook.

[Suresh]

Hi all,

I use the following VB Script to open an XLA and execute a macro.

Dim objxl, xlapath, macro

Set objxl = CreateObject("Excel.Application")
xlapath = "C:\myxla.xla"
macro="myxla.xla!macro1(""param1"",""param2"")"

With objxl
.Visible = True
.Workbooks.Open xlapath
.Workbooks("myxla.xla").RunAutoMacros 1 'xlAutoOpen
.Run macro
End With

Within the xla-macro, I try to open another workbook.

Public Sub macro1(param1 As String, param2 As String)
Dim wb As Workbook
Dim url As String

url = "http://localhost/test.csv"

Debug.Print "Attempting to open : " & url

Set wb = Workbooks.Open(Filename:=url, UpdateLinks:=False,
ReadOnly:=True, Editable:=False, AddToMru:=False, Format:=1)
If wb Is Nothing Then
Debug.Print "Could not open the page: " & url
else
Debug.Print "Successfully opened : " & url
End If
End Sub


This macro works if I invoke it from VBA. But doesnot work (the workbook is
never opened, and wb Is Nothing), if I invoke it using the VB Script.

Could someone please tell me what I am doing wrong ? Is there a better way
to do this ?



Thanks in advance.