Hello Forum.[xpost][/xpost]
I am wondering, how to modify this code below to suit me needs.
Sub EXPORTONGLETS()
'VALID DECLARATION
Dim NOMFEUILLE As String 'NAME VARIABLE FOR THE HOME TAB
Dim NBLIGNES As Long ' VARIBLE NUMBER OF LINES PROVIDED IN NEWS
Dim LADATE As Date ' EXPORT DATE INDICATION
Dim t$
With Worksheets("News")
NBLIGNES = .Range("A" & .Rows.Count).End(xlUp).Row
End With
LADATE = Format(CDate(Now), "dd/MM/yyyy")
'WE LAUNCH A LOOP ON ALL THE LINES OF THE NEWS TAB FROM LINE 2 TO THE END
For i = 3 To NBLIGNES
t = GetHash(Worksheets("News").Range("B" & i).Value) 'GetHash
'RECOVER THE NAME OF THE TAB INDICATED IN COLUMN (A) OF NEWS
NOMFEUILLE = Worksheets("News").Range("A" & i)
If IsError(Application.Match(t, Worksheets(NOMFEUILLE).Columns(3), 0)) Then 'check Hash
'WITH THE DESTINATION SHEET, WE INSERT A LINE IN LINE 3 THEN WE INFORM
With Sheets(NOMFEUILLE)
.Rows("2:2").Insert Shift:=xlDown
.Range("A3").Value = LADATE
'Worksheets(NOMFEUILLE).Range("B3").Value = Worksheets("News").Range("B" & i).Value
Worksheets("News").Range("B" & i).Copy .Range("B3")
.Range("C3").Value = t
.Rows("3:3").EntireRow.AutoFit
End With
End If
'GO TO THE NEXT NEWS VALUE
Next i
With Sheets("News").Activate
End With
End Sub
Function GetHash(ByVal txt$) As String
Dim oUTF8, oMD5, abyt, i&, k&, hi&, lo&, chHi$, chLo$
Set oUTF8 = CreateObject("System.Text.UTF8Encoding")
Set oMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
abyt = oMD5.ComputeHash_2(oUTF8.GetBytes_4(txt$))
For i = 1 To LenB(abyt)
k = AscB(MidB(abyt, i, 1))
lo = k Mod 16: hi = (k - lo) / 16
If hi > 9 Then chHi = Chr(Asc("a") + hi - 10) Else chHi = Chr(Asc("0") + hi)
If lo > 9 Then chLo = Chr(Asc("a") + lo - 10) Else chLo = Chr(Asc("0") + lo)
GetHash = GetHash & chHi & chLo
Next
Set oUTF8 = Nothing: Set oMD5 = Nothing
End Function
Display More
Below. I have a list of 66 web link on worksheet tab “Bond”. Beginning on Cell N2. (See sample worksheet link below).
Each web link data is copied from website and is paste into a table, beginning in Cell B2 & B3. In other words. Each link represents two cells. “Date & price” and continue downwards. (see example).
Example: Just to highlight a few…
Column A | Column B (Date) | Column c (price) | Column N (Web Link) | ||
---|---|---|---|---|---|
Argentina | 1/3/2021 | 57.05 | https://www.investing.com/rates-bonds/ar…historical-data | ||
Australia | 1/3/2021 | 68.235 | https://www.investing.com/rates-bonds/au…historical-data | ||
Austria | 1/3/2021 | 875.69 | https://www.investing.com/rates-bonds/au…historical-data |
The problem is, how can I copy and paste data?
Below you can view the photos of one web link example of the two steps process to copy the data.
Once you understand the first steps. The other links follow the same process… Please view photos below…
Step #1. To change daily data to weekly data. Please see photo highlighted in red...https://imgur.com/NqGoyJv
Step #2 The photo is highlighted in black. Data is copied and paste: Date (Jan 03, 2021) to paste in CellB2 & Price (57.05) paste into Cell C2.
I am not sure if you can help me. How can you all point me to the right direction?
Thanks in advance.
Excel File Here: link removed
Order of links in excel file