As implemented on VolMan – 02/12/2017

The need for a Config file.

Originally on VolMan the settings sheet had all the paths for Site, Office, Shana PC. This is cumbersome. It made it necessary for VolMan to figure out where it is sited, who is the user, why is it being opened – mostly from the file path and filename. Not very elegant!

The idea now is, for the VolMan to be (as far as possible) generic.

How it works

To do that, we have a Config.txt file in the same folder as VolMan.xlsm. There all the site-related data is held.

Open VolMan is opened, the first thing that happens is to read in the Config.txt settings into the Settings sheet. This is done in a simple text file open, not ADO – to keep it simple.

Note that VolMan is not saved by the users. Therefore these Config settings do not persist.

Settings Sheet

The Config settings are ‘pasted’ into a Range(“ConfigSettings”). This is a single cell range as Top-Left of a larger range of variable depth.

[The plan is]

Config data is ‘name – value pairs’. VolMan will create workbook scoped Named Ranges with same name as the ‘name’, but with no spaces.

THIS WAY new Config settings can be added to the Config.txt without having to adjust the Named Ranges on Settings sheet.

Config.txt

Two columns
Comma separated
The ‘name’ part must correspond to the Named Range in the code, but can have spaces.
MUST not have carriage-returns beyond the last line of data! (easily made mistake!)

GetConfigSettings

Came from this tutorial: http://www.homeandlearn.org/open_a_text_file_in_vba.html

Sub GetConfigSettings()

    'Final code

    Dim FilePath As String
    
    FilePath = ThisWorkbook.Path + "\Config.txt"
    
    Open FilePath For Input As #1
    
    iRow = 1
    
    Do Until EOF(1)
    
        Line Input #1, LineFromFile
        
        LineItems = Split(LineFromFile, ",")
        
        Range("ConfigData").Cells(iRow, 1).Value = LineItems(0)
        Range("ConfigData").Cells(iRow, 2).Value = LineItems(1)
        
        iRow = iRow + 1
    
    Loop

    Close #1

End Sub

Create Named Ranges from Config settings.

'a great resource on Named Ranges
'https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges


Sub CreateNamedRanges()

    Dim rngCell As Range
    iRow = 1
    
    Do While Range("ConfigData").Cells(iRow, 1).Value <> ""
    
        strRangeName = Range("ConfigData").Cells(iRow, 1).Value
        strRangeName = Replace(strRangeName, " ", "")
        Set rngCell = Range("ConfigData").Cells(iRow, 2)
                            
        'Chip Pearson https://www.pcreview.co.uk/threads/deleting-a-named-range-in-vba.980486/
        
        ThisWorkbook.Names(strRangeName).Delete
        
        'there is no need to Delete a Name if all we're doing is to modify the RefersTo range
        'So, Deleting and Add makes no sense.
        'A BETTER idea is to Delete ALL Names in the Config Settings block and Add afresh.
        'Why? Because if for any reason the 'Name' part of the Name:Value pair has had to change
        'then ORPHANS will be left behind. Clean up will be a good thing.
        'BUT even then, this only works if the block is extended, not shrunk :(
        'Hmmm .......
        'In any case, Names added are not saved by user. Only those in a Dev Save are saved.
        'WHY NOT ...
        '(1) Delete Config Names on a Deploy?
        '(2) Delete Config Names on a Dev Save? (add Config to Dev Open, as populate big dropdowns)
        
        'This is a TO DO REFINEMENT
        
        ThisWorkbook.Names.Add Name:=strRangeName, RefersTo:=rngCell
    
        iRow = iRow + 1
    
    Loop

End Sub

 

TO THINK

Config Settings on Settings sheet needs a clear range to extend to a variable row depth.

? Put on separate column?

OR ? Put on separate sheet? Config (better I think)