none
Very new to Scripting, Trying to get this macro to work. RRS feed

  • Question

  • I have probably got this all wrong.

    This Macro runs in Excel.

    It allows me to save the file.

    But it will not work for another other use who has access to this file.

    I know it is down to the Users\David.Murray, what I need is to able to amend The Path Variable

    Sub Create_TimeSlot_View()
    ' This copies and sorts the DATA from FSC.XLSM to from the Daily FSC, which is sent out each day.
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
        
        Columns("L:KO").Select
        Selection.EntireColumn.Hidden = True
        If ActiveSheet.AutoFilterMode Then Cells.AutoFilter
        
    Dim Year1 As Integer
    Year1 = DatePart("yyyy", Date)
    Month1 = DatePart("M", Date)
    Day1 = DatePart("D", Date)
    CurrentDate = Day1 & "-" & Month1 & "-" & Year1

     ChDir "C:\Users\david.murray\PERFORM GROUP\Change and Release Management - Change Management\FSC Backup"
        ActiveWorkbook.SaveAs Filename:= _
             "C:\Users\david.murray\PERFORM GROUP\Change and Release Management - Change Management\FSC Backup\FSC " & CurrentDate & ".xlsm"
        ChDir "C:\Users\david.murray\PERFORM GROUP\Change and Release Management - Change Management\FSC"
       ActiveWorkbook.SaveAs Filename:= _
          "C:\Users\david.murray\PERFORM GROUP\Change and Relea

    • Moved by Bill_Stewart Friday, March 15, 2019 4:57 PM This is not "scripts on demand"
    Wednesday, January 2, 2019 1:36 PM

All replies

  • This is not an Excel forum.  VBA is not a scripting language but is a form of VB included in Office products.

    Post your VBNA issues in the Excel Forum for best assistance.

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba


    \_(ツ)_/

    Wednesday, January 2, 2019 1:49 PM
  •  ChDir "C:\Users\david.murray\PERFORM GROUP\Change and Release Management - Change Management\FSC Backup"

     


    Save the file to a network share on a server instead of C:\Users. That would consolidate all backups from every user who has access to update the file and make it easier to restore if someone would corrupt the data.



    • Edited by MotoX80 Wednesday, January 2, 2019 3:44 PM
    Wednesday, January 2, 2019 3:38 PM
  • It is a VBA issue.  The VBA has to retrieve the current users profile and save to that.

    This is NOT a VBA or Excel forum and will likely give incorrect or incomplete answers for VBA Excel.  In this case the correct location is the current users profile.  This is where guesses are not helpful and is why we don't address VBA issues in this forum.

    Also note that the current username is not useful as the profile may not be the exact username if it has been rebuilt during an upgrade.  It can have an extension added and only the current user profile variable will be set correctly.

    The profile path is in the %USERPROFILE% environment variable.

    Here is an example: https://social.msdn.microsoft.com/Forums/office/en-US/8976d5c2-7d58-4d4a-b774-3cee980e5f5e/searching-for-current-user-path-with-vba?forum=accessdev

    ChDir Environ("USERPROFILE") + \PERFORM GROUP\Change and Release Management - Change Management\FSC Backup"

    This thread will be moved to the VBA forum by an administrator where you will be able to get more accurate and complete information.


    \_(ツ)_/

    Wednesday, January 2, 2019 3:58 PM