How to loop through named ranges

Question

I have some code that imports .bas files and i need it to run though 2 named ranges. The first (MacroName) would be a list of file names (e.g exam.bas, print.bas...) and the second (ImportDate) will be the date that the file was imported.

The code below loops though the folder that contains .bas files. It then checks the date modified of the file against the ImportDate named range. But I can't think of a way to loop the named ranges

For Each objFile In objFSO.GetFolder(szImportPath).Files
    If objFile.Name Like "*Import*" Then GoTo skipImport ' Skips the Import Module
    If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _
        (objFSO.GetExtensionName(objFile.Name) = "frm") Or _
        (objFSO.GetExtensionName(objFile.Name) = "bas") And _
        objFile.DateLastModified > Range("ImportDate") Then

        Dim CurrentModuleName

        CurrentModuleName = Left(objFile.Name, (InStrRev(objFile.Name, ".", -1, vbTextCompare) - 1))
        '^ Gets the file name of the module being imported and removes the extension

        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents(CurrentModuleName)
        VBProj.VBComponents.Remove VBComp
        '^ Removes the module that is to be replaced

        Range("MacroName") = objFile.Name
        Range("ImportDate") = Format(Date, "dd/mm/yyyy") & " " & Format(Time, "hh.nn.ss")
        '^ Keep time as without, it will import the same module throughout the day when opened.

        cmpComponents.Import objFile.path
    End If      
skipImport:
Next objFile

I am hoping that it reads the name of the file, checks the date and then if the date modified of the imported file is greater, deletes the current and replaces.

Are you doing this so that you always have the latest version of .bas files in your project? — Zac, Jun 26 '19 at 11:03
@Zac yes it is. there are four people that need the latest versions and they are spread out through the country. — Robert Hall, Jun 26 '19 at 11:05
Without going through your code (which I presume does successfully import the .bas files), I would remove the complication of checking for dates. I've done this in the past where everytime the workbook open, I import the .bas file(s) from the folder. This folder always has the latest version. This way no matter who opens the file, they will always get the correct version. Only complication is that all users must have access to the folder — Zac, Jun 26 '19 at 11:09
I have tried that but for a paper flow reason, i need to log the date of the last import for each file. Originally i held the files online but but it would not work with multiple files, so they are now in a rar file that gets downloaded, extracts then imports. — Robert Hall, Jun 26 '19 at 11:17

score 0 · Answer 1 · answered Jun 26 '19 at 11:12

0

Sub test()
Dim r As Range
For Each r In ThisWorkbook.Names("MacroName").RefersToRange
    Debug.Print r.Address
Next r

End Sub

answered Jun 26 '19 at 11:12

Harassed Dad

4,669
1
10
12

How to loop through named ranges

1 Answers1