Consulting

Results 1 to 8 of 8

Thread: Copy Normal.dotm file

  1. #1
    VBAX Regular
    Joined
    Apr 2007
    Posts
    39
    Location

    Copy Normal.dotm file

    Good Morning!

    I'm trying to make an auto Normal.dotm copy file routine when I open a excel file because sometimes i lost my saved macros. Since the file Normal.dotm has all the macros saved seems to me a good idea to save it from time to time.


    The code is the follwing


    Private Sub Workbook_open()
    Dim a As Integer
    
    
    a = Range("A1").Value + 1
    
    
    Range("A1").Value = a
    
    
    FileCopy "C:\Users\njesus\AppData\Roaming\Microsoft\Templates\Normal.dotm", "C:\Users\njesus\Dropbox\Setcontrol\VBA\Normal" & Range("A1").Value & ".dotm", strDBLocation
    
    
    End Sub


    When the macro runs i'm getting the error:

    "Compile error

    Wrong number of arguments or invalid property assignment"


    maybe because of the Windows folder properties.

    Also i don't have Administrator rights because it is my work desktop

    Is there a better way to solve it?

    Regards
    Microsoft 2010 | VBA 7.1

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,182
    In Normaltemplate Document macromodule:

    Sub Autoexit()
      c00 = Options.defaultfilepath(5) + "\"
      c01 = Format(NormalTemplate.builtindocumentproperties(12), "yyyy-mm-dd")
    
      If Dir(c00 & c01 & "*") = "" Then
        Documents.Open NormalTemplate.FullName
        ActiveDocument.SaveAs c00 & c01 & "_normal.dotm"
        ActiveDocument.Close
      End If
    End Sub
    Last edited by snb; 05-10-2021 at 02:37 AM.

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,310
    Location
    Quote Originally Posted by dzogchen View Post
    I'm trying to make an auto Normal.dotm copy file routine when I open a excel file because sometimes i lost my saved macros.
    Why would you want to make a copy of Word's Normal.dotm file when you open and Excel workbook? Makes no sense to me.

    Moreover, if you aren't making backups using the tools that come with Windows, why not?
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,611
    Location
    I don't know about Normal.dotm, but I SaveCopyAs Excel's Personal.* every time I edit it, then use Windows to backup that entire folder weekly

    In Excel's Personal THisWorkbook Code page
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        If Not Me.Saved Then ThisWorkbook.Save
    End Sub
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
        Me.SaveCopyAs ("D:\!Backup\_Personal\Personal - " & CStr(CDbl(Now)) & ".xls")
    End Sub
    CStr(CDbl(Now)) just adds a timestamp so I can Save often without overwriting previous versions.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,959
    Location
    'FileCopy "C:\Users\njesus\AppData\Roaming\Microsoft\Templates\Normal.dotm", "C:\Users\njesus\Dropbox\Setcontrol\VBA\Normal" & Range("A1").Value & ".dotm", strDBLocation
    
    FileCopy "C:\Users\njesus\AppData\Roaming\Microsoft\Templates\Normal.dotm", "C:\Users\njesus\Dropbox\Setcontrol\VBA\Normal" & a & ".dotm"

  6. #6
    VBAX Regular
    Joined
    Apr 2007
    Posts
    39
    Location
    Afternoon!

    @snb: I tested your code and it worked on microsoft word. Thank you!

    @macropod: I'm trying to copy the file in excel because i have always one excel spreadsheet open and since i have one routine to make periodicly backus from time to time in that spreadsheet i could use that routine also to copy the Normal.dotm file. At the beginning i must confess that i thought to copy Normal.dotm file was straight away. Using backups tools that come with Windows could be a solution also, i didn't remember that, thank you!

    @
    SamT: i will test your code, give me 1h or 2h. Thank you!

    Regards
    Microsoft 2010 | VBA 7.1

  7. #7
    VBAX Regular
    Joined
    Apr 2007
    Posts
    39
    Location
    Afternoon,

    @Kenneth Hobs: That is what I was looking for!!! Thank you very much!!!!

    @SamT: Thank you for your reply!
    Microsoft 2010 | VBA 7.1

  8. #8
    VBAX Regular
    Joined
    Apr 2007
    Posts
    39
    Location
    Afternoon again,

    I can say now for those who may interest that for this code works:

    FileCopy "C:\Users\njesus\AppData\Roaming\Microsoft\Templates\Normal.dotm", "C:\Users\njesus\Dropbox\Setcontrol\VBA\Normal" & Sheets("Folha2").Range("F2").Value & ".dotm"

    you need to change the Windows folder securety properties

    Regards
    Last edited by dzogchen; 05-11-2021 at 09:49 AM.
    Microsoft 2010 | VBA 7.1

Posting Permissions

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