Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/close web pages
I've a workbook called "Wheels".
It contains multiple sheets. One sheet named "Values" & is setup such that Col A has hyperlinks and the remaining columns are to contain the data from a web page related to that link. Another sheet is called "Import". I've written code (let's call it "Sub Transfer()" )that opens the web page from the link using: Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True I then MANUALLY do a SELECT ALL & COPY in IE. My code then pastes the web page, as text, onto Sheet2 using: Columns("A:A").Select ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _ DisplayAsIcon:=False From this sheet, I manipulate the data & paste the required info onto the sheet "Values". I then MANUALLY close the web page. (I've got over 1K of links so I can't keep them open in IE) My code then selects the next hyperlink & I repeat the process. How might I use VBA to automate the MANUAL steps? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/close web pages
Al
have you ever tried webqueries? (data/import external/new webquery) once you've got that working then it's very easy to automate (you just change the connectstring and refresh the query) Note that when you add a querytable it also adds a range name, which is not cleared when you delete the query. Also note the importance of backgroundquery... if you set that to TRUE your code will continue before the query has completed.. Sub Demo() Dim qt As QueryTable Dim i As Integer With ActiveSheet.QueryTables For i = .Count To 1 Step -1 With .Item(i) On Error Resume Next .Destination.EntireRow.Delete On Error GoTo 0 .Delete End With Next Set qt = .Add(Connection:="URL;", Destination:=Range("A1")) End With With qt .Connection = "URL;http://www.devguru.com/home.asp" .Name = "DevGuru" .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With MsgBox "Done" End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool Al <none wrote: I've a workbook called "Wheels". It contains multiple sheets. One sheet named "Values" & is setup such that Col A has hyperlinks and the remaining columns are to contain the data from a web page related to that link. Another sheet is called "Import". I've written code (let's call it "Sub Transfer()" )that opens the web page from the link using: Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True I then MANUALLY do a SELECT ALL & COPY in IE. My code then pastes the web page, as text, onto Sheet2 using: Columns("A:A").Select ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _ DisplayAsIcon:=False From this sheet, I manipulate the data & paste the required info onto the sheet "Values". I then MANUALLY close the web page. (I've got over 1K of links so I can't keep them open in IE) My code then selects the next hyperlink & I repeat the process. How might I use VBA to automate the MANUAL steps? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/close web pages
No I haven't tried webqueries but since your reply I looked into them.
On comment I read of note is that they are one of the least publicized features of Excel. That said I started to look into working with them. But it's all new to me at this point. So if you wouldn't mind, could you explain a few lines of your Sub()? And if you could refer me to some text or site where I could learn, that would be even great As to the code: I am correct in assuming your 1st "With/End With group of commands" is the "loop" to close the last link? I'm confused about the lines: .Connection = "URL;http://www.devguru.com/home.asp" .Name = "DevGuru" What 's the purpose of .Connection if the Set qt is .Add(Connection:="URL;", Destination:=Range("A1")) What's the purpose of .Name BTW, that's for the intro & the help. "keepITcool" wrote in message ... Al have you ever tried webqueries? (data/import external/new webquery) once you've got that working then it's very easy to automate (you just change the connectstring and refresh the query) Note that when you add a querytable it also adds a range name, which is not cleared when you delete the query. Also note the importance of backgroundquery... if you set that to TRUE your code will continue before the query has completed.. Sub Demo() Dim qt As QueryTable Dim i As Integer With ActiveSheet.QueryTables For i = .Count To 1 Step -1 With .Item(i) On Error Resume Next .Destination.EntireRow.Delete On Error GoTo 0 .Delete End With Next Set qt = .Add(Connection:="URL;", Destination:=Range("A1")) End With With qt .Connection = "URL;http://www.devguru.com/home.asp" .Name = "DevGuru" .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With MsgBox "Done" End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool Al <none wrote: I've a workbook called "Wheels". It contains multiple sheets. One sheet named "Values" & is setup such that Col A has hyperlinks and the remaining columns are to contain the data from a web page related to that link. Another sheet is called "Import". I've written code (let's call it "Sub Transfer()" )that opens the web page from the link using: Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True I then MANUALLY do a SELECT ALL & COPY in IE. My code then pastes the web page, as text, onto Sheet2 using: Columns("A:A").Select ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _ DisplayAsIcon:=False From this sheet, I manipulate the data & paste the required info onto the sheet "Values". I then MANUALLY close the web page. (I've got over 1K of links so I can't keep them open in IE) My code then selects the next hyperlink & I repeat the process. How might I use VBA to automate the MANUAL steps? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/close web pages
With object
.property1 = value .property2 = value .method end with ... a syntax to reference an object once, then manipulate several properties or methods on the object. in my example I split the ADD line and the line where you set the actual url. No use really other then to demo that you can change the url for an existing query... For your original purpose you could have 1 wq. With code you could iterate an array of connectstrings. and dump retrieved data in another sheet. the .Name is set so you can later refer to the table by it's name. With Activesheet.QueryTables("MultiPurposeWebQuery") .Connection = "URL;http://support.microsoft.com" end with keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "AG" wrote: No I haven't tried webqueries but since your reply I looked into them. On comment I read of note is that they are one of the least publicized features of Excel. That said I started to look into working with them. But it's all new to me at this point. So if you wouldn't mind, could you explain a few lines of your Sub()? And if you could refer me to some text or site where I could learn, that would be even great As to the code: I am correct in assuming your 1st "With/End With group of commands" is the "loop" to close the last link? I'm confused about the lines: .Connection = "URL;http://www.devguru.com/home.asp" .Name = "DevGuru" What 's the purpose of .Connection if the Set qt is .Add(Connection:="URL;", Destination:=Range("A1")) What's the purpose of .Name BTW, that's for the intro & the help. "keepITcool" wrote in message ... Al have you ever tried webqueries? (data/import external/new webquery) once you've got that working then it's very easy to automate (you just change the connectstring and refresh the query) Note that when you add a querytable it also adds a range name, which is not cleared when you delete the query. Also note the importance of backgroundquery... if you set that to TRUE your code will continue before the query has completed.. Sub Demo() Dim qt As QueryTable Dim i As Integer With ActiveSheet.QueryTables For i = .Count To 1 Step -1 With .Item(i) On Error Resume Next .Destination.EntireRow.Delete On Error GoTo 0 .Delete End With Next Set qt = .Add(Connection:="URL;", Destination:=Range("A1")) End With With qt .Connection = "URL;http://www.devguru.com/home.asp" .Name = "DevGuru" .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlOverwriteCells .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = True .Refresh BackgroundQuery:=False End With MsgBox "Done" End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool Al <none wrote: I've a workbook called "Wheels". It contains multiple sheets. One sheet named "Values" & is setup such that Col A has hyperlinks and the remaining columns are to contain the data from a web page related to that link. Another sheet is called "Import". I've written code (let's call it "Sub Transfer()" )that opens the web page from the link using: Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True I then MANUALLY do a SELECT ALL & COPY in IE. My code then pastes the web page, as text, onto Sheet2 using: Columns("A:A").Select ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _ DisplayAsIcon:=False From this sheet, I manipulate the data & paste the required info onto the sheet "Values". I then MANUALLY close the web page. (I've got over 1K of links so I can't keep them open in IE) My code then selects the next hyperlink & I repeat the process. How might I use VBA to automate the MANUAL steps? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy command causes Excel to close | Excel Discussion (Misc queries) | |||
Copy from another file and close automatically | Excel Discussion (Misc queries) | |||
Macro to close workbook and re-open new copy | Excel Discussion (Misc queries) | |||
copy value to all pages | Excel Programming | |||
Open Copy Close | Excel Programming |