automate copy, paste from htm to excel

[HansD]

Hello Everybody

I hope someone can help me, because i am going crazy.
Our reseller has a pricelist on his website and I need that data in my access database. The data needs to be sorted first and I am using Excel for that.

From Access I downloaded the pricelist ( which is a ASP site) from a protected site to my computer (test.htm), it is a file that looks likes this:

[Productnumber] [Product] [Quantity in stock] [Price]

Underneed that it has about 8500 Products in it.
When I import it into Excel using "import external data" it is all messed up. but when I copy and paste it it is okay.

What I am trying to do is automate the entire process. So when my Excel file (products.xls) opens I need a macro that selects the text from test.htm and copy's it to the clipboard. Then paste it in the excel file. From there I need to organize the data further, but I have that working (Thank God).

I really hope somebody can help me, because I think it is really simple to do, but I really don't see it anymore.

Thanks
Hans

 

[lebisol]

not the best solution but it will take care of copy/paste part:

http://www.hiddensoft.com/autoit3/

All the best!

:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...

 

[HansD]

thanks for the fast reply, I don't know this program.
I am not sure if I can use that.

I need a macro that does the trick, because after the copy and paste the macro continious the sort the data and then imports it back into my Access Database

 

[lebisol]

u can automate anything you want...but again we are talking keystrokes & clicks

above all, why not export data from access into CSV or excell file to begin with. excel-excel shoud be more smoth of a process.
All the best!

:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...

 

[HansD]

The data is not coming from Access, but comes from a ASP website. With Access I already have automated to go to the site, enter password, open correct page and save that page to my computer. Because I need to organize the data before it goes back to Access I am using Excel. Thats easier because of the "record macro" thingie.

But I don't know how to program a macro: to open my downloaded htm file in iexplorer, select all the text, copy it to clipboard and then paste it in my excel file

 

[lebisol]

hmm...just trowing some ideas...not that I am of any help ..
-is it possible to use access and ODBC to the server/asp page?
-can you filter data in access without using excel?
...
consider autoit...its rather easy to use, you can also make an file.exe and schedule it to be executed...
in maner of:
-open IE
-send keystrokes to login into site etc.
-send ctrl+a
-send ctrl+c
-send ctrl+v to paste
etc.

sorry i am not much of programmer when it comes to macros but could give you a hand with the little utility if you get no help from excel gurus.
All the best!


:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...

 

[KenWright]

Just a thought, but have you tried right clicking on the web page and choosing the 'Export to Microsoft Excel' option?

Granted it doesn't automate it, but might be worth a try.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[HansD]

hey guys

Sorry that I react so late but I have been away for a few days. Thanks for your reactions.

I have now programmed it with the sendkeys function.
just open site send "ctrl select" and then "ctrl copy".
It works but after the copy the pc freezes for about 3 minuts and then goes on.
It is a solution but not the best i think, it should go faster.

Ken: when i do "export to excel" the data in excel will be real messed up.


Thanks forall the help, and i hope somebody has a faster solution

Hans