string assistance to open workbook via macro

[fishy]

I have the following macro:

Sub Macro2()

Dim datestamp As String
Dim link As String
datestamp = Format(Date, "yyyy_mm_dd")
link = "Arrestments Planning Model " & datestamp & ".xls"

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Centre Summary Plan").Select
Range("E5").Select
Windows("Planning Model.xls").Activate
Windows(link).Activate
Application.Run _
"'link'!Plan_Weekly_Summary"
Range("E39").Select
Windows("Planning Model.xls").Activate
ActiveCell.FormulaR1C1 = _
"='[link]Summary Plan'!R26C4"
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"='[link]Summary Plan'!R28C4"
Range("E5").Select
End Sub

The dim link is showing the correct file name but the code debugs at
Windows(link).Activate.

Can anyone help?

 

[Jim Thomlinson]

Is the workbook already open??? You need to open a workbook before you can
activate it. Try something more like this...

Sub Macro2()
dim wbkTarget as workbook
Dim datestamp As String
Dim link As String
datestamp = Format(Date, "yyyy_mm_dd")
link = "Arrestments Planning Model " & datestamp & ".xls"

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Centre Summary Plan").Select
Range("E5").Select
Windows("Planning Model.xls").Activate
on error resume next
set wbktarget = workbooks(link)
if wbktarget is nothing then
set wbktarget = workbooks.open("C:\" & link) 'modify directory
if wbktarget is nothing then
msgbox "Sorry can't find " & link
exit sub
end if
on error goto 0

Windows(link).Activate
Application.Run _
"'link'!Plan_Weekly_Summary"
Range("E39").Select
Windows("Planning Model.xls").Activate
ActiveCell.FormulaR1C1 = _
"='[link]Summary Plan'!R26C4"
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"='[link]Summary Plan'!R28C4"
Range("E5").Select
End Sub

 

[fishy]

It seems to debug at the Windows(link).activate

Is the workbook already open??? You need to open a workbook before you can
activate it. Try something more like this...

Sub Macro2()
dim wbkTarget as workbook
Dim datestamp As String
Dim link As String
datestamp = Format(Date, "yyyy_mm_dd")
link = "Arrestments Planning Model " & datestamp & ".xls"

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Centre Summary Plan").Select
Range("E5").Select
Windows("Planning Model.xls").Activate
on error resume next
set wbktarget = workbooks(link)
if wbktarget is nothing then
set wbktarget = workbooks.open("C:\" & link) 'modify directory
if wbktarget is nothing then
msgbox "Sorry can't find " & link
exit sub
end if
on error goto 0

Windows(link).Activate
Application.Run _
"'link'!Plan_Weekly_Summary"
Range("E39").Select
Windows("Planning Model.xls").Activate
ActiveCell.FormulaR1C1 = _
"='[link]Summary Plan'!R26C4"
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"='[link]Summary Plan'!R28C4"
Range("E5").Select
End Sub

--
HTH...

Jim Thomlinson

I have the following macro:

Sub Macro2()

Dim datestamp As String
Dim link As String
datestamp = Format(Date, "yyyy_mm_dd")
link = "Arrestments Planning Model " & datestamp & ".xls"

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Centre Summary Plan").Select
Range("E5").Select
Windows("Planning Model.xls").Activate
Windows(link).Activate
Application.Run _
"'link'!Plan_Weekly_Summary"
Range("E39").Select
Windows("Planning Model.xls").Activate
ActiveCell.FormulaR1C1 = _
"='[link]Summary Plan'!R26C4"
Range("E6").Select
ActiveCell.FormulaR1C1 = _
"='[link]Summary Plan'!R28C4"
Range("E5").Select
End Sub

The dim link is showing the correct file name but the code debugs at
Windows(link).Activate.

Can anyone help?