Macro to download data from website (need fill in info and redirect to further pages)

Dear professionals

I wish to write a macro, trying to do the following steps:

1. open the website: http://webb-site.com/
2. fill in a stock code (e.g 8120) AND click the button “current” redirect to another webpage
3. Click the “raw prices”
4. Import the data from web into the excel spreadsheet.


1. open the website: http://webb-site.com/

2. fill in a stock code (e.g 8120) AND click the button “current” redirect to another webpage
1.jpg

3. Click the “raw prices”
2.png

4. Import the data from web into the excel spreadsheet.
1.jpg


Many many thanks....

Originally Posted by tatfish87

I wish to write a macro

Hi,

where is your code ?


Automate Internet Explorer

Sub createseed()
'
' createseed 巨集
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://webb-site.com/dbpub/hpu.asp?i=2972", Destination:=Range("$A$1"))
.Name = "hpu.asp?i=2972"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


I use the "record macro" function of excel to get the above code, but the problem i encounter is that, i have to manually do step 1 to step 3 to go to the page :http://webb-site.com/dbpub/hpu.asp?i=2972

1. open the website: http://webb-site.com/
2. fill in a stock code (e.g 8120) AND click the button “current” redirect to another webpage
3. Click the “raw prices”

Can it be some code for automate the step 1 to step 3 please? Many thanks...

You must respect forum rules about code tags !


You don't have to start from the home site as you should see in the webpage address after submit Current button …

Follow samples in my previous post link to begin your code to pilot IE …

4. Import the data from web into the excel spreadsheet.

Which ? Where ? …

Hi Marc, sorry for overlook, i am new comer.. will take note for the code tags in future..

I wish to set up a button (with the macro inside) in an excel file, after pressing it, then create another new excel file and import the data into it...

thank you..

Originally Posted by tatfish87

import the data into it...

Which data ?

Dear Marc

I have study your previous post and revise the code as follow:

Please Login or Register  to view this content.

The macro work well until the following: I wish to automate the IE to click the text link "raw prices" of the first row (there are several text link named "raw prices" . Would you please kindly teach me to debug? I have replace the "Forgot your Password?" with "raw prices" but fail to work...thanks

Please Login or Register  to view this content.

For further clarification, i wish to import all the data (after click into the text link "raw prices" ... into a newly create excel file.

Which mean.. macro button on excel file A, use the macro to create excel file B with the imported data... Thanks..

The code above is just doing the automate internet part, not yet reach the create excel file part, as i am being stucked at the step of automate clicking the text link "raw prices".

Dear Marc

I must express my high appreciation and thank you to you.

However, at the same time i have a little bit depress as i cannot continuing the code which i am using(which was done by many try and error as a newbie). Anyway really thank you.
As this code is totally different thing, i cannot understand much.

This macro works very nice to capture the whole page data into a new created excel (file name book1)
May I have a little more about it please?

I created a button(with your macro code inside) at Excel file A.
Is it possible that, i enter the stock code (replace 8120) in a cell of Excel A, then run your macro, and then generate the Excel B (with file named "seed") ??
Currently i need to manually replace the 8120 inside the code in order to retrieve the data for another stock..

Lastly for this data capture, i dont need the capture all above the row of "Trade day, close, high low...."
Anyway, i can use another macro to delete them, just see if it is possible to get rid of it from the beginning...

Again, thank you for your time and expertise !! you are excellent.

- I wish to paste the data (table) at cell A1 , sheet2 of the new created excel file. Sorry for not making myself clear.

- I wish to type the stock code in the cell G2 of excel A (original file contain the button & macro), is it i should replace the "STOCK" in line 6 of your code with "=G2"?

- Can the macro instructed the newly created excel B to be saved at a desinated path? currently i wish to save it with name "seed" at the same directory of the workbook with macro (you are correct) ..

Dear Marc

For the stock code part, i have tried this but not work..

Please Login or Register  to view this content.

i have amended line 1 and line 6..but not work..

Your changes mean nothing but an error … For Excel VBA basics, use the Macro recorder, the newbie best friend !

A VBA code must respect Excel objects levels : Application / Workbook / Worksheet / Cells …
So cell G2 from workbook A : ok but don't you think something is missing ?

I wrote clear for it in my previous post :

I don't know which cell from which worksheet from I suppose the workbook where is located the code.

For the new workbook, once saved, should be closed or stays opened ?

Thanks it was of good help

Dear Marc

Yes i always use macro recorder , but cant with your code, it seems different thing..

The code should be located in Excel A.
I want the button locate at sheet1 of Excel A.
The Cell G2 on sheet1 of Excel A for me to enter a stock code (e.g 8120).

After i enter the stock code in G2 on sheet1 of Excel A... press the button (contain your code).
Generate a new excel file B, with the data pasted starting at cell A1 on sheet1 of excel B..

It will be very kind for you to amend your original code for me this time.. so i can understand the core part of the amendment.. Thank you again Marc..

For the new workbook (Excel B named "seed"). should be closed once saved. (in the same directory of excel A)

Modify a cell value is a very Excel basics, so using Macro Recorder brings VBA code to reach a cell
whatever the kind of code (by the way several request samples in this forum !) …

Please Login or Register  to view this content.

Dear Marc

Thank you very much, it works nicely.. in my excel A.. there is a macro, will open the newly created (excel B) to get the data inside..the code is as follow:

Please Login or Register  to view this content.

Actually, "Processor" is my Excel A... putting at desktop. The "Seed" is Excel B, also at desktop
When i run the above code, error occurs at the row of

Please Login or Register  to view this content.

I dont have such error if the seed is manually created by data import from web.. Do you know the reason of it?

THank you again.

Dear Marc

I believe the error is not caused by the file name. i have tried to rename the manually created excel B to "seed".. then the error does not occur.

If the "seed" is created by using your code.. then the error will occur at

Please Login or Register  to view this content.

So i guess there is different in nature of the macro created "seed" and the manual created "seed"....

Obviously, error cames from file name in your code not equal to real file name ‼
Just see in Windows Explorer as in my code …

Originally Posted by tatfish87

there is a macro, will open the newly created (excel B) to get the data inside

If you need data in workbook A, why create workbook B ?‼ Why not loading data directly in workbook A ? …

Dear Marc

I have figured out the problem finally, there is a "space" after the name "seed"

Thanks a lot for your time..