Macro to Select file location for lookups

[Cat129]

Morning All,

I'm not sure if this is possible but was hoping you could tell me.

I need to write a macro that does lots of lookups and others things by comparing two spreadsheets. The spreadsheet that is being used as the lookup will have a different file name each time it is run. It is renamed to the date it was last updated and then replaced by the one doing the looking up once all the lookups etc have been completed.

I was thinking of creating some sort of pop up that would ask you to select the file and then the file that you select is used through out the rest of the code.

Is this possible? Or am I over complicating things?

Help!

Thanks,
Cat

 

[Norie]

Oops, added that path in at the last minute for a bit of error checking and got it kind of the wrong way round.

Anyway this is how it should look.

Option Explicit

Function GetUNC(strMappedDrive As String) As String
Dim objFso As Object
Dim objDrv As Object
Dim strDrive As String
    
    Set objFso = CreateObject("Scripting.FileSystemObject")

    strDrive = objFso.getdrivename(strMappedDrive)
    
    For Each objDrv In objFso.drives
        If objDrv.Path = strDrive Then Exit For
    Next objDrv
    
    If Not objDrv Is Nothing Then
        GetUNC = Replace(strMappedDrive, strDrive, objDrv.sharename)
    End If

    Set objFso = Nothing
    
End Function

 

[Cat129]

Thanks for all your help

Working great, however I have found a downside to using the function over the sub

There will be around 30 or more formulas that will need to use the function, and each formula that uses it creates a new popup for the user to select a file.

With the sub it stored the outcome and it only had to be selected once.

I just wanted to say, I really really appreciate your help, Thank You

 

[Norie]

Cat

Why aren't you using the function once to populate a (global) variable with the workbook path/name and using that throughout the rest of the code?

Actually, what 'function' are you referring to?

The only function I posted was the one to get the UNC path.

 

[Cat129]

Sorry, you are right.

I'm trying to do to many things at once and it didn't even occur to me. I just chucked another formula in without thinking.

I'm not great at VBA, completely self taught using the recorder and trawling through forums, so sometimes I completely miss the obvious.

Luckily I should be going on a course soon through work (Once I find one) so I will stop bugging you all so much