Subscript out of range?
 

As a temporary fix to my larger problem so I can at least get the macro
in the system, I have put together this code. My problem is, I am
getting a Runtime Error 9: Subscript Out of Range on the
Windows(UF).Activate portion. Shouldn't I be able to reference a
variable I have defined earlier in a function like this? Also, am I
asking for trouble with the Kill function set up like it is?

Dim UF As Variant
Dim UFOld As Variant

UF = Application.GetOpenFilename(FileFilter:="XML Files
(*.xml)", Title:="This Weeks Projections for Marc")
Workbooks.Open Filename:=UF

Sheets("Marc").Select
Sheets("Marc").Copy After:=Workbooks("Projection
Summary.xls").Sheets("Summary")
Windows(UF).Activate
ActiveWindow.Close

UFOld = Application.GetOpenFilename(FileFilter:="XML Files
(*.xml)", Title:="Last Weeks Projections for Marc")
Workbooks.Open Filename:=UFOld

Sheets("Marc").Select
Sheets("Marc").Name = "Marc Last Week"
Sheets("Marc Last Week").Copy After:=Workbooks("Projection
Summary.xls").Sheets("Marc")
Windows(UFOld).Activate
ActiveWindow.Close
Kill (UFOld)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Subscript out of range?
 

Jason,

The problem is that UF contains the complete pathname of the file
(e.g., "C:\Test\File.xls") and to access the Windows collection,
you need only the file name, without any path. You can split out
the path and get only the file name with code like

Dim UF As String
Dim FName As String
FName = Mid(UF, InStrRev(UF, "\") + 1)
Workbooks(FName).Activate


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jason Hancock" wrote in message
...
As a temporary fix to my larger problem so I can at least get
the macro
in the system, I have put together this code. My problem is, I
am
getting a Runtime Error 9: Subscript Out of Range on the
Windows(UF).Activate portion. Shouldn't I be able to reference
a
variable I have defined earlier in a function like this? Also,
am I
asking for trouble with the Kill function set up like it is?

Dim UF As Variant
Dim UFOld As Variant

UF = Application.GetOpenFilename(FileFilter:="XML Files
(*.xml)", Title:="This Weeks Projections for Marc")
Workbooks.Open Filename:=UF

Sheets("Marc").Select
Sheets("Marc").Copy After:=Workbooks("Projection
Summary.xls").Sheets("Summary")
Windows(UF).Activate
ActiveWindow.Close

UFOld = Application.GetOpenFilename(FileFilter:="XML
Files
(*.xml)", Title:="Last Weeks Projections for Marc")
Workbooks.Open Filename:=UFOld

Sheets("Marc").Select
Sheets("Marc").Name = "Marc Last Week"
Sheets("Marc Last Week").Copy
After:=Workbooks("Projection
Summary.xls").Sheets("Marc")
Windows(UFOld).Activate
ActiveWindow.Close
Kill (UFOld)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Subscript out of range?
 

Workbooks(FName).Activate

might as well be
Windows(FName).Activate


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chip Pearson" wrote in message
...
Jason,

The problem is that UF contains the complete pathname of the
file
(e.g., "C:\Test\File.xls") and to access the Windows
collection,
you need only the file name, without any path. You can split
out
the path and get only the file name with code like

Dim UF As String
Dim FName As String
FName = Mid(UF, InStrRev(UF, "\") + 1)
Workbooks(FName).Activate


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jason Hancock" wrote in message
...
As a temporary fix to my larger problem so I can at least get
the macro
in the system, I have put together this code. My problem is,
I
am
getting a Runtime Error 9: Subscript Out of Range on the
Windows(UF).Activate portion. Shouldn't I be able to
reference
a
variable I have defined earlier in a function like this?
Also,
am I
asking for trouble with the Kill function set up like it is?

Dim UF As Variant
Dim UFOld As Variant

UF = Application.GetOpenFilename(FileFilter:="XML
Files
(*.xml)", Title:="This Weeks Projections for Marc")
Workbooks.Open Filename:=UF

Sheets("Marc").Select
Sheets("Marc").Copy After:=Workbooks("Projection
Summary.xls").Sheets("Summary")
Windows(UF).Activate
ActiveWindow.Close

UFOld = Application.GetOpenFilename(FileFilter:="XML
Files
(*.xml)", Title:="Last Weeks Projections for Marc")
Workbooks.Open Filename:=UFOld

Sheets("Marc").Select
Sheets("Marc").Name = "Marc Last Week"
Sheets("Marc Last Week").Copy
After:=Workbooks("Projection
Summary.xls").Sheets("Marc")
Windows(UFOld).Activate
ActiveWindow.Close
Kill (UFOld)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Subscript out of range?
 

Jason

You need just the file name in the Windows( ).Activate. You've got the full
path.

try it like this:

Set wbUF = Workbooks.Open(Filename:=UF)
UFFN = wbUF.Name

Sheets("Marc").Select
Sheets("Marc").Copy _
After:=Workbooks("Projection Summary.xls").Sheets("Summary")
Windows(UFFN).Activate
ActiveWindow.Close

Regards

Trevor


"Jason Hancock" wrote in message
...
As a temporary fix to my larger problem so I can at least get the macro
in the system, I have put together this code. My problem is, I am
getting a Runtime Error 9: Subscript Out of Range on the
Windows(UF).Activate portion. Shouldn't I be able to reference a
variable I have defined earlier in a function like this? Also, am I
asking for trouble with the Kill function set up like it is?

Dim UF As Variant
Dim UFOld As Variant

UF = Application.GetOpenFilename(FileFilter:="XML Files
(*.xml)", Title:="This Weeks Projections for Marc")
Workbooks.Open Filename:=UF

Sheets("Marc").Select
Sheets("Marc").Copy After:=Workbooks("Projection
Summary.xls").Sheets("Summary")
Windows(UF).Activate
ActiveWindow.Close

UFOld = Application.GetOpenFilename(FileFilter:="XML Files
(*.xml)", Title:="Last Weeks Projections for Marc")
Workbooks.Open Filename:=UFOld

Sheets("Marc").Select
Sheets("Marc").Name = "Marc Last Week"
Sheets("Marc Last Week").Copy After:=Workbooks("Projection
Summary.xls").Sheets("Marc")
Windows(UFOld).Activate
ActiveWindow.Close
Kill (UFOld)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!