Copy And Paste 66 Data Using Google Chrome with Macro

[Matrix2021]

Hello Forum.

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

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


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...


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: https://drive.google.com/file/d/18uS...7vr56YdvG/view

Order of links in excel file

[cactusdata]

For a start, this is superfluous:

Dim LADATE As Date
LADATE = Format(CDate(Now), "dd/MM/yyyy")

as it can be reduced to:

Dim LADATE As Date
LADATE = Date

[Matrix2021]

Cactusdata. Thanks for your reply.

Yes, I am sorry... The macro really is unnecessary in having more over as I am requesting.

And I am not sure if you got the opportunity to view the problem for further analysis. What do you want me to do?
How can I overcome this problem, if it falls under your expertise.

Thanks in advance

[cactusdata]

Sorry, busy with real work - those lines just caught my eyes.

[Matrix2021]

That's funny Lol. No problem.

But hey. My work is real work too... I just pulling our my hair, very busy but going no where to find a way to make this work...

Running, running working, working and getting no where. How can you help a person like that?

[NeoPa]

Matrix2021:
But hey. My work is real work too.

Indeed. Don't forget that not all members / experts are native English speakers so perhaps I would suggest here CactusData was simply explaining why he had little time to help you further at this stage. I suspect there was no intention to sound disparaging. That's just a subtlety of the language.

Sometimes it can be helpful to pop something quite limited into the mix. It may not answer the whole question - especially in a case like this where the whole is quite unclear and seems pretty large. I'm sure your lack of clarity also comes from trying to express it in English too of course.

I get that you want data transferred from some web link into your database. Are you expecting the code to do the copying and the pasting for you automatically? If so then you haven't done nearly enough work yet to determine how to go about that (I'll assume not for now but if so then it involves page scraping which is massively non-trivial as well as requiring very clear and precise understanding of the actual page you're working with. I wouldn't even go to that trouble for my own work except when I have to. Not easy). If you plan to copy an individual item of data at a time and then get the code to paste it into a specific cell then we can help with that. You'd still need to indicate what you actually have to start with and what steps you envisage going through.

I can see you've actually gone to a lot of trouble to do this as well as you can so my next comment shouldn't be seen as critical, but asking people to work out what you should be saying in your question by reading some code is not generally appreciated for two reasons :

1. It puts the work you're responsible for on their shoulders - passing the buck as it were.
2. If your code were working as required you wouldn't be asking the question - therefore your code doesn't give a good indication of what you want.

As I say, these are general points as I do appreciate that you've gone to a lot of trouble to do this as well as you can. Unfortunately, that still leaves us with a question that has too little to enable us to help you easily.

[Matrix2021]

Hello NeoPa,

I am sorry. You are probably right. I mess-up and if there are no solution, it may make sense the moderators ended my post.

I have little of excel knowledge. reading different blog posts, watching YouTube and trying to make some sense in what I am doing. I have failed very badly. I am not sure on how to proceed?

The real problem is. How to create a macro to open google web browser, by opening the link from Cell N2 and pasting the data in Cell B2 and C2? looping downwards to the next link and the other Cells?

I am sorry NeoPa.

My native language is English. It's probably my dialect language. I don't know.

My concern was this. How can I adjust my request for others to understand?

What steps I can take to move forward, for everyone to understand my post? If they are willing to help me...

What should I do?

Thanks in advance

[twinnyfo]

Matrix2021,

It appears to me that what you are trying to do is "data mine" from web pages. There are web pages that have market/stock info, and you want to be able to extract specific and particular data from the web page, using VBA and save that specific data to Excel. Is this correct?

First, I will say that I am not certain that this is impossible. There may be some very extremely smart folks on this forum who could figure out how to extract the data from the web page--but they probably won't be on the VBA forum, but rather some HTML or web page forums we have here on Bytes. Trying to trick VBA into using keystrokes and copying and pasting data that way is almost random when you have modern web pages. You need a way to dig beneath the surface and get to the data behind that web page. That is not an easy thing, as far as I know. But, I am sure there are those who might find it easy. Once you extract that data, then you want to use VBA to save the data to Excel. That's something folks on the VBA forum can easily assist with--if you get there.

Second, and I think this is more important, it appears that you want to automate this--either because you access this particular page frequently, OR because you have numerous pages you extract data from AND you access those pages frequently. This makes sense, because I am extremely lazy myself, and any time I can get my PC to do something for me, I am all for it. However, the extraction of the data you are trying to do appears to be data extraction that is performed far more easily using tools that many stock/trading sites/services already provide.

Why is this second point so important? Well, for one thing, if you are simply exploring the possibility of doing this for your own technical playground (I've done such things before), then that's fine. It can be very useful to explore things you don't know in order to expand your toolkit. I've done that for years. But, if, for example, you are trying to do this to manage a major financial portfolio, then I would simply recommend using the tools your financial agency provides, which will probably be much easier to use.

Another example of this comes in the form of people posting questions on this forum of the sort: "I want to create a banking database to manage my income, outgo and categorize my expenses" I tell them to go to a bank. YES -- It is possible to build a banking database in MS Access. But, why? Banks have those tools built in. Or, people will want to build a church management database. I recommend they do some research and buy one. The level of development required to build such a tool, while possible, far outweighs any benefit of saving a few dollars by doing it yourself.

Now, I am not trying to discourage you here. I would just like you to look at the project you are intending to build--and it appears your experience is more at the novice level than the expert level--and determine the benefits you might really gain over simply using tools available to you through other means.

I am sure there are experts here who are willing to flight follow this thread if it piques their interest and they have some expertise here. I have personally spent many weeks with one OP working through issues on a project that interested me and have worked with folks around the world for months on their projects. But, your request is a project, not a problem. A lot will be required and expected from both sides in this.

And.... Keep in mind, that this is just my two cents. We are here to hepp. All of us here want to see you succeed. We also want to keep you informed about bigger picture issues when we can.

Hope this hepps!

[Rabbit]

You can automate internet explorer by creating and interacting with the internet explorer object.

But why go through that trouble. The image you attached shows a link to download the data. It would be easier to automate a url fetch to download the data and process it.

[Niheel]

Can't you query the web using Power Query and Excel's Get and Transform Feature?
Can't tell if you are trying to import into an access db or if you are using vba to get the data and don't care if it's updated in the excel sheet itself.

I am guessing you want the latest prices on the foreign bonds? update in your excel sheet then --> push out to your VBA app?

Connect to a web page

• Use Excel's Get & Transform experience to connect to a web page and import information from different tables.
  
• Click the Data tab, then New Query > From Other Sources > From Web.
  Note: If you don't see the New Query button, click the Data tab, then click From Web.
  
• In the From Web dialog box, enter a web page URL, then click OK.
• Power Query > From Web > Input URL dialog
  
• In this case, we're using: http://en.wikipedia.org/wiki/UEFA_Eu...l_Championship.
  
• If the web page requires user credentials:
  
• In the Access Web dialog box, click a credentials option, and provide authentication values.
  
• Click Save.
  
• Click OK.
  
• Power Query will analyze the web page, and load the Navigator pane in Table View.
  
• If you know which table you want to connect to, then choose it from the list. For this example, we chose the Results table.
• Power Query > From Web > Navigator Table View
  
• Otherwise, you can switch to the Web View and pick the appropriate table manually. In this case, we've selected the Results table.
• Power Query > From Web > Navigator > Web View
  
• Click Load, and Power Query will load the web data you selected into Excel.

Source:
Import data from external data sources (Power Query)
Import data from a Web page in Excel

[NeoPa]

Matrix2021:
I am sorry. You are probably right. I mess-up and if there are no solution, it may make sense the moderators ended my post.

I think maybe you're being a bit too hard on yourself there. I was trying to help you understand without being too critical. That was for a very sound reason - even though your question wasn't perfect, it clearly wasn't from lack of effort. Give yourself a break and just learn from what some of these great experts are sharing with you.

BTW Apologies for suggesting English wasn't your native language. I saw words within the code which seemed to me very much to be French. My bad if I got that wrong.

I would bring your attention to two points - made variously by these posters :

1. Mainly TwinnyFo explained the difference between what you're looking for here and what we would consider to be a problem that's appropriate for a technical question. This is more of a project than a problem.
2. A point all three have made - and one of them is the actual owner of the site itself answering a technical question here so count yourself very blessed - which is that there already exist resources out there which are likely to be far more appropriate for you to focus your energies on than trying to re-create your own bespoke software. Niheel has even gone as far as to do some of the heavy-lifting for you.

So, no, you haven't required any chastisement, and yes, I believe & hope there is enough help here to get you a considerable way towards your intended destination.

Good luck.

[Matrix2021]

Hello Rabbit "You can automate internet explorer by creating and interacting with the internet explorer object."

Thanks for your reply. the problem I am facing with internet explorer is this. The site is broken very badly. (Please see photo) apparently it is not compatible.

Hope I answered the question rightly.

Thanks for the feed back .

[Matrix2021]

Hi Niheel

Thanks for your reply. You have given me a lot of food, I am not familiar of.

If you are comfortable, would like to start off by saying your quote: "I am guessing you want the latest prices on the foreign bonds? update in your excel sheet then --> push out to your VBA app?" Yes. You are correct.

However, I am trouble . I am not sure if you can help me out.
I have given power Query some thought. What's bothering me. I have 66 web link. Each link is contributed make one table in column B & C. beginning in row two.

How can this be done, with so much of links with out breaking query? Will read the article as you recommended...

Thanks in advance

[Matrix2021]

Hello NeoPa.

I am graceful for introducing me to the experts on this post...

Our misunderstandin g have brought opportunity, which I am most graceful for the comments.

What's next? To get my hands dirty again and see if Niheel recommendations , can solved my problems. Otherwise, it look like there are no other alternative.

Thanks for your feedback. Is there any other thing that I need to know of?

Thanks again

Cheers

[NeoPa]

It would appear you are doing what is possible and exploring the valid options.

Just remember that a project like this is never going to be simple. You've decided you want to climb Everest. Don't be surprised if you find it hard going ;-)