How do assign a name to a remote workbook ?

[Dan Thompson]

I am trying to have my VBA code open a workbook from a website however I
don't want to have to keep refering to the whole URL name every time I need
to call it or work with it in my code so I just want to assign a short name
variable to represent it and currently my code is giving me a subscript out
of range error here is my code

Dim WPSR As Workbook
This is the code that is not working
Set WPSR =
Workbooks("http://tonto.eia.doe.gov/oog/ftparea/wogirs/xls/psw03.xls")
Workbooks.Open WPSR


This is my original code to open the file
'Workbooks.Open
Filename:="http://tonto.eia.doe.gov/oog/ftparea/wogirs/xls/psw03.xls"
'Sheets("Data 1").Select
'ActiveWindow.SmallScroll Down:=3

 

[Gary''s Student]

Once you have opened the file (using the code that works) the name should be
available:

Dim remoteWB as string
remoteWB=ActiveWorkbook.Name

 

[Dan Thompson]

Ok That will probably work however I am looking for a way to do this without
using the ActiveWorkbook Function. For my purpose I would prefer code to
reference the remote workbook directly by its name because if I use your code
then whenever I have another workbook that is the active workbook the code
will be referencing the wrong workbook wont it ? Or dose VBA remember the
workbook name RemoteWB as the Active workbook that it first referenced at the
time you assigned RemotWB to it ?

 

[Gary''s Student]

O.K.......the trick is that when you Open a workbook, it becomes the
ActiveWorkbook.


Might as well remember its name while you are there.