I have done a lot of scripting with Word templates and have a save macro on
the toolbar to auto save to system. I have an ocx desktop control which will
pre-populate info on the new document.
I am trying to duplicate this effort in Excel but am having an issue when
saving.
With a new xlt template it will pre-load the data and when I use the toolbar
macro to save it works fine the first time.
Saves as "u:\COMM_red1.xls"
If I then delete the xls created and then new template it will pre-load fine
but, upon saving it errors. Says "red1.xls" could not be found.
I created the toolbar macro by:
1) Creating the macro code
2) Customize toolbar New 'SaveIR'
3) Commands Macro Custom button : dragged this to the toolbar macro
4) Right clicked on cutom button and assign macro 'SaveIR'

If I open the template and look at the assign macro it has
"U:\COMM_red1.xls" and not 'SaveIR'

What am doing wrong?

Here is my code of workbook open to load and the Save macro:

Workbook Open:
Private Sub Workbook_Open()
Sheets("Sheet1").Unprotect
Rows(92).Hidden = False
Rows(90).Hidden = False
Sheets("Sheet1").Protect
Set objIRConn = CreateObject("irdesktopcontrol.irdesktopcontrolx")
objIRConn.Active = True

If objIRConn.Active Then

Sheets("Sheet1").Unprotect

'Drawer
strdrawer = objIRConn.Drawer

'Insured Name
strInsName = objIRConn.Filename
Sheet1.Cells(7, 3) = strInsName

'Account Number
strAcctNum = objIRConn.FileNum
Sheet1.Cells(9, 3) = strAcctNum


If strdrawer = "COMM" Then
Sheet1.CheckBox1 = True
ElseIf strdrawer = "SCOM" Then
Sheet1.CheckBox2 = True
ElseIf strdrawer = "PSIC" Then
Sheet1.CheckBox3 = True
End If


objIRConn.Active = False
If IsNumeric(StrUD2) Then
Rows(92).Hidden = True
Else
Rows(90).Hidden = True
End If
Sheets("Sheet1").Protect
End If

End Sub

Save Macro:
Sub SaveIR()
On Error Resume Next
Set objIRConn = CreateObject("irdesktopcontrol.irdesktopcontrolx")
If VarType(objIRConn) 0 Then
objIRConn.Active = True


If objIRConn.Active Then
strdrawer = objIRConn.Drawer
strCL = objIRConn.Filename

Dim myTemp, myTempName
Set myTemp = ActiveDocument.AttachedTemplate
myTempName = myTemp.Name
Dim myTemp1, myTempName1
Set myTemp1 = ActiveDocument
myTempName1 = myTemp1.Name
pos2 = InStr(1, myTempName1, "DOC0")
pos3 = InStr(1, myTempName1, "DOT0")
pos6 = InStr(1, myTempName1, "~")
pos7 = InStr(1, myTempName1, ".")

msg = "Are you sure you want to Save Document to ImageRight?"
Style = vbYesNo + vbInformation + vbDefaultButton1
Response = MsgBox(msg, Style)

If Response = vbYes Then

'Page Description
pos1 = InStr(myTempName, ".")
strpg = Mid(myTempName, 1, (pos1 - 1))
strdesc = strpg


sFilename = "u:" + strdrawer + "_" + strCL + ".xls"
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=sFilename, _
FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False

Application.Quit
Application.StatusBar = "Application Closing."

Else
ActiveWorkbook.Activate
End If

objIRConn.Active = False
End If
Set objIRConn = Nothing
End If
End Sub



Thanks,
Bryan