Shorten value contained in a cell

[Alejandro Gombao]

Hello everyone,

I'm trying to elaborate an Index for a documentation repository at my company.

First of all, I obtained a list of all files contained, and pasted in an Excel the network links to each of them.
Second, I sorted the list so all files would be just below the directory that contains them.
Then I elaborated a series of macros for the following:

- Spacing the start of the lines (aka, moving the values to the right, depending of how "deep" each file is in the repository)
- Transforming each network link into an "interactive" hyperlink that allows the user to open files directly from the Index
- Identifying which of the links are files and which are links to directories, and colouring the rows that contain the directories

Now, I would like to shorten those links so they only show "the last part of the link" which would be, obviosuly, the file/directory's name, to do so I tried to use "inStrRev" to find the position of the last "\" in the name, then substract that position to the total length of the link and then extract (with "Right") the remaining number of characters from the link.

Sadly it isn't working as intended, it shortened "random" numbers of characters from the original links.
After some debugging, I have found the "return" value of InStrRev is not as expected, and by now I'm lost on how to do this.

Here's my code. It's probably painful to watch for some of you since I started coding VBA yesterday (literally)... Hope you can help me on this

Sub AcortarNombres()

Dim Posit1 As Integer, Posit2 As Integer

Dim Nombre As String
Dim aux As String

For x = 1 To 7676
For y = 1 To 11

Set curCell2 = Cells(x, y)

If IsEmpty(curCell2) = True Then
Else:

Posit1 = 0
Posit2 = 0

aux = Right(curCell2.Value, 100)

Posit1 = InStrRev(aux, "\")

Posit2 = Len(curCell2.Value) - Posit1

Nombre = Right(curCell2.Value, Posit2)

curCell.Value = Nombre


End If


Next y
Next x


End Sub

 

[Eric W]

It looks like your code should work, but there could be some odd interaction since you're doing the InStrRev on the aux variable, and the Right on the curCell2 value. You could try something like this:

    Posit1 = InStrRev(aux, "\")
    Nombre = Mid(aux, Posit1 + 1)

 

[Alejandro Gombao]

Hello Eric, thanks for your reply.

First of all, I will post an example of some values I am working with:

O\4.2.3.2.2.2.3.1.1.1.1 AEROGENERADORES_SOTILLO.dbf

Q:\VDR Spark\Documents - P. Spark\4 VILLAR MIR SLU y FILIALES\4.2 Generación - Generation\4.2.3 PE Sotillo - Sociedad Eolica Aragonesa 53 SL\4.2.3.2 Técnico-operativo\4.2.3.2.2 informes seguimiento ambiental\4.2.3.2.2.2 2º Año- 2021\4.2.3.2.2.2.3 3º Informe\4.2.3.2.2.2.3.1 PE Sotillo\4.2.3.2.2.2.3.1.1 Informe_PE_SOTILLO_CUATRIMESTRE3_AÑO2\4.2.3.2.2.2.3.1.1.1 SHAPES SOTILLO\4.2.3.2.2.2.3.1.1.1.10 MORTALIDAD__SOTILLO.shp

Q:\VDR Spark\Documents - P. Spark\4 VILLAR MIR SLU y FILIALES\4.2 Generación - Generation\4.2.3 PE Sotillo - Sociedad Eolica Aragonesa 53 SL\4.2.3.2 Técnico-operativo\4.2.3.2.2 informes seguimiento ambiental\4.2.3.2.2.2 2º Año- 2021\4.2.3.2.2.2.3 3º Informe\4.2.3.2.2.2.3.1 PE Sotillo\4.2.3.2.2.2.3.1.1 Informe_PE_SOTILLO_CUATRIMESTRE3_AÑO2\4.2.3.2.2.2.3.1.1.1 SHAPES SOTILLO\4.2.3.2.2.2.3.1.1.1.11 MORTALIDAD__SOTILLO.shx

Q:\VDR Spark\Documents - P. Spark\4 VILLAR MIR SLU y FILIALES\4.2 Generación - Generation\4.2.3 PE Sotillo - Sociedad Eolica Aragonesa 53 SL\4.2.3.2 Técnico-operativo\4.2.3.2.2 informes seguimiento ambiental\4.2.3.2.2.2 2º Año- 2021\4.2.3.2.2.2.3 3º Informe\4.2.3.2.2.2.3.1 PE Sotillo\4.2.3.2.2.2.3.1.1 Informe_PE_SOTILLO_CUATRIMESTRE3_AÑO2\4.2.3.2.2.2.3.1.1.1 SHAPES SOTILLO\4.2.3.2.2.2.3.1.1.1.2 AEROGENERADORES_SOTILLO.sbn

Q:\VDR Spark\Documents - P. Spark\4 VILLAR MIR SLU y FILIALES\4.2 Generación - Generation\4.2.3 PE Sotillo - Sociedad Eolica Aragonesa 53 SL\4.2.3.2 Técnico-operativo\4.2.3.2.2 informes seguimiento ambiental\4.2.3.2.2.2 2º Año- 2021\4.2.3.2.2.2.3 3º Informe\4.2.3.2.2.2.3.1 PE Sotillo\4.2.3.2.2.2.3.1.1 Informe_PE_SOTILLO_CUATRIMESTRE3_AÑO2\4.2.3.2.2.2.3.1.1.1 SHAPES SOTILLO\4.2.3.2.2.2.3.1.1.1.3 AEROGENERADORES_SOTILLO.sbx

Q:\VDR Spark\Documents - P. Spark\4 VILLAR MIR SLU y FILIALES\4.2 Generación - Generation\4.2.3 PE Sotillo - Sociedad Eolica Aragonesa 53 SL\4.2.3.2 Técnico-operativo\4.2.3.2.2 informes seguimiento ambiental\4.2.3.2.2.2 2º Año- 2021\4.2.3.2.2.2.3 3º Informe\4.2.3.2.2.2.3.1 PE Sotillo\4.2.3.2.2.2.3.1.1 Informe_PE_SOTILLO_CUATRIMESTRE3_AÑO2\4.2.3.2.2.2.3.1.1.1 SHAPES SOTILLO\4.2.3.2.2.2.3.1.1.1.4 AEROGENERADORES_SOTILLO.shp

As you can see, they are pretty long routes.

I have tried your suggestion, but the result was the same.
If I look at the value of variables in every loop, the next happens:

First Loop:

Posit1 = 2
Posit2 = 49
Len(curCell2.Value) = 51 - which means all is good up to this -
Nombre = "dbf" - with your code change, as original it would be "4.2.3.2.2.2.3.1.1.1.1 AEROGENERADORES_SOTILLO.dbf" wich is correct
aux = O\4.2.3.2.2.2.3.1.1.1.1 AEROGENERADORES_SOTILLO.dbf

Second Loop: (now the thing starts going nuts)

Posit1 = 54 - incorrect, it shoud be 375
Posit2 = 368 - obviously incorrect
Len(curCell2.Value) = 422 - This is correct
Nombre = "CUATRIMESTRE3_AÑO2\4.2.3.2.2.2.3.1.1.1 SHAPES SOTILLO\4.2.3.2.2.2.3.1.1.1.10 MORTALIDAD__SOTILLO.shp" - which makes no sense
aux = "O\4.2.3.2.2.2.3.1.1.1.1 AEROGENERADORES_SOTILLO.dbf" - close to correct, but makes no sense, since "aux" should contain 100 characters as commanded on "aux = Right(curCell2.Value, 100)" line on the code


4th loop is even crazier:

Nombre = TRIMESTRE3_AÑO2\4.2.3.2.2.2.3.1.1.1 SHAPES SOTILLO\4.2.3.2.2.2.3.1.1.1.2 AEROGENERADORES_SOTILLO.sbn
aux = CUATRIMESTRE3_AÑO2\4.2.3.2.2.2.3.1.1.1 SHAPES SOTILLO\4.2.3.2.2.2.3.1.1.1.11 MORTALIDAD__SOTILLO.shx

which are lines from DIFFERENT ROWS

I hope this helps you help me on this...

Thanks!

 

[Alejandro Gombao]

It looks like your code should work, but there could be some odd interaction since you're doing the InStrRev on the aux variable, and the Right on the curCell2 value. You could try something like this:

    Posit1 = InStrRev(aux, "\")
    Nombre = Mid(aux, Posit1 + 1)

Hello Again,

I've just discovered what the problem was.

aux = Right(curCell2.Value, 100)

Posit1 = InStrRev(aux, "\")

Posit2 = Len(aux) - Posit1

Nombre = Right(aux, Posit2)

curCell.Value = Nombre

The problem was in "Posit2" I was substracting the Posit1 to the full length of the link, but Posit1 value was the position starting from the 100th char from the right.

Thank you for your help!