Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb scripting -- copying web page, paste as text
I make a report daily that is a copy, paste text only of an internal site. I
am trying to emulate a copy of an internal web page and paste--text only into a spreadsheet. From there I have a macro that runs & cuts the data into a report. Is there a way to insert this copy/paste into the script? The closest I could come up with is : ==== Sub GetData() Workbooks.Open "http://server/content./" End Sub Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb scripting -- copying web page, paste as text
With credits & kudos to user KeepItCool, I found this:
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 "Nixter" wrote: I make a report daily that is a copy, paste text only of an internal site. I am trying to emulate a copy of an internal web page and paste--text only into a spreadsheet. From there I have a macro that runs & cuts the data into a report. Is there a way to insert this copy/paste into the script? The closest I could come up with is : ==== Sub GetData() Workbooks.Open "http://server/content./" End Sub Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb scripting -- copying web page, paste as text
I tried this code, but got errors in the following 2 lines...
.WebDisableRedirections = True .Refresh BackgroundQuery:=False Were you able to get this code to work? Regards, Chrispy "Nixter" wrote: With credits & kudos to user KeepItCool, I found this: 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 "Nixter" wrote: I make a report daily that is a copy, paste text only of an internal site. I am trying to emulate a copy of an internal web page and paste--text only into a spreadsheet. From there I have a macro that runs & cuts the data into a report. Is there a way to insert this copy/paste into the script? The closest I could come up with is : ==== Sub GetData() Workbooks.Open "http://server/content./" End Sub Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb scripting -- copying web page, paste as text
Try taking out the line re WebDisableRedirections completely.
-- HTH RP (remove nothere from the email address if mailing direct) "Chrispy" wrote in message ... I tried this code, but got errors in the following 2 lines... .WebDisableRedirections = True .Refresh BackgroundQuery:=False Were you able to get this code to work? Regards, Chrispy "Nixter" wrote: With credits & kudos to user KeepItCool, I found this: 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 "Nixter" wrote: I make a report daily that is a copy, paste text only of an internal site. I am trying to emulate a copy of an internal web page and paste--text only into a spreadsheet. From there I have a macro that runs & cuts the data into a report. Is there a way to insert this copy/paste into the script? The closest I could come up with is : ==== Sub GetData() Workbooks.Open "http://server/content./" End Sub Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb scripting -- copying web page, paste as text
Yes, I commented that one out already as that property is not available, and
it gave an error with the Refresh line "Bob Phillips" wrote: Try taking out the line re WebDisableRedirections completely. -- HTH RP (remove nothere from the email address if mailing direct) "Chrispy" wrote in message ... I tried this code, but got errors in the following 2 lines... .WebDisableRedirections = True .Refresh BackgroundQuery:=False Were you able to get this code to work? Regards, Chrispy "Nixter" wrote: With credits & kudos to user KeepItCool, I found this: 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 "Nixter" wrote: I make a report daily that is a copy, paste text only of an internal site. I am trying to emulate a copy of an internal web page and paste--text only into a spreadsheet. From there I have a macro that runs & cuts the data into a report. Is there a way to insert this copy/paste into the script? The closest I could come up with is : ==== Sub GetData() Workbooks.Open "http://server/content./" End Sub Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb scripting -- copying web page, paste as text
What was the error message. Can you query the site manually? If so, try
re-recording the macro on that machine. -- Regards, Tom Ogilvy "Chrispy" wrote in message ... Yes, I commented that one out already as that property is not available, and it gave an error with the Refresh line "Bob Phillips" wrote: Try taking out the line re WebDisableRedirections completely. -- HTH RP (remove nothere from the email address if mailing direct) "Chrispy" wrote in message ... I tried this code, but got errors in the following 2 lines... .WebDisableRedirections = True .Refresh BackgroundQuery:=False Were you able to get this code to work? Regards, Chrispy "Nixter" wrote: With credits & kudos to user KeepItCool, I found this: 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 "Nixter" wrote: I make a report daily that is a copy, paste text only of an internal site. I am trying to emulate a copy of an internal web page and paste--text only into a spreadsheet. From there I have a macro that runs & cuts the data into a report. Is there a way to insert this copy/paste into the script? The closest I could come up with is : ==== Sub GetData() Workbooks.Open "http://server/content./" End Sub Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Link not copying hyperlinks | Excel Discussion (Misc queries) | |||
how to repeat a paste of cells without copying again | Excel Discussion (Misc queries) | |||
copying page with VBA | Excel Programming | |||
Selecting ranges to copying and Paste | Excel Programming |