Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't save template (Excel 2000/Sr1)

    The following sub is suppose to take an activeworkbook that was opened via a program from an template and then save it as a template but it doen't work. I do not get an error message, it just doesn't work. The object g_objAppInstance is the Application object and the line "
    InitialFname = Application.TemplatesPath & g_objAppInstance.ActiveWorkbook.name" is working fine so I know that g_objAppInstance is good. Does anyone see what is wrong?

    Public Sub SaveAsTemplateHandler()

    On Error GoTo Event_Err
    Dim InitialFname As String
    Dim NewFileName As String
    Dim StrPos As Long
    Dim flag As Boolean

    InitialFname = Application.TemplatesPath & g_objAppInstance.ActiveWorkbook.name

    StrPos = InStr(InitialFname, ".xls")
    If (StrPos > 0) Then
    InitialFname = Left(InitialFname, (StrPos - 1))
    End If


    NewFileName = Application.GetSaveAsFilename(InitialFilename:=Ini tialFname, _
    FileFilter:="Template (*.xlt), *.xlt", Title:="Save As Workpaper Template")



    If NewFileName <> "False" Then
    'There is a bug in Excel 2000 that sometimes appends a "." to a filename without an
    'extension. If this is passed into SaveAs, unexpected results may occur.

    'Check for a trailing period.
    If Right(NewFileName, 1) = "." Then
    NewFileName = Left(NewFileName, Len(NewFileName) - 1) 'Remove the period from the string.
    End If


    If g_objAppInstance Is Nothing Then
    MsgBox "g_objAppInstance is nothing"
    End If

    If g_objAppInstance.ActiveWorkbook Is Nothing Then
    MsgBox "g_objAppInstance.ActiveWorkbook is nothing"
    End If

    g_objAppInstance.ActiveWorkbook.SaveAs FileName:=NewFileName, FileFormat:=xlTemplate


    MsgBox " Did it get saved = " & g_objAppInstance.ActiveWorkbook.Saved ' This is always false fro some reason



    End If

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can't save template (Excel 2000/Sr1)

    In Xl 97 when I step thru it it saves and says TRUE.

    BUT I added the lines:
    <pre>Dim g_objAppInstance
    Set g_objAppInstance = Application</pre>


    otherwise I got a "Variable not defined" error for g_objAppInstance (I use option explicit)
    and then I got an "object required" error since g_objAppInstance was "nothing"

    When you step thru the code does it do what it is supposed to? Where does it "fail"?
    Steve

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't save template (Excel 2000/Sr1)

    The g_objAppInstance was set earlier in another module to Application, you could just use ActiveWorkbook for testing. The code does not report an error, when the saveas line is executed it just retrurns false right away and the template is not saved.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Can't save template (Excel 2000/Sr1)

    Is the file saved and the msgbox is wrong or is the file NOT saved and the msgbox is correct ?

    Like I said, It seems to work in xl97 (with the 2 lines I added). I save a template with a name I choose in the officetemplates folder.

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't save template (Excel 2000/Sr1)

    FYI, I put your code in a module in Excel 2002 SP-2, and declared g_objAppInstance like Steve did. The template is saved, and the message box returns True.

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Can't save template (Excel 2000/Sr1)

    [EDIT; NEVER MIND, all this hogwash only applies to the Boolean FALSE which is returned by GetsaveAsFileName when the user Cancels. I'll leave it here in case it has some future use.]

    Jim, a wild guess without testing the code, based on a prior experience. In this line

    If NewFileName <> "False" Then

    NewFileName is a Boolean when it returns False; therefore I think testing against a string "False" gives a bogus result. (Check the GetSaveAsFileName method in Help, you'll see what I'm on about. ) Try

    Dim NewFileName as Variant

    And after the NewFileName variable is set, find out via

    MsgBox Typename(NewFileName)

    If that's the problem, you can test with

    If Typename(NewFileName) = Boolean Then 'it's false, handle accordingly
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't save template (Excel 2000/Sr1)

    Hmmm.

    Did you check if it REALLY didn't save?

    Could there be any event code that fires a recalc after the SaveAs?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •