VBA to copy an htm file into excel

[Gwyndalf]

The target file is on my computer although its name changes twice a week.
If I use a web query it tells me there is no data. If I manually select the
contents then copy and paste it into my spreadsheet all the data is there.
What I want to do from within an excel macro:
1. Open the htm file selected by the user ie 12mar.htm (when I do this
manually it opens in IE - my attempts to reproduce this with vba have failed
miserably)
2. Select and copy the file contents (the whole page is fine)
3. paste it into a specific sheet (called "Hands") in my workbook.
4. Close the htm file and return to excel/continue with the macro I've
written

(From this stage I've already got the macro to place the data in the correct
areas on another sheet ready for web uploading)

Thank you to anyone who can offer assistance

 

[ryguy7272]

Import it! Turn on the macro recorder, go to Daat > Import > follow the
Wizard prompts. When finished, turn off the macro recorder and view your
code.

Play with it for a while; should make sense.

Post back if you have more questions.

Good luck,
Ryan---

 

[Gwyndalf]

I've tried that and Excel attempts to do it via a web query; it asks me to
select what I want to import and then informs me that there is no data.
(That's doing it manually not via vba) What it can import is merely the
title (the tick box then highlights a border around all of the page but there
are 12 other tick boxes for the tables themselves) and then it ignores the 12
tables below it - even if they've been selected.

 

[ryguy7272]

Humm, not sure what you've got there. I looked at this site:
http://lib.stat.cmu.edu/DASL/Datafiles/Birthrates.html

Click File > Save As > Web Page, HTML, and then in Excel > Data > Import
Data ... navigate to the source ... Open > Click the Yellow Arrow (next to
Birthrate) and Make it a Green Check > Import. That works Fine!

Or,

Click File > Save As > Text File, and then in Excel > Data > Import Data ...
navigate to the source ... Open > Click the Yellow Arrow (next to Birthrate)
and Make it a Green Check > Import. That works Fine too!

If your file doesn't import, maybe it it not HTML, or a format that Excel
can interpret. It sounds like an issue with the File itself, not Excel.
Maybe you have to start saving those files in a different format.

Try what I suggested and see how you get along. I know that works. Once
you get it working. Turn on the macro recorder and run through the steps.
When you are done, turn off the macro recorder and assign a Command Button to
your code. Then you can run the import process, in a fraction of a secodn,
whenever you need to.

Good luck,
Ryan---

 

[Gwyndalf]

Thanks for your efforts - I've managed to accomplish by using
'Application.SendKeys'
as could not get the web query to identify any data other than the title.