Rationalize my import / export code

Guys,

I have this code that I am using to export and import modules into a workbook to be upgraded.

2 things.

1 - This line - Application.Dialogs(xlDialogActivate).Show IS NOT making the selected workbook acitive. I need to do this so I can define TWB as the target workbook

2 - I'm sure I could put a couple of loops in my code to reduce it a bit.

Thanks in advance . .
Bob

Code:

---

Private Sub upgrade()
    'Select target workbook
    Dim TWB As Workbook 'Target Workbook
    Dim UWB As Workbook 'Upgrade Workbook
    Dim FName As String 'temp filename for export / import
    Application.ScreenUpdating = False
    Set UWB = ActiveWorkbook
    MsgBox "Please select the Workbook to upgrade" & vbNewLine & "from the following list . . ." & vbNewLine & "(Click OK to show)"
    Application.Dialogs(xlDialogActivate).Show
    Set TWB = ActiveWorkbook
    'delete existing modules in Target WB
    Dim VBComp As VBComponent
    On Error Resume Next
    With TWB
        Set VBComp = ThisWorkbook.VBProject.VBComponents("frmsavewr")
        ThisWorkbook.VBProject.VBComponents.Remove VBComp
        Set VBComp = ThisWorkbook.VBProject.VBComponents("frmsavecr")
        ThisWorkbook.VBProject.VBComponents.Remove VBComp
        Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_Sendmail")
        ThisWorkbook.VBProject.VBComponents.Remove VBComp
        Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_spellcheck")
        ThisWorkbook.VBProject.VBComponents.Remove VBComp
        Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_Toolbar")
        ThisWorkbook.VBProject.VBComponents.Remove VBComp
    End With
    'import / export code
    With UWB
        FName = .Path & "\code.txt"
        .VBProject.VBComponents("frmsavewr").Export FName
    End With
    TWB.VBProject.VBComponents.Import FName
    With UWB
        FName = .Path & "\code.txt"
        .VBProject.VBComponents("frmsavecr").Export FName
    End With
    TWB.VBProject.VBComponents.Import FName
    With UWB
        FName = .Path & "\code.txt"
        .VBProject.VBComponents("Mdl_Sendmail").Export FName
    End With
    TWB.VBProject.VBComponents.Import FName
    With UWB
        FName = .Path & "\code.txt"
        .VBProject.VBComponents("Mdl_spellcheck").Export FName
    End With
    TWB.VBProject.VBComponents.Import FName
    With UWB
        FName = .Path & "\code.txt"
        .VBProject.VBComponents("Mdl_Toolbar").Export FName
    End With
    TWB.VBProject.VBComponents.Import FName
    Application.ScreenUpdating = True
End Sub

---

Answer to your questions:

1. It is making the other workbook active, you just can't see it because you have set ScreenUpdating to false. (set screenupdating to true then run your code to confirm this)

2. What is this piece of code supposed to do? The "With TWB" part is doing nothing because you keep referring to "ThisWorkbook".

Code:

---

With TWB
    Set VBComp = ThisWorkbook.VBProject.VBComponents("frmsavewr")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("frmsavecr")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_Sendmail")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_spellcheck")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_Toolbar")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
End With

---

Another general note. Get rid of the "On Error Resume Next". This is not helping you debug your code. Chances are, Excel will tell you where you problem is if you don't tell it to ignore the errors.

Quote:

---

Originally Posted by Mark O'Brien

Answer to your questions:

1. It is making the other workbook active, you just can't see it because you have set ScreenUpdating to false. (set screenupdating to true then run your code to confirm this)

2. What is this piece of code supposed to do? The "With TWB" part is doing nothing because you keep referring to "ThisWorkbook".

Code:

---

With TWB
    Set VBComp = ThisWorkbook.VBProject.VBComponents("frmsavewr")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("frmsavecr")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_Sendmail")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_spellcheck")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
    Set VBComp = ThisWorkbook.VBProject.VBComponents("Mdl_Toolbar")
    ThisWorkbook.VBProject.VBComponents.Remove VBComp
End With

---

Another general note. Get rid of the "On Error Resume Next". This is not helping you debug your code. Chances are, Excel will tell you where you problem is if you don't tell it to ignore the errors.

---

Do NOT get rid of necessary On Error statements.
In this case, the statement is absolutely needed.

Quote:

---

Originally Posted by Howard Kaikow

Do NOT get rid of necessary On Error statements.
In this case, the statement is absolutely needed.

---

I think he was referring to when Debugging your code. Sometimes the Resume Next is not your best friend when developing/debugging, although useful for any kind of (final) product. It seems you two are on the same page, different paragraphs. :)

Quote:

---

Originally Posted by firefytr

I think he was referring to when Debugging your code. Sometimes the Resume Next is not your best friend when developing/debugging, although useful for any kind of (final) product. It seems you two are on the same page, different paragraphs. :)

---

I was referring to necessary error statements.
In this case, the error statement is necessary because of what is being done.

One could argue that an On Error GoTo 0 should be inserted as soon as the On Error Resume Next is no longer required.

On Error Resume Next can be useful even in VB .Net.

Mark!!



:006:

Hi all,

Well, for debugging purpose it's obvious that the "On Error Resume Next" is not good as it ignore any kind of problems.

In general I prefer and recommend to to catch the underlying errors enabling us to find out what's wrong.

Code:

---

'.....
On Error Goto Error_Handling
'.....
Exit_Here:
Exit Sub
Error_Handling:
MsgBox "Error: " & Err.Number & vbCrLf _
& "Description: " & Err.Description
Resume Exit_Here

---

For production-code that has been debugged and tested and where we know what kind of error that may occur and it's not subject to any actions by the end-users we may use the Resume Next-statement or better use an If-statement/Case-select to singel out unexpected errors.

Nowadays with so many different kind of configurations and so many versions of Windows as well as Excel in use it's a must to take error-handling seriously :)

Kind regards,
Dennis

To get back to the original question here, what symptom(s) indicate that the dialog is not activating the workbook. There is nothing obviously wrong with that bit of the code.

As Mark has said, though, the With TWB block doesn't actually make use of TWB. It does rather look as if that is the problem. And I would agree that if the On Error statement were disabled during debugging it would probably help.

And, yes, a loop would shorten your code. Something along the lines of:

Code:

---

FName = .Path & "\code.txt"
For Each mod in Array("frmsavewr","frmsavecr","Mdl_Sendmail", etc.)
    Set VBComp = TWB.VBProject.VBComponents(mod)
    TWB.VBProject.VBComponents.Remove VBComp
    UWB.VBProject.VBComponents(mod).Export FName
    TWB.VBProject.VBComponents.Import FName
Next

---

I am assuming TWB is correct where I have it shown in red, and I have not checked your code thoroughly - it just looks like that is what you are doing

Quote:

---

Originally Posted by Howard Kaikow

Do NOT get rid of necessary On Error statements.
In this case, the statement is absolutely needed.

---

I have never found error trapping necessary or useful while debugging code. The same could be said for your contributions to this thread.

Quote:

---

Originally Posted by XL-Dennis

Hi all,

Well, for debugging purpose it's obvious that the "On Error Resume Next" is not good as it ignore any kind of problems.

In general I prefer and recommend to to catch the underlying errors enabling us to find out what's wrong.

Code:

---

'.....
On Error Goto Error_Handling
'.....
Exit_Here:
Exit Sub
Error_Handling:
MsgBox "Error: " & Err.Number & vbCrLf _
& "Description: " & Err.Description
Resume Exit_Here

---

For production-code that has been debugged and tested and where we know what kind of error that may occur and it's not subject to any actions by the end-users we may use the Resume Next-statement or better use an If-statement/Case-select to singel out unexpected errors.

Nowadays with so many different kind of configurations and so many versions of Windows as well as Excel in use it's a must to take error-handling seriously :)

Kind regards,
Dennis

---

I don't disagree, but I was referring to keeping only "necessary" on error resume next statements.

If a programmer does not know up front that such statements are needed in particular places, they are going to get in trouble anyway.

You would not believe some the sloppy error handling in code that I've had to fix for clients.

For reference, see also:

http://www.mrexcel.com/board2/viewto...=531530#531530

Quote:

---

You would not believe some the sloppy error handling in code that I've had to fix for clients.

---

Me too but the good news is that I charge the clients for it ;)

I believe we all agree about the need of error-statements and I regret that this subject is not viewed on public forums as this aspect set the final quality of the solutions.