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

 

[Cat129]

You can use Application.GetOpenFilename to allow the user to browse for and select a file.

Dim strBasePath As String
Dim strFullPath As String
Dim strFileName As String
Dim strFilePath As String

    strBasePath = "C:\Test\"
    
    ChDir strBasePath
    
    strFullPath = Application.GetOpenFilename("Excel Workbooks (*.xls*),*.xls*", 1, "Choose file", "Select", False)
    
    If strFullPath <> "False" Then
    
        strFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
        
        strFilePath = Replace(strFullPath, strFileName, "")
        
        MsgBox "File selected:" & vbTab & strFileName, vbExclamation, "Congatulations!"
        
    End If

Brilliant, Thank You , there is so much Excel can do how any one person learns it all is beyond me.

So if I have understood the above correctly I would simply add in strFilePath in the formulas where I need the file to be referenced?

 

[Norie]

So if I have understood the above correctly I would simply add in strFilePath in the formulas where I need the file to be referenced?

Really depends on the formula and if the file will be opened or closed.

 

[Cat129]

Really depends on the formula and if the file will be opened or closed.

the majority would be vlookups, so it would be referenced within the range part, I assumed once I had the file name I could then just reference it like normal replacing [test.xls] with [strFilePath]

File would be closed

 

[Norie]

Are you talking about replacing the filename in the code or on the worksheet?

 

[Cat129]

In the code. The worksheet will be populated further by the code that is written after it has got a reference for the file name.

Without the file name/location there is no way for the spreadsheet to work

 

[Cat129]

Hi Norrie

When I use the below code it isn't working as expected, I wondered if I was doing something wrong. I just added a simple lookup onto the end of your code.

You can use Application.GetOpenFilename to allow the user to browse for and select a file.

Dim strBasePath As String
Dim strFullPath As String
Dim strFileName As String
Dim strFilePath As String

strBasePath = "C:\Test\"
    
    ChDir strBasePath
    
    strFullPath = Application.GetOpenFilename("Excel Workbooks (*.xls*),*.xls*", 1, "Choose file", "Select", False)
    
    If strFullPath <> "False" Then
    
        strFileName = Mid(strFullPath, InStrRev(strFullPath, "\") + 1)
        
        strFilePath = Replace(strFullPath, strFileName, "")
        
       ' MsgBox "File selected:" & vbTab & strFileName, vbExclamation, "Congatulations!"
        
    End If


    Range("Q7").FormulaR1C1 = _
        "=VLOOKUP(RC[-16],'[strFilePath]08-May-2018'!C2,1,)"

What happens is I get a popup asking me to select the files, which is brilliant. But then when I go to reference it further down the code in the lookup I get another popup appear asking me to select the file again as if its an unknown reference.

Are you able to help me out please

Thanks,
Cat

 

[Norie]

Try this.

  Range("Q7").FormulaR1C1 = _
        "=VLOOKUP(RC[-16],'[" & strFilePath & "]08-May-2018'!C2,1,)"

PS That might not work either as strFilePath only holds the filename, not the path. You need the full (hint) path if the workbook is closed.

 

[Cat129]

Sorry

Was being stupid, forgot to concatenate it.

However this is pulling in the drive name F:\ not the server name

Is there a way to get it to return the server name instead of the drive?

The issue is each site has different servers mapped differently, so my F is different to the next persons

Thanks,
Cat

 

[Norie]

Cat

Try this to get the UNC for the mapped drive(s).

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 Is Nothing objDrv Then
        GetUNC = Replace(strMappedDrive, strDrive, objDrv.sharename)
    End If

    Set objFso = Nothing 
    
End Function

 

[Cat129]

Hey, thanks for the code,

I'm getting a red line on

'If Not Is Nothing objDrv Then'

Cat

Try this to get the UNC for the mapped drive(s).

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 Is Nothing objDrv Then
        GetUNC = Replace(strMappedDrive, strDrive, objDrv.sharename)
    End If

    Set objFso = Nothing 
    
End Function