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

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

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

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

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

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