Change Part Of Before Save Code In Bulk?
Results 1 to 6 of 6

Thread: Change Part Of Before Save Code In Bulk?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,349
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Change Part Of Before Save Code In Bulk?

    I have loads of files that have a before save code within them. I need to change the location, is it possible to change part of the code without opening them all individually?

    This is the code

    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    
        If MsgBox("Save?", vbYesNo, "Confirm") = vbNo Then Exit Sub
       Application.DisplayAlerts = False
        ActiveSheet.Copy
       ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\MANAGER\Desktop\Darrens Catalogues\Suspension_Catalog2.1A.csv", FileFormat:=xlCSV, CreateBackup:=False
        ActiveWorkbook.Close False
    End Sub
    All I need to change is the 'Manager' in red to Manager1!

    From another code I have it uses ADO connection and SQL that doesn't open the files is it possible here?
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  2. #2
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,349
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Part Of Before Save Code In Bulk?

    Is anyone able to help with this please?
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  3. #3
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,240
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Change Part Of Before Save Code In Bulk?

    Have you considered using the VBExtensibility library ?

    Edit:
    But that would open the files. However you could temporarly disable the application events, open the files, make the changes , save and close ... I think you could even have the excel files opened in another hidden instance of excel .
    Last edited by Jaafar Tribak; Jun 27th, 2019 at 05:43 AM.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  4. #4
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,349
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Part Of Before Save Code In Bulk?

    Quote Originally Posted by Jaafar Tribak View Post
    Have you considered using the VBExtensibility library ?

    Edit:
    But that would open the files. However you could temporarly disable the application events, open the files, make the changes , save and close ... I think you could even have the excel files opened in another hidden instance of excel .
    Thanks for the reply but I don't understand it!!

    It doesn't matter if the code opens, makes the changes and closes them again.
    Last edited by Dazzawm; Jun 27th, 2019 at 08:12 AM.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,045
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Change Part Of Before Save Code In Bulk?

    Cross-posted: https://www.ozgrid.com/forum/forum/h...e-code-in-bulk

    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,240
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Change Part Of Before Save Code In Bulk?

    Take a look at this http://www.cpearson.com/excel/VBE.aspx

    Focus on the Find and ReplaceLine methods of the CodeModule object.

    Regards.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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