Results 1 to 3 of 3
  1. #1
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update closed workbooks (2000 up)

    I have several thousand workbooks in which I need to update data due to changing legislation, working practices, QA requirements etc. Nominally, all I need to do is the range A1:I9 with a new format which I have successfully done in the past by running a macro that opens each file in turn, copies the new data from a template, then saves and closes the file.

    These are production parts lists which may contain a number of worksheets, depending on which issue it is on. Typically, if a small change is made, the current worksheet is copied to the same workbook, the stated issue number changed and whatever revisions that are required made. However, we need to trap this event and mark this new worksheet as 'provisional' to prevent it being used before it has been authorised. (In theory, the production department could open the workbook in read-only mode and assume the 'top' worksheet is current.)

    I have found that I can detect the copying of the workbook by adding the following code to the worksheet:

    <code>Private Sub Worksheet_Activate()
    If Right(ActiveSheet.Name, 1) = ")" Then
    'Code to mark as provisional here...
    End Sub</code>

    ...but of course the existing workbooks/worksheets do not contain this code.

    So, help please! I think I have two choices:

    1] Copy a new worksheet (including the data changes and Private Sub Worksheet_Activate() macro) to the workbook being updated. Copy all data apart from the range A1:I9 from the existing worksheet into the new worksheet, then delete the old worksheet.
    Problem - the existing worksheet may contain graphics which may be anywhere on the worksheet and would have to be included in the copy.

    2] Copy the new A1:I9 block from the template to the existing worksheet and add the Private Sub Worksheet_Activate() macro.
    Problem - I haven't the slightest idea how to copy a macro from one worksheet to another!

    Solution 2 would be preferred as it covers me against not copying something I haven't thought of, such as comments added outside the expected range!

    I trust this makes sense - all comments and suggestions gratefully received!

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

    Re: Update closed workbooks (2000 up)

    In the workbook containing the macro, set a reference to Microsoft Visual Basic for Applications Extensibility 5.3 (using Tools | References in the Visual Basic Editor).

    You can now use code like this:

    Dim wbk As Workbook
    Dim strLines As String
    Dim vbc As CodeModule
    ' Important - turn off event handling
    Application.EnableEvents = False
    ' Open workbook
    Set wbk = ...
    ' Use name of worksheet that contains correct Worksheet_Activate procedure
    Set vbc = ThisWorkbook.VBProject.VBComponents("MySheet").Cod eModule
    ' Fill string variable with code
    strLines = vbc.Lines(1, vbc.CountOfLines)
    ' Use name of worksheet to which you want to add the code
    Set vbc = wbk.VBProject.VBComponents("OtherSheet').CodeModul e
    ' Delete any existing code
    vbc.DeleteLines 1, vbc.CountOfLines
    ' Add code from the string variable
    vbc.AddFromString strLines
    ' Turn on event handling again
    Application.EnableEvents = True

    For background, see Programming To The VBE on Chip Pearson's site.

  3. #3
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update closed workbooks (2000 up)

    Thank you! - that looks just the ticket!

    It's not knowing where to look that usually trips me up... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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