Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    looking for a way to update a file... (2003)

    I created an excel workbook that our project managers use on a monthly basis to study job cost. It has been pointed out to me that there are a few errors. I am wondering what the best way to fix the problem is:
    1-should I fix the problem in my Master file and send it back out
    2-is there a way to write a macro that could fix the problems, if so, how could i apply this "Patch"
    3-anyone have any other ideas...

    thanks,
    jackal

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

    Re: looking for a way to update a file... (2003)

    We'd need a bit more info.
    1) Does each manager fill the info with his/her own information?
    2) What kind of errors - incorrect formulas, defined names, charts, macro code, userforms, ...?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: looking for a way to update a file... (2003)

    Each manager completes what they determine to be a "Cost to Complete" the job and some other information as well. The errors are incorrect formulas, sum formula to be exact. The reason I am asking about the "Patch" option is to keep from having several different versions. I am trying to keep some type of file management with these job studies. When it comes time to study the job for the next month, there is a button that opens a form that adds a sheet for the month to be studied. Hope this helps explain a little better.

    thanks,
    jackal

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

    Re: looking for a way to update a file... (2003)

    It sounds like a "repair" macro could be a good way to handle this. You can write a macro that modifies formulas. The workbook to be modified should already be open, or you could display a dialog to let the user open it. The exact code depends on the structure of the workbook, of course, but you'd use code such as
    <code>
    With Workbooks("Management.xls")
    .Worksheets("Expenses").Range("K3").Formula = "=SUM(K4:K22)"
    .Worksheets("Assets").Range("P11").Formula = "=SUMIF(A1:A10,P11, B1:B10)"
    End With</code>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: looking for a way to update a file... (2003)

    I created a new workbook, with a button that reads "Update CIP". The following macro is attached to this button:
    <pre>Private Sub cmd1_Click()
    Application.Dialogs(xlDialogOpen).Show

    With ActiveWorkbook
    .Worksheets("Apr 07").Range("B45").Formula = "=sum(B28,B31,B35,B39,B43,B44)"
    .Worksheets("Apr 07").Range("C45").Formula = "=sum(C28,C31,C35,C39,C43,C44)"
    .Worksheets("Apr 07").Range("D45").Formula = "=sum(D28,D31,D35,D39,D43,D44)"
    .Worksheets("Apr 07").Range("E45").Formula = "=sum(E28,E31,E35,E39,E43,E44)"
    .Worksheets("Apr 07").Range("D8").Formula = "=IF(B8=0," & Chr(34) & Chr(34) & _
    ",IF(C8>0,(C8-B8)/C8," & Chr(34) & Chr(34) & "))"
    .Worksheets("Apr 07").Range("F2").Formula = "VERSION 4.1A"
    End With
    ActiveWorkbook.Close SaveChanges:=True
    End Sub</pre>


    One more question---When the workbook in question is opened, there is a form that is activated with OnOpen. Is there a way to not have this open or to disable it or close it without user interaction?

    thanks,
    jackal

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

    Re: looking for a way to update a file... (2003)

    Insert the line

    Application.EnableEvents = False

    above the line that displays the Open dialog, and

    Application.EnableEvents = True

    below it. This will suppress the execution of Workbook_Open (and other "automatic" macros).

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: looking for a way to update a file... (2003)

    This works great. thank Hans

    jackal

Posting Permissions

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