Subscript out of range?

[Jason Hancock]

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)

 

[Chip Pearson]

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

 

[Chip Pearson]

Workbooks(FName).Activate

might as well be
Windows(FName).Activate


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

 

[Trevor Shuttleworth]

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