vb scripting -- copying web page, paste as text

[Nixter]

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

[Nixter]

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
>

[Chrispy]

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
> >

[Bob Phillips]

Try taking out the line re WebDisableRedirections completely.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chrispy" <[email protected]> wrote in message
news:[email protected]...
> 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
> > >

[Chrispy]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > > >
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > > >
> >
> >
> >