Window Object - Subscript out of Range

I want to be able to use my macro for lots of different projects, so the
filenames that I use throughout the macro need to be easy to change.

So I've set them up at the beginning of the Macro so they're easy to
identify using the following sequence:

myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project
Services\03 Man Hour Reports\"

mySourceFileBase = ActiveWorkbook.Worksheets("Imported
Data").Range("F5").Value

mySourceFile = myPath & mySourceFileBase

So I can put the file name that I want to import data from in the workbook
containing the macro and hopefully not have to open the VBE to alter the file
references.

and it's used it correctly in this line:

Workbooks.Open Filename:=mySourceFile

but after I've activated a different window, I can't get the syntax right
for activating the other window that mySourceFile refers to. It wants the
file name reference ("RF Man Hours Report 0506.xls").

Is there any way that I can use mySourceFile as a window object?

Helen,
Give yourself a WB variable to work with afterwards. e.g.
Dim SourceWS as Workbook
Set SourceWS=Workbooks.Open Filename:=mySourceFile

Then you can use
SourceWS.Worksheets(1).range...
without caring what any actual names are.

You could also incorporate a call to Application.GetOpenFileName, so you do
not have to have any hard coded file names.

NickHK

"Helen" <[email protected]> wrote in message
news:[email protected]...
>
> I want to be able to use my macro for lots of different projects, so the
> filenames that I use throughout the macro need to be easy to change.
>
> So I've set them up at the beginning of the Macro so they're easy to
> identify using the following sequence:
>
> myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project
> Services\03 Man Hour Reports\"
>
> mySourceFileBase = ActiveWorkbook.Worksheets("Imported
> Data").Range("F5").Value
>
> mySourceFile = myPath & mySourceFileBase
>
> So I can put the file name that I want to import data from in the workbook
> containing the macro and hopefully not have to open the VBE to alter the
file
> references.
>
> and it's used it correctly in this line:
>
> Workbooks.Open Filename:=mySourceFile
>
> but after I've activated a different window, I can't get the syntax right
> for activating the other window that mySourceFile refers to. It wants the
> file name reference ("RF Man Hours Report 0506.xls").
>
> Is there any way that I can use mySourceFile as a window object?

Nick,

I've copied what you've written below letter for letter but it's telling me
that there's a syntax error on the following line:

Set SourceWS=Workbooks.Open Filename:=mySourceFile

"NickHK" wrote:

> Helen,
> Give yourself a WB variable to work with afterwards. e.g.
> Dim SourceWS as Workbook
> Set SourceWS=Workbooks.Open Filename:=mySourceFile
>
> Then you can use
> SourceWS.Worksheets(1).range...
> without caring what any actual names are.
>
> You could also incorporate a call to Application.GetOpenFileName, so you do
> not have to have any hard coded file names.
>
> NickHK
>
> "Helen" <[email protected]> wrote in message
> news:[email protected]...
> >
> > I want to be able to use my macro for lots of different projects, so the
> > filenames that I use throughout the macro need to be easy to change.
> >
> > So I've set them up at the beginning of the Macro so they're easy to
> > identify using the following sequence:
> >
> > myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project
> > Services\03 Man Hour Reports\"
> >
> > mySourceFileBase = ActiveWorkbook.Worksheets("Imported
> > Data").Range("F5").Value
> >
> > mySourceFile = myPath & mySourceFileBase
> >
> > So I can put the file name that I want to import data from in the workbook
> > containing the macro and hopefully not have to open the VBE to alter the
> file
> > references.
> >
> > and it's used it correctly in this line:
> >
> > Workbooks.Open Filename:=mySourceFile
> >
> > but after I've activated a different window, I can't get the syntax right
> > for activating the other window that mySourceFile refers to. It wants the
> > file name reference ("RF Man Hours Report 0506.xls").
> >
> > Is there any way that I can use mySourceFile as a window object?
>
>
>

Helen,
I was too quick with the Copy/Paste. Yes, now that you are using the return
value from Workbooks.Open, the arguments need to in brackets. So:
Set SourceWS=Workbooks.Open (Filename:=mySourceFile)

NickHK

"Helen" <[email protected]> wrote in message
news:[email protected]...
> Nick,
>
> I've copied what you've written below letter for letter but it's telling
me
> that there's a syntax error on the following line:
>
> Set SourceWS=Workbooks.Open Filename:=mySourceFile
>
> "NickHK" wrote:
>
> > Helen,
> > Give yourself a WB variable to work with afterwards. e.g.
> > Dim SourceWS as Workbook
> > Set SourceWS=Workbooks.Open Filename:=mySourceFile
> >
> > Then you can use
> > SourceWS.Worksheets(1).range...
> > without caring what any actual names are.
> >
> > You could also incorporate a call to Application.GetOpenFileName, so you
do
> > not have to have any hard coded file names.
> >
> > NickHK
> >
> > "Helen" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > I want to be able to use my macro for lots of different projects, so
the
> > > filenames that I use throughout the macro need to be easy to change.
> > >
> > > So I've set them up at the beginning of the Macro so they're easy to
> > > identify using the following sequence:
> > >
> > > myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project
> > > Services\03 Man Hour Reports\"
> > >
> > > mySourceFileBase = ActiveWorkbook.Worksheets("Imported
> > > Data").Range("F5").Value
> > >
> > > mySourceFile = myPath & mySourceFileBase
> > >
> > > So I can put the file name that I want to import data from in the
workbook
> > > containing the macro and hopefully not have to open the VBE to alter
the
> > file
> > > references.
> > >
> > > and it's used it correctly in this line:
> > >
> > > Workbooks.Open Filename:=mySourceFile
> > >
> > > but after I've activated a different window, I can't get the syntax
right
> > > for activating the other window that mySourceFile refers to. It wants
the
> > > file name reference ("RF Man Hours Report 0506.xls").
> > >
> > > Is there any way that I can use mySourceFile as a window object?
> >
> >
> >

Thanks Nick

It asks Excel to Open the file when you Set SourceWS. Is there a way that I
can create a variable like this but without using the Open / Close functions?


I would like to be able to apply this to the file containing the macro too,
but as the file is already open when it's being run, using Set
HomeWS=Workbooks.Open(Filename....) will prompt Excel to want to reopen and
give the save / no save message box. Which I would like to avoid.

"NickHK" wrote:

> Helen,
> I was too quick with the Copy/Paste. Yes, now that you are using the return
> value from Workbooks.Open, the arguments need to in brackets. So:
> Set SourceWS=Workbooks.Open (Filename:=mySourceFile)
>
> NickHK
>
> "Helen" <[email protected]> wrote in message
> news:[email protected]...
> > Nick,
> >
> > I've copied what you've written below letter for letter but it's telling
> me
> > that there's a syntax error on the following line:
> >
> > Set SourceWS=Workbooks.Open Filename:=mySourceFile
> >
> > "NickHK" wrote:
> >
> > > Helen,
> > > Give yourself a WB variable to work with afterwards. e.g.
> > > Dim SourceWS as Workbook
> > > Set SourceWS=Workbooks.Open Filename:=mySourceFile
> > >
> > > Then you can use
> > > SourceWS.Worksheets(1).range...
> > > without caring what any actual names are.
> > >
> > > You could also incorporate a call to Application.GetOpenFileName, so you
> do
> > > not have to have any hard coded file names.
> > >
> > > NickHK
> > >
> > > "Helen" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > I want to be able to use my macro for lots of different projects, so
> the
> > > > filenames that I use throughout the macro need to be easy to change.
> > > >
> > > > So I've set them up at the beginning of the Macro so they're easy to
> > > > identify using the following sequence:
> > > >
> > > > myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project
> > > > Services\03 Man Hour Reports\"
> > > >
> > > > mySourceFileBase = ActiveWorkbook.Worksheets("Imported
> > > > Data").Range("F5").Value
> > > >
> > > > mySourceFile = myPath & mySourceFileBase
> > > >
> > > > So I can put the file name that I want to import data from in the
> workbook
> > > > containing the macro and hopefully not have to open the VBE to alter
> the
> > > file
> > > > references.
> > > >
> > > > and it's used it correctly in this line:
> > > >
> > > > Workbooks.Open Filename:=mySourceFile
> > > >
> > > > but after I've activated a different window, I can't get the syntax
> right
> > > > for activating the other window that mySourceFile refers to. It wants
> the
> > > > file name reference ("RF Man Hours Report 0506.xls").
> > > >
> > > > Is there any way that I can use mySourceFile as a window object?
> > >
> > >
> > >
>
>
>

Nick.

Thanks for your help... I've worked out the rest:

Dim HomeWS
HomeWS = "PM&E Man Hour Report 253 draft.xls"

Windows(HomeWS).Activate

Thanks again.


"Helen" wrote:

> Thanks Nick
>
> It asks Excel to Open the file when you Set SourceWS. Is there a way that I
> can create a variable like this but without using the Open / Close functions?
>
>
> I would like to be able to apply this to the file containing the macro too,
> but as the file is already open when it's being run, using Set
> HomeWS=Workbooks.Open(Filename....) will prompt Excel to want to reopen and
> give the save / no save message box. Which I would like to avoid.
>
> "NickHK" wrote:
>
> > Helen,
> > I was too quick with the Copy/Paste. Yes, now that you are using the return
> > value from Workbooks.Open, the arguments need to in brackets. So:
> > Set SourceWS=Workbooks.Open (Filename:=mySourceFile)
> >
> > NickHK
> >
> > "Helen" <[email protected]> wrote in message
> > news:[email protected]...
> > > Nick,
> > >
> > > I've copied what you've written below letter for letter but it's telling
> > me
> > > that there's a syntax error on the following line:
> > >
> > > Set SourceWS=Workbooks.Open Filename:=mySourceFile
> > >
> > > "NickHK" wrote:
> > >
> > > > Helen,
> > > > Give yourself a WB variable to work with afterwards. e.g.
> > > > Dim SourceWS as Workbook
> > > > Set SourceWS=Workbooks.Open Filename:=mySourceFile
> > > >
> > > > Then you can use
> > > > SourceWS.Worksheets(1).range...
> > > > without caring what any actual names are.
> > > >
> > > > You could also incorporate a call to Application.GetOpenFileName, so you
> > do
> > > > not have to have any hard coded file names.
> > > >
> > > > NickHK
> > > >
> > > > "Helen" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >
> > > > > I want to be able to use my macro for lots of different projects, so
> > the
> > > > > filenames that I use throughout the macro need to be easy to change.
> > > > >
> > > > > So I've set them up at the beginning of the Macro so they're easy to
> > > > > identify using the following sequence:
> > > > >
> > > > > myPath = "U:\PM&E\09 Project Work\253 Kazakhstan Project\02 Project
> > > > > Services\03 Man Hour Reports\"
> > > > >
> > > > > mySourceFileBase = ActiveWorkbook.Worksheets("Imported
> > > > > Data").Range("F5").Value
> > > > >
> > > > > mySourceFile = myPath & mySourceFileBase
> > > > >
> > > > > So I can put the file name that I want to import data from in the
> > workbook
> > > > > containing the macro and hopefully not have to open the VBE to alter
> > the
> > > > file
> > > > > references.
> > > > >
> > > > > and it's used it correctly in this line:
> > > > >
> > > > > Workbooks.Open Filename:=mySourceFile
> > > > >
> > > > > but after I've activated a different window, I can't get the syntax
> > right
> > > > > for activating the other window that mySourceFile refers to. It wants
> > the
> > > > > file name reference ("RF Man Hours Report 0506.xls").
> > > > >
> > > > > Is there any way that I can use mySourceFile as a window object?
> > > >
> > > >
> > > >
> >
> >
> >