Refering to the current workbook in a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro that I have written to open a certificate and dump information
into textboxes from the open excel workbook. The problem is the macro refers
to my template workbook so if I save it as a different name the macro code
breaks. How do you refere to the current open workbook instead of the
workbook name? This is the code that I am having trouble with. Thank you for
your help.


Sub NewCert()
'
' NewCert Macro
' Macro recorded 5/29/2007 by Tim Peter
'

'
Workbooks.Open Filename:= _
"I:\work\Engineering Data\Product Data\Nozzles\Forms\Certificate.xls"
ActiveWindow.Zoom = 250
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 266.95,
265.57, _
35.39, 12.9).Select
Selection.Characters.Text = ""
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Formula = _
"'[Nozzle_Certificate_Form_20ft-Bell.xls]Data 20 cft Bell'!$K$2"
ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 345.53,
362.8, _
90.28, 18#).Select
Selection.Characters.Text = ""
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
 
You can use ActiveWorkbook to refer to the current book. But since you are
opening the book in the macro that would not help with the issue of saving it
under a different name.

You can either assume the sheet you want to modify is open and the current
sheet or you can Application.Dialogs(xlDialogOpen).Show to allow them to
select the file.

Peter Richardson
 
Back
Top