.xls* for .xlsx not working when activating window

[jordanburch]

Hey Guys,

I have the below code. Its very strange because it opens the file just fine, but it doesnt activate the file . The file is a .xlsx file. Any ideas?

runtime error 9 subscript out of range


works fine and opens file
Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Monthly Suspense Recon\" & ThisWorkbook.Worksheets("Variables").Range("A3").Value & "\" & ThisWorkbook.Worksheets("Variables").Range("A1").Value & "\" & "CARS" & "\" & ThisWorkbook.Worksheets("Variables").Range("A2").Value & " " & "CARS Detail Transaction Lines.xls*")


does not work and activate the window
Windows(ThisWorkbook.Worksheets("Variables").Range("A2").Value & " " & "CARS Detail Transaction Lines.xls*").Activate
Range("E11").Select

 

[Fluff]

You cannot use wildcards like that, try

Dim Wbk as Workbook
Set Wbk=Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Monthly Suspense Recon\" & ThisWorkbook.Worksheets("Variables").Range("A3").Value & "\" & ThisWorkbook.Worksheets("Variables").Range("A1").Value & "\" & "CARS" & "\" & ThisWorkbook.Worksheets("Variables").Range("A2").Value & " " & "CARS Detail Transaction Lines.xls*")

Wbk.activate

 

[jordanburch]

You cannot use wildcards like that, try

Dim Wbk as Workbook
Set Wbk=Workbooks.Open ("K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Monthly Suspense Recon\" & ThisWorkbook.Worksheets("Variables").Range("A3").Value & "\" & ThisWorkbook.Worksheets("Variables").Range("A1").Value & "\" & "CARS" & "\" & ThisWorkbook.Worksheets("Variables").Range("A2").Value & " " & "CARS Detail Transaction Lines.xls*")

Wbk.activate

thanks fluff! why is it that it works when I open the file but not when I try to activate it?

Jordan

 

[Fluff]

I'm surprised that you can actually open the file with a wildcard.