Sleeper: Rationalize my import / export code

[Staticbob]

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

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

[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".

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.

[Howard Kaikow]

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".

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.

[Zack Barresse]

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.

[Howard Kaikow]

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.

[Anne Troy]

Mark!!

[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.

'.....
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

[TonyJollans]

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:

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

[Mark O'Brien]

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.

[Howard Kaikow]

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.

'.....
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.

[Richie(UK)]

For reference, see also:

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

[XL-Dennis]

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.