string assistance to open workbook via macro

  • Thread starter Thread starter fishy
  • Start date Start date
F

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?
 
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
 
It seems to debug at the Windows(link).activate

Jim Thomlinson said:
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 said:
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?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to shift 3
MACRO assistance 1
Macro to run on hidden sheet 4
getting a macro to run X amount of times.. 2
Macro to Close a specific workbook. 1
Change data with a macro 8
How to insert macro results on a separate spreadsheet in row order 1
Set destination sheet based on variable and paste data assistance 10

Back
Top