Macro to Select file location for lookups

Cat129

Board Regular
Joined
Oct 20, 2012
Messages
96
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
 
You can use Application.GetOpenFilename to allow the user to browse for and select a file.
Code:
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?
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
Are you talking about replacing the filename in the code or on the worksheet?
 
Upvote 0
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
Try this.
Code:
  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.
 
Upvote 0
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
 
Upvote 0
Cat

Try this to get the UNC for the mapped drive(s).
Code:
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
 
Upvote 0
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).
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top