Best Practice for Updating different workbooks in VBA

[silentwolf]

Hi guys,

I would just like to get some ideas on how you go about those kind of situation.
Several different Excel Workbooks need to be updated from time to time.

Because those workbooks require different Updates, different Code and are all .xls or xlsx files I created the codes
to update within the Personal.XLSB file.

On the Ribbon I placed a Button which runs one of those Updates when pressed.
But this allows at present only one "Type of Workbook" to be updated.

How would you go about it to keep it flexible so you can choose a workbook which need to be updated and run the update accordingly.
Would you put the code into the Personal XLSB File or is this bad practice?

Would you create a userform to pick files and then run the appropriate update?
Or create several xlsm files?

Just wondering how you would do it the best way?

Hope this is understandable what I am trying to do and maybe someone can give me some Ideas or better Solution of doing this.
Or perhabs just share your experience on how you would go about it?


Many thanks

Albert

 

[Anthony47]

Let me give my late and unsolicitated opinion

I understand that this is an automation that you use yourself (not designed to be operated by other users), so you don't need a sophisticated interface.
You could use an InputBox for selecting the file to update, then using (for example) Select Case you start the appropriate updating:

Sub MakeChoice()
Dim ibPrompt As String, Choice As Long
'
ibPrompt = "1 for FILEA" & vbCrLf & "2 for LongFileOrDescription" & vbCrLf & "3 for ThatOtherFile" & _
   vbCrLf & "4 for YouKnowWhat" & vbCrLf & "Or leave empty to abort"
Choice = Application.inputbox(prompt:=ibPrompt, Default:=0, Title:="Select file to update", Type:=1)
Select Case Choice
    Case 1
        Call CallingMacro(FullNameOfTheFile1)
    Case 2
        Call CallingMacro(FullNameOfTheFile2)
    'etc
    
End Select
End Sub

The button on the ribbon or on the QAT will invoke this Sub MakeChoice, and I assumed that you have a Sub CallingMacro that accept as a parameter the name of the file to work on

I think this could be suitable for max 5-10 different options

For longer options a userform with a listbox would probably be better, and could also allow for multiple choices in a single operation.

If this automation is for yourself, then using PERSONAL file is perfect

If the automation is to be used by other persons then a userform could be a better interface; in this case you will insert the automation within a file that you make available to these other guys.

This is the opinion from a self-defined "experieced user" (to mean that I am not a programmer)

 

[silentwolf]

Hi Anthony,
many thanks for your reply and giving me another idea of how to..

Yes that would be for just me using and updating the files so your suggestion is sure worth trying out!

Cheers
Albert