Using Vlookup on other Workbook whichs path is stored in a Cell

Lars_2020 · 26. August 2020

Good Day

Im Trying to do Vlookup on a closed Workbook whichs path is stored in a Cell.

For that im writing a Function which opens the Workbook by the path given, and then does the VLookup on a predefined range in the newly opened Workbook. Somehow my function doesnt work and i cant find out why.

Heres the function:

PosType is the Value to lookup in VLookup

Function getPosPosType As String, FilePath As String As String

Dim FileName As String

FileName = GetFilenameFromPathFilePath

Workbooks.Open FilePath

getPos = Application.VLookupPosType, WorkbooksFileName.Worksheets"Sheet0".Range"A1:B50".Value, 2, False

End Function

GetFilenameFromPath returns only the Filename "example.xlsx" like so:

Function GetFilenameFromPathByVal strPath As String As String

' Returns the rightmost characters of a string upto but not including the rightmost '\'

' e.g. 'c:\winnt\win.ini' returns 'win.ini'

If Right$strPath, 1 <> "\" And LenstrPath > 0 Then

GetFilenameFromPath = GetFilenameFromPathLeft$strPath, LenstrPath - 1 + Right$strPath, 1

End If

End Function

I have been trying to solve this for hours now but i just cant seem to find whats wrong with my code. Thanks for your help!

Jason (Office Engine

Editing a Sway using the website is not supported on mobile phones. If you want to edit on the phone, you need to download the Sway mobile app (of which there is currently only an iOS version). Otherwise, you'll need to continue editing on a tablet or
desktop.

Hope this helps!

Jason

Chcristoph_252

Many thanks for the suggestions. They did not help, though. I deleted the template Book.xltx in the start-up folder and made sure there is none anywhere on the C drive. After the close / open /close / open procedure I am back to A1 style.

There is no PERSONAL.XLSB on my C drive. I checked also the spelling in lower case. But when I open an existing workbook directly in the file explorer, the I do get actually two books: the one I wanted and an empty Book1.xlsx. Is this the other workbook
your mention? Is it associated with the root cause? I would be grateful for a sound solution.

For the time being I implemented a Aut o_O

pen VBA procedure, which is setting the R1C1 style at opening of the workbook. But that is a bit clumsy and I would appreciate a more professional solution.

DaveM121

Hi J

Here is the formula that would count the number of ; + 1 in a cell, which if all the persons were separated by a ; would work

(Pleas Note, you will need to replace A1 with the appropriate cell reference)

=LEN(A1)-LEN(SUBSTITUTE(A1,";","")) + 1

However from your screenshot, sometimes a ; is used and other times a , is used to separate the persons names, and in that case, this is the formula you need

=LEN(A1)-LEN(SUBSTITUTE(A1,";","")) + LEN(A1)-LEN(SUBSTITUTE(A1,",","")) + 1

Andreas Killer

Weil die Dateiendung fehlt, das Workbooks Objekt braucht den kompletten Namen.

Der Code unten könnte hilfreich sein. Und Du solltest zuerst das Objekt auf NOTHING prüfen, sonst gibt's den nächsten Fehler in Deinem Code bei
.Row

Andreas.

Function GetWorkBook(ByVal WorkBookName As String) As Workbook

'Return the workbook that name begins with or is WorkBookName, Nothing if not open

Dim fso As Object 'FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")

'Path given?

If Len(fso.GetParentFolderName(WorkBookName)) > 0 Then

'Compare the full path of each open workbook

For Each GetWorkBook In Workbooks

If StrComp(GetWorkBook.FullName, WorkBookName, vbTextCompare) = 0 Then

Exit Function

End If

Next

ElseIf InStrRev(WorkBookName, ".") > 0 Then

'We must exact match if an extension is given

On Error GoTo ExitPoint

Set GetWorkBook = Workbooks(WorkBookName)

Else

'Without an extension it can be a new file too

On Error GoTo SearchIt

Set GetWorkBook = Workbooks(WorkBookName)

Exit Function

SearchIt:

On Error GoTo ExitPoint

For Each GetWorkBook In Workbooks

If StrComp(fso.GetBaseName(GetWorkBook.Name), WorkBookName, vbTextCompare) = 0 Then

Exit Function

End If

Next

End If

ExitPoint:

End Function

Ge.H.a

In my mind not very helpful.

Would have preferred a light-gray outline of the table (like in past versions), which does not get printed. In that way one could easily see where the table is located and where the different cells are. It is still possbile today, when activating the "Textbegrenzungen",
but then you get also a grey line on every text line of the document, which does not at all contribute to a clear overview of the document.

Thx anyhow for your answer.

Hi,

willkommen im Windows Forum!

Using Vlookup on other Workbook whichs path is stored in a Cell - Similar Threads - Using Vlookup other	Forum	Datum
which generic cialis is best is there a cheaper alternative to cialis which generic cialis is best is there a cheaper alternative to cialis: . . Is there a cheaper alternative to cialis ==> https://tinyurl.com/Cialis365 . . . . . comprar cialis en linea which generic cialis is best is there a cheaper alternative to cialis...	Microsoft Windows	13. Mai 2025
other benefits of tadalafil which is better cialis or tadalafil other benefits of tadalafil which is better cialis or tadalafil: . . Tadalafil prezzo -–> https://tinyurl.com/CialisTrue . . . . . how much is tadalafil tadalafil prezzo cialis prezzo online cialis prezzo online how much is tadalafil tadalafil...	Microsoft Windows	12. Mai 2025
Which interface is convenient? Which interface is convenient?: I often notice that the interface of some casinos is too complicated and it takes time to figure it out. Which platforms are simple and user-friendly?	Microsoft Windows	15. November 2024
How can I switch which gpu windows is using for different games for example Minecraft is... How can I switch which gpu windows is using for different games for example Minecraft is...: So I have the problem that my Laptop lacks performance heavily in certain games like Minecraft because it uses the internal Intel GPU instead of my NVIDIA GPU. I tried to change this in the...	Apps	16. September 2024
How to condition formula on other cell content How to condition formula on other cell content: Hi, I am not sure how else to phrase my request and hope someone can help me. I created an expense list for my project and am constantly adding new positions. The expenses are paid by several...	Microsoft Office	13. August 2020
Refer to changing workbook names (which include a date range) Refer to changing workbook names (which include a date range): Hi, I need to copy/paste data to an OverviewWorkbook from several workbooks every month. The file name of every workbook used includes a date, eg something like...	Microsoft Office	26. April 2019
vlookup vlookup: Hi , I am trying to recreate a VLOOKUP example from your on-line tutorial. iT WILL NOT WORK. Is there a specific cell format necessary for vlookup?	Microsoft Office	13. April 2018

Using Vlookup on other Workbook whichs path is stored in a Cell

Using Vlookup on other Workbook whichs path is stored in a Cell

Edit Sway on mobile device

excel reference style drops back to A1

Counting persons in one cell

Ribbon Dropdown zur Laufzeit mit Daten von anderem Workbook füllen?

Word 365

User Advert

Using Vlookup on other Workbook whichs path is stored in a Cell - Microsoft Office

Using Vlookup on other Workbook whichs path is stored in a Cell - Similar Threads - Using Vlookup other

which generic cialis is best is there a cheaper alternative to cialis

other benefits of tadalafil which is better cialis or tadalafil

Which interface is convenient?

How can I switch which gpu windows is using for different games for example Minecraft is...

How to condition formula on other cell content

Refer to changing workbook names (which include a date range)

vlookup

Using Vlookup on other Workbook whichs path is stored in a Cell solved