Greetings for everyone!
Introduction.
At the work, we use an electronic document management web application (we can name it for example as "webdocs") that allows us to search the documents by their specific number.
The "webdocs" have an option to download an excel file to show the list of expired and coming outer/internal documents.
That excel file has the column which consists № character + document number + line break + date of entry.
The webdocs.
When I search a document, the URL looks like the following: https://webdocs.com/#!/cancelar/incoming/document_list_organization?page=1&document_recipient_reg_number=12345678&boss=-1&from_date=01.01.2022&to_date=12.31.2022&year=2022
So the URL consists 3 main blocks, the second one is what I am looking for:
The problem
I wrote the VBA code that adds the additional column and it pasts the URL into each cell of the table of data.
The main point is to replace the second block of URL with the value of the column "B", that is why I have added a formula that ignores "№" character and takes the values until the line break (character 10).
Dim zRange, zCells As Range
Set zRange = .Range("I3", .Range("I3").End(xlDown)).Offset(0, 5)
.Range("N2").Value = "Find the document"
For Each zCells In zRange
.Hyperlinks.Add Anchor:=zCells, _
Address:="https://webdocs.com/#!/cancelar/incoming/document_list_organization?page=1&document_recipient_reg_number="
& zCells.Formula = "RIGHT(LEFT(" & "B" & zCells.Row & ",FIND(CHAR(10)," & "B" & zCells.Row & ")-1), LEN(LEFT(" & "B" & zCells.Row & ",FIND(CHAR(10)," & "B" & zCells.Row & ")-1))-2)"
& "&boss=-1&from_date=01.01.2022&to_date=12.31.2022&year=2022", _
ScreenTip:="Open the document", _
TextToDisplay:="Open the document"
Next zCells
The code interprets the ".Formula" as text and when I opened the hyperlink, I saw the formula on the URL's second block but not the value from the cells of the column "B". The code does not work as it should.
The question
What is the way to fix the problem?
Hint:
Sub TestA()
Const Address As String = "https://webdocs.com/#!/cancelar/incoming/document_list_organization?page=1&document_recipient_reg_number=12345678&boss=-1&from_date=01.01.2022&to_date=12.31.2022&year=2022"
MsgBox "Reg #: " & Split(Split(Address, "=")(2), "&")(0)
End Sub
Likewise:
Sub TestB()
Dim DocID As String
DocID = Range("B" & zCells.Row).Text
MsgBox "Reg #: " & Split(Split(DocID, "№")(1), Chr(10))(0)
End Sub