string assistance to open workbook via macro
 

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?

string assistance to open workbook via macro
 

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


"fishy" wrote:

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?

string assistance to open workbook via macro
 

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

"Jim Thomlinson" wrote:

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


"fishy" wrote:

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?