473,372 Members | 1,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Copy And Paste 66 Data Using Google Chrome with Macro

13 Byte
Hello Forum.

I am wondering, how to modify this code below to suit me needs.

Expand|Select|Wrap|Line Numbers
  6.  Dim t$
  8.  With Worksheets("News")
  9.  NBLIGNES = .Range("A" & .Rows.Count).End(xlUp).Row
  10.  End With
  12.  LADATE = Format(CDate(Now), "dd/MM/yyyy")
  15.  For i = 3 To NBLIGNES
  16.  t = GetHash(Worksheets("News").Range("B" & i).Value) 'GetHash
  18.  NOMFEUILLE = Worksheets("News").Range("A" & i)
  19.  If IsError(Application.Match(t, Worksheets(NOMFEUILLE).Columns(3), 0)) Then 'check Hash
  21.  With Sheets(NOMFEUILLE)
  22.  .Rows("2:2").Insert Shift:=xlDown
  23.  .Range("A3").Value = LADATE
  24.  'Worksheets(NOMFEUILLE).Range("B3").Value = Worksheets("News").Range("B" & i).Value
  25.  Worksheets("News").Range("B" & i).Copy .Range("B3")
  26.  .Range("C3").Value = t
  27.  .Rows("3:3").EntireRow.AutoFit
  28.  End With
  29.  End If
  31.  Next i
  33.  With Sheets("News").Activate
  34.  End With
  36. End Sub
  39. Function GetHash(ByVal txt$) As String
  40.  Dim oUTF8, oMD5, abyt, i&, k&, hi&, lo&, chHi$, chLo$
  41.  Set oUTF8 = CreateObject("System.Text.UTF8Encoding")
  42.  Set oMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
  43.  abyt = oMD5.ComputeHash_2(oUTF8.GetBytes_4(txt$))
  44.  For i = 1 To LenB(abyt)
  45.  k = AscB(MidB(abyt, i, 1))
  46.  lo = k Mod 16: hi = (k - lo) / 16
  47.  If hi > 9 Then chHi = Chr(Asc("a") + hi - 10) Else chHi = Chr(Asc("0") + hi)
  48.  If lo > 9 Then chLo = Chr(Asc("a") + lo - 10) Else chLo = Chr(Asc("0") + lo)
  49.  GetHash = GetHash & chHi & chLo
  50.  Next
  51.  Set oUTF8 = Nothing: Set oMD5 = Nothing
  52. 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
Attached Images
File Type: jpg Web links.jpg (78.0 KB, 504 views)
File Type: jpg Weekly.jpg (156.3 KB, 446 views)
File Type: jpg 2.JPG (45.6 KB, 419 views)
Jan 4 '21 #1
16 2898
214 Expert 128KB
For a start, this is superfluous:

Expand|Select|Wrap|Line Numbers
  1. Dim LADATE As Date
  2. LADATE = Format(CDate(Now), "dd/MM/yyyy")
as it can be reduced to:

Expand|Select|Wrap|Line Numbers
  1. Dim LADATE As Date
  2. LADATE = Date
Jan 4 '21 #2
13 Byte
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
Jan 4 '21 #3
214 Expert 128KB
Sorry, busy with real work - those lines just caught my eyes.
Jan 4 '21 #4
13 Byte
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?
Jan 4 '21 #5
32,556 Expert Mod 16PB
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.
Jan 4 '21 #6
13 Byte
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
Jan 4 '21 #7
3,653 Expert Mod 2GB

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!
Jan 4 '21 #8
12,516 Expert Mod 8TB
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.
Jan 4 '21 #9
2,460 Expert Mod 2GB
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.

Import data from external data sources (Power Query)
Import data from a Web page in Excel
Jan 4 '21 #10
32,556 Expert Mod 16PB
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.
Jan 4 '21 #11
13 Byte
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 .

Jan 5 '21 #12
13 Byte
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
Jan 5 '21 #13
13 Byte
Hello NeoPa.

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

Our misunderstanding 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

Jan 5 '21 #14
32,556 Expert Mod 16PB
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 ;-)
Jan 5 '21 #15
13 Byte
Hello NeoPa, Niheel, Rabbit, Twinnyfo & Cactusdata

Thank you for giving me the opportunity to discussed this thread. "Greatly appreciate it".

My Challenge was solved.

A person of the name Narimanych, has helped me in a different forum. Plus they are other recommendation give by him & others.

Below is the link that solved my problem*.

Thanks for you feed back. Hope this thread help someone in the future.

*<Link to Competing Forum Site Removed>
Jan 6 '21 #16
32,556 Expert Mod 16PB
I'm sure your breaking of the rules was unintentional but I'm afraid I have had to remove the link to the competing forum site.

The resources here are provided free of charge to the public as well as to members, but that doesn't mean there aren't costs involved to provide this service, and we have rules (Bytes Rules & FAQ), just like any site does, to protect the sources of income that enable it to work that way. This ensures we can continue to provide the service without having to charge fees to the members.
Jan 6 '21 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Mansi | last post by:
I'm trying to automate excel from c#. One of the things I need to do is to copy/paste/insert rows in excel via c# code. I tried to do the following: 1) Select a row in excel (a12 to k12) 2)...
by: lgbjr | last post by:
Hello All, I¡¯m using a context menu associated with some pictureboxes to provide copy/paste functionality. Copying the image to the clipboard was easy. But pasting an image from the clipboard...
by: serge calderara | last post by:
Dear all, I have an treeview control with different node object, I would like to implement the Copy/Paste function of an object . For that I am using the folowing function to copy teh object to...
by: roopashree | last post by:
hi, currently I am able to cut,copy and paste images for only one image. Suppose I have 4 images-then I should group all the images so that I can cut/copy all 4 images and paste them. How...
by: alarock | last post by:
anyone please reply me ..how do i do cut copy paste operation using graphics path and clipboard with c#........... please reply to my mailto:: deleted
by: OfficeDummy | last post by:
Hi, everyone! Like I mentioned in the thread title, I need to copy&paste data between different workbooks, and it works fine. However, when the data has been copied to the destination workbook,...
by: veer | last post by:
hi all i want to create a program in which i want to use function key like F12, F11 i mean to say when i press F11 or F12 a particular code is paste in text box. when i used this method in my...
by: KeredDrahcir | last post by:
Can anyone help me. I don't know if this is a mysql, php or Google Chrome error. I have a login form but for some reason I'm getting an error when I use Google Chrome. It used to work in all...
by: snehac | last post by:
Hi, i am pretty amateur in VBA, it would be great if anyone could help me on this. I need to copy data from excel to website...Please help.
by: peridian | last post by:
Hi, If I use php to echo an XML file directly to the response, all I see is the XML text. This is because Google Chrome comments out the XML declaration at the start of the text, causing it to...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.