Hyperlinks, import html and other stuf
Hans Jeucken
This time i have a lot op questions
question one:
in a sheet i have in row f a lot of hyperlinks
in al of these hyperlinks i have to change a word
(search has to change in ped)
Than i want to open the firts hyperlink, import the page (or part of)
close ie (otherwise i have 40 open ie and go out of memory)
and open the next hyperlink
is this posible?
Greetings Hans
Stratos Malasiotis
This sounds possible; however you'll have to use Netscape (just
joking).
What to you mean by " *import* the page (or part of) "? Import it where?
Regards,
Stratos
Hans Jeucken
"Stratos Malasiotis" <ie...@csv.warwick.ac.uk> schreef in bericht
news:3969D334...@csv.warwick.ac.uk...
Stratos Malasiotis
I was expecting Jake Marx to pop-up showing us his super-duper IE
Automation procedures, but it seems that he abandoned us (where are you
Jake?<g>).
Well..., here is an alternative solution; at least something to get you
started.
You said:
> read "copy" or "paste" into a sheet of excel 2000
Unfortunately, I don't have access to XL2000 yet, but the following
applies to 97 and above.
As regards, copying and pasting I don't think that this is required.
in a standard module add (although it cries for a class module):
-----------------------------------------------------------------------------------------
Option Explicit
Private Function fncGetWebData(URLocation As String, _
Optional SourceTableAddress As String =
vbNullString, _
Optional TargetBookFullName As String =
vbNullString, _
Optional TargetTableAddress As String =
vbNullString, _
Optional DisplayMessages As Boolean =
True) As Boolean
'downloads data from a specified URL and puts them in a new workbook
Dim WebBook As Workbook, TargetBook As Workbook
Dim WebTable As Range, WebTableDims(1 To 2) As Long
Dim TargetTable As Range
Dim SheetIndex As Integer, SheetNo As Integer
Dim TargetBookName As String
Dim FullNameLen As Integer, anIndex As Integer
Dim MessagePrompt As String
'initiate the result of the function to False; assume failure
fncGetWebData = False
'initialise other variables
TargetBookName = vbNullString
'turn screenupdating off; display message to statusbar
Application.StatusBar = "Downloading Web Data... Please Wait"
Application.ScreenUpdating = False
'download the specified html file
On Error Resume Next
Application.DisplayAlerts = False
Set WebBook = Workbooks.Open(FileName:=URLocation)
Application.DisplayAlerts = True
On Error GoTo 0
If WebBook Is Nothing Then
MessagePrompt = "The specified URL could not be found." & Chr(10) &
_
"No data were collected"
If DisplayMessages = True Then
MsgBox Title:="Get Web Data", Prompt:=MessagePrompt,
Buttons:=vbCritical
End If
GoTo ExitFunction
End If
'get the data from the source table in the WebBook
On Error GoTo ExitFunction
'if the SourceTableAddress argument is missing get the used range
If SourceTableAddress = vbNullString Then
Set WebTable = WebBook.Worksheets(1).UsedRange
WebTableDims(1) = WebTable.Rows.Count
WebTableDims(2) = WebTable.Columns.Count
Else
'get the range specified by SourceTableAddress argument
On Error Resume Next
Set WebTable = WebBook.Worksheets(1).Range(SourceTableAddress)
On Error GoTo 0
If WebTable Is Nothing Then 'i.e. if the specified address was
invalid
MessagePrompt = "The specified source range address could not
be located." & Chr(10) & _
"No data were collected."
If DisplayMessages = True Then
MsgBox Title:="Get Web Data", Prompt:=MessagePrompt,
Buttons:=vbCritical
End If
GoTo ExitFunction
End If
WebTableDims(1) = WebTable.Rows.Count
WebTableDims(2) = WebTable.Columns.Count
End If
'determine in which workbook to put the collected data
If Not TargetBookFullName = vbNullString Then
FullNameLen = Len(TargetBookFullName)
For anIndex = FullNameLen To 1 Step -1
If Mid(TargetBookFullName, anIndex, 1) =
Application.PathSeparator Then Exit For
TargetBookName = Right(TargetBookFullName,
Len(TargetBookFullName) - anIndex + 1)
Next anIndex
On Error Resume Next
Set TargetBook = Workbooks(TargetBookName)
If TargetBook Is Nothing Then
Set TargetBook = Workbooks.Open(FileName:=TargetBookFullName)
End If
On Error GoTo 0
End If
'if the TargetBookFullName file could not be found create a new
workbbok
On Error GoTo ExitFunction
If TargetBook Is Nothing Then
Set TargetBook = Workbooks.Add
TargetBook.Windows(1).Caption = "WebData" 'to be able to recognise
it from the controling
'sub-procedure
SheetNo = TargetBook.Worksheets.Count
'leave only one worksheet in the new workbook
If SheetNo > 1 Then
Application.DisplayAlerts = False
For SheetIndex = 2 To SheetNo
TargetBook.Worksheets(SheetIndex).Delete
Next SheetIndex
Application.DisplayAlerts = True
End If
End If
'determine the range to put the collected data
If TargetTableAddress = vbNullString Then
Set TargetTable = TargetBook.Worksheets(1).Cells(1,
1).Resize(WebTableDims(1), WebTableDims(2))
Else
On Error Resume Next
Set TargetTable = Range(TargetTableAddress).Cells(1,
1).Resize(WebTableDims(1), WebTableDims(2))
On Error GoTo 0
End If
If TargetTable Is Nothing Then 'i.e. if the target table address is
invalid
MessagePrompt = "The specified target range address could not be
located." & Chr(10) & _
"No data were collected."
If DisplayMessages = True Then
MsgBox Title:="Get Web Data", Prompt:=MessagePrompt,
Buttons:=vbCritical
End If
GoTo ExitFunction
End If
'put the data in the specified address
TargetTable.Value = WebTable.Value
'close the WebBook
Application.DisplayAlerts = False
WebBook.Close SaveChanges:=False
Application.DisplayAlerts = True
TargetBook.Activate
'display the result
Application.ScreenUpdating = True
'the function was completed succesfully
fncGetWebData = True
ExitFunction:
On Error Resume Next
Application.DisplayAlerts = False
WebBook.Close SaveChanges:=False
Application.DisplayAlerts = True
Application.StatusBar = False
On Error GoTo 0
Set WebBook = Nothing
Set TargetBook = Nothing
Set WebTable = Nothing
Set TargetTable = Nothing
End Function
Sub test1_fncGetWebData()
Debug.Print
fncGetWebData("http://www.warwick.ac.uk/research/rae/guide.html")
End Sub
Sub test2_fncGetWebData()
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
_
SourceTableAddress:="B10:F36")
End Sub
Sub test3_fncGetWebData()
'you must first save an empty, unprotected file at:
"d:\tempfile.xls"
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
_
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls")
End Sub
Sub test4_fncGetWebData()
'you must first save an empty, unprotected file at:
"d:\tempfile.xls"
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
_
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls", _
TargetTableAddress:="C10")
End Sub
----------------------------------------------------------------------------------
As you can see I have provided you with four examples of using the
function:
Without changing the URL address you can run the fist and second
To run the two last you must first save in your D:\ drive a file
called: "d:\tempfile.xls"
If you like the general idea you can improve the function and optimise
it for your purposes.
I think that you can now start building your project; if you run into
more problems and you want more help, you know what is the newsgroup to
turn to.
HTH
Jake Marx
Hi Stratos,
> I was expecting Jake Marx to pop-up showing us his super-duper IE
> Automation procedures, but it seems that he abandoned us (where are you
> Jake?<g>).
I'm still here - just returned yesterday from a long, no-email, no-web,
no-NG vacation. Quite a nice change of pace. <g>
> Well..., here is an alternative solution; at least something to get you
> started.
I think you did more than just get him started! I think that the automation
routines I've posted are great for some things, but not for others. For
example, if you're looking for a string within the body of a web document
(or a specific HTML tag), they work great. However, if you're looking to
open the URL and have it look similar to the original web document, the
Workbooks.Open method is the way to go, as you suggested.
Regards,
Jake Marx
Stratos Malasiotis
>
> I'm still here - just returned yesterday from a long, no-email, no-web,
> no-NG vacation. Quite a nice change of pace. <g>
>
Regards,
Stratos
Jake Marx wrote:
>
> Hi Stratos,
>
> > I was expecting Jake Marx to pop-up showing us his super-duper IE
> > Automation procedures, but it seems that he abandoned us (where are you
> > Jake?<g>).
>
> I'm still here - just returned yesterday from a long, no-email, no-web,
> no-NG vacation. Quite a nice change of pace. <g>
>
> > Well..., here is an alternative solution; at least something to get you
> > started.
>
Hans Jeucken
Briljant
Just two minor questions.
1. Can i keep the tempfile open?
So i can "copy" more than one hyperlink to the sheet.
2. Is there a way to supres the messages "file is open" and password and
username every time i open the site.
Greetings Hans
Stratos Malasiotis
> 1. Can i keep the tempfile open?
> So i can "copy" more than one hyperlink to the sheet.
In the way that it is now the tempfile.xls (or whatever file name) will
remain open and obviously you can't reopen it. There are two solutions:
one, not to specify any file name when you call the function; this will
open a new workbook called WebData (you can open as many of these you
want (or your system memory allows)); then in the calling procedure get
the data from the WebData and put them in any location of any other
workbook and close the Webdata. The second solution would be to modify
the code in the function so each time it finds the tempfile.xls open it
adds a new worksheet and put the data directly in there; i.e. optimise
the function for your purposes.
> 2. Is there a way to supres the messages "file is open" and password and
> username every time i open the site.
The "file is open" goes to the previous issue; means that tempfile.xls
is already open; you cannot reopen it.
I don't know exactly why you get password/login request (probably an FTP
site or something, I guess. Try record a macro opening the address from
the Open dialog and passing the password etc. If that works then there
may be a programmatic solution.
Also note that if you have an passworded ftp site you could specify an
address like:
ftp://login:pass...@mimosa.warwick.ac.uk/stratos/myfile.xls
At least, Ithink so...
HTH
Stratos
Stratos Malasiotis
Sub test6_fncGetWebData()
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
_
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls", _
TargetTableAddress:="Sheet1!C10")
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
_
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls", _
TargetTableAddress:="Sheet2!C10")
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
_
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls", _
TargetTableAddress:="Sheet3!C10")
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
_
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls", _
TargetTableAddress:="Sheet4!C10")
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
_
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls", _
TargetTableAddress:="Sheet5!C10")
End Sub
which worked perfectly fine (I inserted the five worksheets in advance).
Obviously it open and closes the web page 5 times which rather
inefficient but if you have 5 different web pages it is allright;
something like a web-query I guess.
Apparently, when I started designing the function I was thinking of web
pages; you may have to modify it for other kinds of files.
Stratos Malasiotis
Sub test8_fncGetWebData()
Debug.Print
fncGetWebData(URLocation:="ftp://mylogin:mypas...@mimosa.warwick.ac.uk/.../.../../.../.../index.html")
End Sub
and
Sub test8_fncGetWebData()
Debug.Print
fncGetWebData(URLocation:="ftp://mylogin:mypas...@mimosa.warwick.ac.uk/.../.../../.../.../temp.csv")
End Sub
which one of my passworded ftp accounts and it works perfectly fine (and
very fast since it is in the intranet).
So if you specify the address precisely you could open such kingd of
files as well.
Note the macro recorder cut off the password parts and it gave:
Workbooks.Open FileName:= _
"ftp://mimosa.warwick.ac.uk/.../.../../.../.../index.html""
Hans Jeucken
Thanks
The funny thing is there is no password etc.
When i open the site manualy i am not ask for any thing, only when i use
excel. Well i can live with this. (Just)
I have an other question tho
When i "import" the hyperlink i get the text of the site.
Is there a way to get the hyperlinks instead of the text?
P.S. the site is again a page with hyperlinks
Greetings Hans
Stratos Malasiotis
Still struggling with that project?
Yes you can get the hyperlinks as well.
Try to run the test1_fncGetWebLinks sub-procedures to get lots of them.
I have put some filters to remove unwanted limks but obviously is not
perfect yet; you can work on it
----------------------------------------------------------------------------------------------------------
Option Explicit
Public Function fncGetWebLinks(URLocation As String) As Integer
'downloads a file from a specified URL and puts any links in the a new
file
'returns: 1 - success: all hyperlinks have been collected successfully
' 0 - failure: unexpected error
' -1 - failure: the target URL could not be opened
' -2 - failure: the target URL didn't have any hyperlinks
Dim WebBook As Workbook, TargetBook As Workbook
Dim LinksCollection As New Collection, aHyperlink As Hyperlink, aLink
As Variant
Dim SheetIndex As Integer, SheetNo As Integer, HyperlinkAddress As
String
Dim anIndex As Integer
Dim MessagePrompt As String
'initiate the result of the function to 0; assume unexpected error
fncGetWebLinks = 0
'turn screenupdating off; display message to statusbar
Application.StatusBar = "Downloading Web Links... Please Wait"
Application.ScreenUpdating = False
'download the specified html file
On Error Resume Next
Application.DisplayAlerts = False
Set WebBook = Workbooks.Open(FileName:=URLocation)
Application.DisplayAlerts = True
If WebBook Is Nothing Then: fncGetWebLinks = (-1): GoTo ExitFunction
'Get all unique hyperlinks from the WebBook in the LinksCollection
For Each aHyperlink In WebBook.Worksheets(1).Hyperlinks
On Error Resume Next
If Not Left(aHyperlink.Address, 1) = "/" Then
HyperlinkAddress = aHyperlink.Address
Else
HyperlinkAddress = URLocation & aHyperlink.Address
End If
If Not UCase(Left(HyperlinkAddress, 10)) = "JAVASCRIPT" Then
If Not InStr(1, HyperlinkAddress, "/") = 0 Then
LinksCollection.Add HyperlinkAddress, CStr(HyperlinkAddress)
End If
End If
Next aHyperlink
'create a new workbook to put the hyperlinks
If Not LinksCollection.Count = 0 Then
On Error GoTo ExitFunction
Set TargetBook = Workbooks.Add
TargetBook.Windows(1).Caption = "Web Links"
SheetNo = TargetBook.Worksheets.Count
'leave only one worksheet in the new workbook
If SheetNo > 1 Then
Application.DisplayAlerts = False
For SheetIndex = 2 To SheetNo
TargetBook.Worksheets(SheetIndex).Delete
Next SheetIndex
Application.DisplayAlerts = True
End If
TargetBook.Worksheets(1).Name = "Web Links"
TargetBook.Activate
Else 'if there are no links
fncGetWebLinks = (-2)
GoTo ExitFunction
End If
'put the collected hyperlinks in the TargetBook
anIndex = 4
With TargetBook.Worksheets(1)
For Each aLink In LinksCollection
Cells(anIndex, 2).Value = aLink
.Hyperlinks.Add Anchor:=Cells(anIndex, 2), Address:=aLink
anIndex = anIndex + 1
Next aLink
'format the worksheet
.Columns(2).AutoFit
.Cells.Interior.ColorIndex = 2
.Cells(2, 2).Value = "Links collected from: " & URLocation
.Cells(2, 2).Font.Bold = True
End With
'close the WebBook
Application.DisplayAlerts = False
WebBook.Close SaveChanges:=False
Application.DisplayAlerts = True
TargetBook.Activate
'display the result
Application.ScreenUpdating = True
'the function was completed succesfully
fncGetWebLinks = 1
ExitFunction:
On Error Resume Next
Application.DisplayAlerts = False
WebBook.Close SaveChanges:=False
Application.DisplayAlerts = True
Application.StatusBar = False
On Error GoTo 0
Set aHyperlink = Nothing
Set LinksCollection = Nothing
Set WebBook = Nothing
Set TargetBook = Nothing
End Function
Sub test1_fncGetWebLinks()
Debug.Print
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks2.htm")
Debug.Print
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks3.htm")
Debug.Print
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks6.htm")
Debug.Print fncGetWebLinks("http://www.cnn.com")
Debug.Print fncGetWebLinks("http://www.bbc.co.uk")
End Sub
------------------------------------------------------------------------------------------------------------------
HTH
Stratos
Stratos Malasiotis
This is a slightly more improved and debugged version
It still needs more work but it requires experimentation.
This versio produces two columns : one with the names and the other with
the respective links
-----------------------------------------------------------------------------------
Option Explicit
Public Function fncGetWebLinks(URLocation As String) As Integer
'downloads a file from a specified URL and puts any links in the a new
file
'returns: 1 - success: all hyperlinks have been collected successfully
' 0 - failure: unexpected error
' -1 - failure: the target URL could not be opened
' -2 - failure: the target URL didn't have any hyperlinks
Dim WebBook As Workbook, TargetBook As Workbook, TempHyperName As
String
Dim LinksCollection As New Collection, aHyperlink As Hyperlink, aLink
As Variant
Dim SheetIndex As Integer, SheetNo As Integer, HyperlinkAddress As
String
Dim anIndex As Integer, TempArray As Variant
'initiate the result of the function to 0; assume unexpected error
fncGetWebLinks = 0
'turn screenupdating off; display message to statusbar
Application.StatusBar = "Downloading Web Links... Please Wait"
Application.ScreenUpdating = False
'download the specified html file
On Error Resume Next
Application.DisplayAlerts = False
Set WebBook = Workbooks.Open(FileName:=URLocation)
Application.DisplayAlerts = True
If WebBook Is Nothing Then: fncGetWebLinks = (-1): GoTo ExitFunction
'Get all unique hyperlinks from the WebBook in the LinksCollection
For Each aHyperlink In WebBook.Worksheets(1).Hyperlinks
On Error Resume Next
If UCase(Left(aHyperlink.Address, 5)) = "HTTP:" Or _
UCase(Left(aHyperlink.Address, 7)) = "MAILTO:" Or _
UCase(Left(aHyperlink.Address, 5)) = "NEWS:" Then
HyperlinkAddress = aHyperlink.Address
ElseIf InStr(1, aHyperlink.Address, "/") = 0 Then
For anIndex = Len(URLocation) To 1 Step -1
If Mid(URLocation, anIndex, 1) = "/" Then Exit For
TempHyperName = Left(URLocation, anIndex - 1)
Next anIndex
HyperlinkAddress = TempHyperName & aHyperlink.Address
Else
HyperlinkAddress = URLocation & IIf(Left(aHyperlink.Address, 1)
= "/", aHyperlink.Address, "/" & aHyperlink.Address)
End If
If Not UCase(Left(aHyperlink.Address, 10)) = "JAVASCRIPT" Then
HyperlinkAddress = "{" & Chr(34) & HyperlinkAddress &
""",""" & aHyperlink.Range.Value & Chr(34) & "}"
LinksCollection.Add HyperlinkAddress,
CStr(aHyperlink.Address)
'LinkNameCollection.Add aHyperlink.Range.Value,
CStr(aHyperlink.Name)
End If
Next aHyperlink
'create a new workbook to put the hyperlinks
If Not LinksCollection.Count = 0 Then
' On Error GoTo ExitFunction
Set TargetBook = Workbooks.Add
TargetBook.Windows(1).Caption = "Web Links"
SheetNo = TargetBook.Worksheets.Count
'leave only one worksheet in the new workbook
If SheetNo > 1 Then
Application.DisplayAlerts = False
For SheetIndex = 2 To SheetNo
TargetBook.Worksheets(SheetIndex).Delete
Next SheetIndex
Application.DisplayAlerts = True
End If
TargetBook.Worksheets(1).Name = "Web Links"
TargetBook.Activate
Else 'if there are no links
fncGetWebLinks = (-2)
GoTo ExitFunction
End If
'put the collected hyperlinks in the TargetBook
With TargetBook.Worksheets(1)
anIndex = 4
For Each aLink In LinksCollection
TempArray = Evaluate(aLink)
Cells(anIndex, 3).Value = TempArray(1)
.Hyperlinks.Add Anchor:=Cells(anIndex, 3),
Address:=TempArray(1)
Cells(anIndex, 2).Value = TempArray(2)
Cells(anIndex, 2).Font.Bold = True
anIndex = anIndex + 1
Next aLink
'format the worksheet
.Columns(2).AutoFit: .Columns(3).AutoFit
.Cells.Interior.ColorIndex = 2
.Cells(2, 2).Value = "Links collected from: " & URLocation
.Cells(2, 2).Font.Bold = True
.Cells(2, 2).Font.Size = 12
End With
'close the WebBook
Application.DisplayAlerts = False
WebBook.Close SaveChanges:=False
Application.DisplayAlerts = True
TargetBook.Activate
'display the result
Application.ScreenUpdating = True
'the function was completed succesfully
fncGetWebLinks = 1
ExitFunction:
On Error Resume Next
Application.DisplayAlerts = False
WebBook.Close SaveChanges:=False
Application.DisplayAlerts = True
Application.StatusBar = False
On Error GoTo 0
Set aHyperlink = Nothing
Set LinksCollection = Nothing
Set WebBook = Nothing
Set TargetBook = Nothing
End Function
ub
Sub test2_fncGetWebLinks()
Debug.Print
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks2.htm")
Debug.Print
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks3.htm")
Debug.Print
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks6.htm")
Debug.Print fncGetWebLinks("http://www.cnn.com")
Debug.Print fncGetWebLinks("http://www.bbc.co.uk")
End Sub
--------------------------------------------------------------------
Regards,
Stratos
Hans Jeucken
i will test it tomorrow.
Great work
The project by the way is ready
Thanks again.
"Stratos Malasiotis" <ie...@csv.warwick.ac.uk> schreef in bericht
news:396E03AE...@csv.warwick.ac.uk...
David McRitchie
I already used to verify the index order on my xlindex.htm page
it was in better shape than I thought and is in better shape now.
Also might add CNN is a lot easier to read without all the garbage.
Generates in Col
B) description
C) hyperlink
I used one of my open toolbar buttons (at least for now)
Sub GenLinksForWs()
fncGetWebLinks (ActiveCell.Value)
End Sub
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Hans Jeucken
Tested it
works perfectly
thanks a miljon
Stange problem tho, again i have to enter a password etc.
I think it is a excel 2000 problem
Thanks again
Stratos Malasiotis
I'm glad that you fid that function useful.
As regards the first question:
if you use:
Debug.Print
fncGetWebData(URLocation:="http://www.warwick.ac.uk/research/rae/guide.html",
-
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls", _
TargetTableAddress:="C10")
you need the brackets,
but if you just execute without receiving the output of the function,
like:
fncGetWebData
URLocation:="http://www.warwick.ac.uk/research/rae/guide.html", -
SourceTableAddress:="B10:F36", _
TargetBookFullName:="d:\tempfile.xls", _
TargetTableAddress:="C10"
you don't need the brackets.
That's a general VB rule; nothing to do with function
As regards the second question:
I don't think that it is the function that fails there; it just has its
limitations and you'll have to work around them in the calling procedure
or improve the functionality of the function including more code.
If you describe to me exactly what are you trying to do, I'll try to
give you a solution.
Best Regards,
Stratos
Peter Jamieson wrote:
>
> G'day Stratos,
> Using Win95/Office97
> Your function is a brilliant piece of work much appreciated!
> One snag though: when I tried to run the test subs such as the one below I
> got a "Compile Error: Expected:=" error message.
> When I removed the brackets from around the function parameters it ran OK
> and passed data fast to a closed workbook.
> I could not get any data into an open workbook though, even the WebData one.
> Copied and pasted the code and triple checked and some.
> What gives?
> Cheers, Peter J.
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> snip.......
Stratos Malasiotis
I'm glad that you like the fncGetWebLinks function; it's cool isn't it.
Be careful though, because occasionally (1-2%) it does not translate the
links correctly.
As regards that password problem:
Is that site that you are trying to access public ? Can you give me the
address so I can try to find a workaround?
Regards,
Stratos
David McRitchie
Each of the sites you had in your example are asking for a
username and password.
Though it appears we can hit the [Cancel] button and continue.
Expect it is an FTP usage but do not see "FTP" in the code.
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks2.htm")
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks3.htm")
fncGetWebLinks("http://www.j-walk.com/ss/excel/links/xllinks6.htm")
fncGetWebLinks("http://www.cnn.com")
fncGetWebLinks("http://www.bbc.co.uk")
HTH, (am using Excel 2000)
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Stratos Malasiotis <ie...@csv.warwick.ac.uk> wrote ...
> As regards that password problem:
> Is that site that you are trying to access public ? Can you give me the
> address so I can try to find a workaround?
> Hans Jeucken wrote:
Stratos Malasiotis
I don't experience that behaviour in XL97.
Does using displayAlerts=False or something similar help?
Stratos
David McRitchie
You already have turned off Display alerts on the OPEN so that
is not the problem. Area of code identified is the OPEN.
'download the specified html file
On Error Resume Next
MsgBox "aaa" 'added for testing....
Application.DisplayAlerts = False
Set WebBook = Workbooks.Open(filename:=URLocation)
MsgBox "bbb" 'added for testing ....
'Application.DisplayAlerts = True
I get a transferring dialog box, then I get the following dialog box:
Please enter your authentication information:
Resource: http://www.j-walk.com/ss/excel/links/xllinks6.htm
User name:
Password:
[x] Save the password in your password listing
Found same problems (search: authentication & password) both without solutions
in
http://www.deja.com/=dnc/getdoc.xp?AN=529198627 Ola Hallengren 99/09/24
http://www.deja.com/=dnc/getdoc.xp?AN=578808754 Hessel Flach 00/01/28
Ola indicates that only Excel 2000 users experience the problem.
MS KB search led to this
OFF2000: Link to Office Document Produces Password Prompt (Q225234).
http://support.microsoft.com/support/kb/articles/q225/2/34.asp
Don't know what to make of that as to how it might relate.
My system: WinNT 4.0 WorkStation,
Office 2000 with SR-1 applied just received it in mail this week
-- Q245025 OFF2000: How to Obtain and Install Microsoft
Office 2000 Service Release 1/1a (SR-1/SR-1a)
**The SR-1 update CD can be ordered without charge**
HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Stratos Malasiotis <ie...@csv.warwick.ac.uk> wrote in message
news:396F42EA...@csv.warwick.ac.uk...
Peter Jamieson
G'day Stratos,
Using Win95/Office97
Your function is a brilliant piece of work much appreciated!
One snag though: when I tried to run the test subs such as the one below I
got a "Compile Error: Expected:=" error message.
When I removed the brackets from around the function parameters it ran OK
and passed data fast to a closed workbook.
I could not get any data into an open workbook though, even the WebData one.
Copied and pasted the code and triple checked and some.
What gives?
Cheers, Peter J.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
snip.......