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?

Window Object - Subscript out of Range
 

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" wrote in message
...

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?

Window Object - Subscript out of Range
 

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" wrote in message
...

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?

Window Object - Subscript out of Range
 

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" wrote in message
...
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" wrote in message
...

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?

Window Object - Subscript out of Range
 

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" wrote in message
...
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" wrote in message
...

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?

Window Object - Subscript out of Range
 

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" wrote in message
...
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" wrote in message
...

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?