0

Background:

I have written some VB to search through a directory for a file (the date of the file will be variable, hence the wildcard) the code will then open the file, copy a list of names to another spreadsheet, store the names in an array so I can return their relative position in the other spreadsheet - pos, I need to do this as the next part of the code writes a link to that spreadsheet.

My filename search code is as follows:

Dim lcFound As String
lcFound = Dir("C:\Users\KDelaney\Desktop\*XYZ Select XYZ List.xlsm")

    If lcFound <> "" Then
        Workbooks.Open Filename:=lcFound
    End If

Not going to add all the code as I don't think it's relative to the question. The writing a link formula to each cell in a f loop is as below, the below code was fine before I was using the file search, as I was testing it with the full file name and path:

  Cells(f, 4).Formula = "=" & "'" & lcPath & "[" & lcFile & "]" & "Template" & "'!" & "AB" & pos

Question:

Basically the question is what would be the best way to convert C:\Users\KDelaney\Desktop\*XYZ Select XYZ List.xlsm into lcPath and lcFile, or is there a way round separating the strings? (Have briefly tested it unseparated but it seems like the square brackets around the filename is required).

I'm guessing the best course of action would be using InStr function? to return the position of the end of the file path? e.g. I know the filepath will always end with "Desktop\" so if I use InStr to return that position I will then know the filepath is between 1 and the returned value of InStr. However this is where I have stalled, not sure where to go from here, in terms of separating the two strings. I also know the start of the file name will always begin with a number and end with the file extension. I am probably making a meal of this problem and someone will come back with a very simple solution (hopefully)

Any help would be appreciated. Apologies if the question was too in depth just wanted to give you the full picture. Thanks.

KDE
  • 115
  • 8

1 Answers1

0

Sorry for:

a) Not researching enough if the question has already been asked/answered

b) answering my own question

Link contains two good answers, a function and using file system object. How to extract file name from path?

I am going to persevere using the split function for my own knowledge and if things don't go well I will resort to one of the other two methods in the link above.

Further edit: haven't cleaned the code up yet, but I have realised I was massively overcomplicating the problem, all it required was a bit of messy string manipulation. If anyone comes across a similar problem:

 Dim WrdArray() As String
 Dim lcFound As String
 strPath = "C:\Users\KDelaney\Desktop\*XYZ Select XYZ List.xlsm"
 newstrL = InStrRev(strPath, "\")
 newstrL = newstrL - 1
 strlen = Len(strPath)
 lnPath = strlen - newstrL
 MsgBox Left(strPath, lnPath)

Definitely not the best way of doing it, have realized there are 'multiple ways to skin a cat' in this situation.

Link below for lots of info on string manipulation:

https://www.excel-easy.com/vba/string-manipulation.html

KDE
  • 115
  • 8
  • Well I can't add an answer but the quicket way is to use strreverse in conjection with split e.g. (myArray = Split(StrReverse("C:\Users\KDelaney\Desktop\*XYZ Select XYZ List.xlsm"), "\", Limit:=2) GetPathAndName = Array(StrReverse(myArray(0)), StrReverse(myArray(1))) – freeflow Mar 12 '21 at 12:33
  • I thought I solved it with string manipulation, however it appears you can't do that when using dir. Tried your bit of code and it works somewhat: it reads the letters in reverse up to 'Kdelaney' - but as I said the whole string is completely backwards! – KDE Mar 12 '21 at 12:38
  • It works fine but as a snip[pet from a function may be confusing. Here is the full function which returns an array of 2 items. Item(0) is the file name, Item(1) is the path. I put ### where you should split the lines. 'Public Function GetPathAndName(ByVal ipPathAndName As String) As Variant### Dim myArray As Variant myArray = Split(StrReverse(ipPathAndName), "\", Limit:=2)### GetPathAndName = Array(StrReverse(myArray(0)), StrReverse(myArray(1)))### End Function' – freeflow Mar 12 '21 at 15:16
  • Learned so much about string manipulation, was going down a rabbit hole thinking string manipulation doesn't work when using Dir function as I was looking for the '\' in lcFound and returning nothing, it turns out lcFound only has the file name stored, file path is in the background somewhere so I was searching for a '\' and there wasn't one, so filename was just lcFound and I used for wb.Path for the path. Thanks for the help freeflow, (you're always popping up on my questions!) – KDE Mar 15 '21 at 12:17