Solved: Copy/Paste from HTML Spreadsheet to Excel

[JP2R]

Quick background - because this is actually a several part 'issue' -
Searching and removing ad hoc networks throughout a secured campus area I have to take a snapshot of a screen - using a Cisco 'tool' - that shows the ad hoc networks within the area, however, when I copy and paste the information into Word or Excel it becomes an image or picture and sits on top of the cells.

Is there a work around for this?
I've tried to paste it using "Paste Special" and the choices provided - to no avail.

I am using a Dell Laptop
Microsoft Office 2007 Professional
Browsers used were IE and Firefox - most current versions

I had been able to do this in the past - but for some reason not now - perhaps I'm overlooking something.

-- Jae

[lucas]

Jae, is the data in a table in the html file? Is a screenshot the only way you can access the data?

The reason I ask is that I think there is an entry in the knowledbase (kb at the top of the page) that will retrieve the contents of a table in a web page into excel. Would that be possible?

[JP2R]

Jae, is the data in a table in the html file? Is a screenshot the only way you can access the data?

The reason I ask is that I think there is an entry in the knowledbase (kb at the top of the page) that will retrieve the contents of a table in a web page into excel. Would that be possible?

Lucas,

Yes, the data in the table in the html file. I can't grab a screen shot with Firefox, but I was able to do so with IE. I didn't know if there was any other way to grab it, I'm sorry - not most savvy with that stuff.

I will check the post you mention - in the Knowledge Base - I may have overlooked it or misread it (which often happens) sorry for the repeat posting if so...

Sincerely,
Jae

[lucas]

Jae, here is a link to it.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=576

[JP2R]

Jae, here is a link to it.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=576

Lucas,

This is awesome! I am going to try and login (VPN) to work and see if I can't get this to work. I have one other question regarding this - if you don't mind - (you have been so helpful).

I noticed that in the code you have it set to go to position A1 - how would it know to go to the next available row if I were having to update this twice a day?

(I don't mind having to go in and change it - was just wondering)

Most respectfully,

-- Jae

[lucas]

Try this Jae
[VBA]Option Explicit
Sub gethtmltable()
Dim objWeb As QueryTable
Dim sWebTable As String
Dim LastRow As Object
'You have to count down the tables on the URL listed in your query
'This example shows how to retrieve the 2nd table from the web page.
sWebTable = 2

Set LastRow = Range("A" & Rows.Count).End(xlUp)
'Sets the url to run the query and the destination in the excel file
'You can change both to suit your needs



Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://www.vbaexpress.com/kb/default.php", _
Destination:=LastRow.Offset(2, 0))


' Set objWeb = ActiveSheet.QueryTables.Add( _
' Connection:="URL;http://www.vbaexpress.com/kb/default.php", _
' Destination:=Range("A1"))




With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = sWebTable
.Refresh BackgroundQuery:=False
.SaveData = True
End With
Set objWeb = Nothing
End Sub
[/VBA]

[JP2R]

Lucas -

I tried this and it works - but I'm just getting the headers.
I used a FireFox add-on (Web Developer) and checked the depth of the table and it appears to be 3 - I have attached an image.

The page has frames - and I had the frame open in a separate page - I checked the link or address and it was

https://wwism1/screens/apf/adhoc_rogue_list.html

a login is required - and the MAC address is a hyperlink for further detail
so I'm not sure how to get the data from the table.

another image is one from using Web Developer ( Will post after this one) to get more information
please let me know if I've just got my head in my socks.

Thanks
-- Jae

[JP2R]

Here is the second image
-- Jae

[JP2R]

Guys --

Again, I wanted to provide an update for this particular problem of Copy/Paste.

Recognizing that https: (the 's' being the problem here) - it is not possible to copy and paste a table utilizing the standard processes available. So, if a person has the full blown Adobe deal they can do this - or - you can simply use the Microsoft version of the PDF by:

1. Right click on the frame of the webpage you wish to obtain the table from
2. select "print"
3. for the printer choose Microsoft XPS Document Writer
4. Save to desktop
5. Right click the file you've saved
6. Highlight the table
7. Paste into Excel Spreadsheet
... if using 2007 the paste option that pops up will allow you to import a text file...just choose this and follow the steps...

I hope this helps someone else - as it did me. I really struggled with this one. I still haven't figured out to automate it much - but this works for now.

-- Jae --

[kinshasa]

i know this thread is old...but i was wondering if they might be a way to modify this code and to let a macro take urls from colA (sheet1) and paste the entire page(xlentirepage) on sheet2...then go back to 2nd url on colA(sheet1) and paste that info on the last row on sheet2???
url are always the same
wwwdotdomaindotcom/1
wwwdotdomaindotcom/2
wwwdotdomaindotcom/3