Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
hi,
i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
Remove the ON ERROR statement and try again. Your code is failing and you
don't know it. "geebee" wrote: hi, i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
ok thanks..taking it a step further now...
i have the following: With myIE .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918200557769_ZZ-2DRLSD-20CH-20SENT.pdf" .... i would like to know if there is a way i can have something like : ..navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918" & %(like 0 to 10) & "_ZZ-2DRLSD-20CH-20SENT.pdf" just in case i do not know the exact URL? or is there any way i can LOOP through numbers/possibilities to arrive at the "200557769" part which comes before the "_ZZ? thanks in advance, geebee "Joel" wrote: Remove the ON ERROR statement and try again. Your code is failing and you don't know it. "geebee" wrote: hi, i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
You may be able to get filename by using a FTP command. Try it manually
first to see if it works. For example. Not all websites allow FTP access or require a password to get access. Some have Annoymous account where the user name is iether FTP or Annoymous. Usual pasword for Anoymous accounts are FTP or nothing. Some sittes give messages to tell you the password. See example of Microsofts FTP account below. The DIR command will give you a list of files in a directory. changing directories is equivalent to changing directories on a PC. Each forwarrd slash is a subdirectory. 1) Open FTP from start button - Start - Run - FTP 2) Enter URL type Open <CR to: FTP.microsoft.com User <ftp.microsoft.com:<none: FTP <cr Annoyymous access allowed, send identity (e-mail name) as password Password: Enter your e-mail address Annoymous user logged in ftpdir You can use the following commands in FTP HELP - gives list of all command DIR - lists of files in directory CD - Change directory DIR - List file names "geebee" wrote: ok thanks..taking it a step further now... i have the following: With myIE .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918200557769_ZZ-2DRLSD-20CH-20SENT.pdf" ... i would like to know if there is a way i can have something like : .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918" & %(like 0 to 10) & "_ZZ-2DRLSD-20CH-20SENT.pdf" just in case i do not know the exact URL? or is there any way i can LOOP through numbers/possibilities to arrive at the "200557769" part which comes before the "_ZZ? thanks in advance, geebee "Joel" wrote: Remove the ON ERROR statement and try again. Your code is failing and you don't know it. "geebee" wrote: hi, i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
well it is an https site so i cant ge tthis to work. ok i just tried to
validate url based on https site, but i am getting an error message that says runtime error -2147012851 the certificate authority is invalid. not sure how to handle this. i think it has something to do with having the certificate installed. but how can i deal with this in automation? is there a better way? thanks in advance, geebee "Joel" wrote: You may be able to get filename by using a FTP command. Try it manually first to see if it works. For example. Not all websites allow FTP access or require a password to get access. Some have Annoymous account where the user name is iether FTP or Annoymous. Usual pasword for Anoymous accounts are FTP or nothing. Some sittes give messages to tell you the password. See example of Microsofts FTP account below. The DIR command will give you a list of files in a directory. changing directories is equivalent to changing directories on a PC. Each forwarrd slash is a subdirectory. 1) Open FTP from start button - Start - Run - FTP 2) Enter URL type Open <CR to: FTP.microsoft.com User <ftp.microsoft.com:<none: FTP <cr Annoyymous access allowed, send identity (e-mail name) as password Password: Enter your e-mail address Annoymous user logged in ftpdir You can use the following commands in FTP HELP - gives list of all command DIR - lists of files in directory CD - Change directory DIR - List file names "geebee" wrote: ok thanks..taking it a step further now... i have the following: With myIE .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918200557769_ZZ-2DRLSD-20CH-20SENT.pdf" ... i would like to know if there is a way i can have something like : .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918" & %(like 0 to 10) & "_ZZ-2DRLSD-20CH-20SENT.pdf" just in case i do not know the exact URL? or is there any way i can LOOP through numbers/possibilities to arrive at the "200557769" part which comes before the "_ZZ? thanks in advance, geebee "Joel" wrote: Remove the ON ERROR statement and try again. Your code is failing and you don't know it. "geebee" wrote: hi, i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
You shouldn't be using https instead replace with ftp. They are different
protocols. https is hyper-text transfer protocol and ftp is file transfer protocol. Because FTP has a password authorization, you may not need a certificate. "geebee" wrote: well it is an https site so i cant ge tthis to work. ok i just tried to validate url based on https site, but i am getting an error message that says runtime error -2147012851 the certificate authority is invalid. not sure how to handle this. i think it has something to do with having the certificate installed. but how can i deal with this in automation? is there a better way? thanks in advance, geebee "Joel" wrote: You may be able to get filename by using a FTP command. Try it manually first to see if it works. For example. Not all websites allow FTP access or require a password to get access. Some have Annoymous account where the user name is iether FTP or Annoymous. Usual pasword for Anoymous accounts are FTP or nothing. Some sittes give messages to tell you the password. See example of Microsofts FTP account below. The DIR command will give you a list of files in a directory. changing directories is equivalent to changing directories on a PC. Each forwarrd slash is a subdirectory. 1) Open FTP from start button - Start - Run - FTP 2) Enter URL type Open <CR to: FTP.microsoft.com User <ftp.microsoft.com:<none: FTP <cr Annoyymous access allowed, send identity (e-mail name) as password Password: Enter your e-mail address Annoymous user logged in ftpdir You can use the following commands in FTP HELP - gives list of all command DIR - lists of files in directory CD - Change directory DIR - List file names "geebee" wrote: ok thanks..taking it a step further now... i have the following: With myIE .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918200557769_ZZ-2DRLSD-20CH-20SENT.pdf" ... i would like to know if there is a way i can have something like : .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918" & %(like 0 to 10) & "_ZZ-2DRLSD-20CH-20SENT.pdf" just in case i do not know the exact URL? or is there any way i can LOOP through numbers/possibilities to arrive at the "200557769" part which comes before the "_ZZ? thanks in advance, geebee "Joel" wrote: Remove the ON ERROR statement and try again. Your code is failing and you don't know it. "geebee" wrote: hi, i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
Can you map the network drive on you PC. Go to Window Explorer (not Internet
Explorer). Menu Tools - Map Network Drive. Once this is done then you can use a simple DIR("*.*") to get the filenames. "geebee" wrote: well i tried it manually and it says "unknown host". so i may have to go with the looping thing i had in mind. any ideas regarding that looping thing i mentioned? thanks in advance, geebee "Joel" wrote: You shouldn't be using https instead replace with ftp. They are different protocols. https is hyper-text transfer protocol and ftp is file transfer protocol. Because FTP has a password authorization, you may not need a certificate. "geebee" wrote: well it is an https site so i cant ge tthis to work. ok i just tried to validate url based on https site, but i am getting an error message that says runtime error -2147012851 the certificate authority is invalid. not sure how to handle this. i think it has something to do with having the certificate installed. but how can i deal with this in automation? is there a better way? thanks in advance, geebee "Joel" wrote: You may be able to get filename by using a FTP command. Try it manually first to see if it works. For example. Not all websites allow FTP access or require a password to get access. Some have Annoymous account where the user name is iether FTP or Annoymous. Usual pasword for Anoymous accounts are FTP or nothing. Some sittes give messages to tell you the password. See example of Microsofts FTP account below. The DIR command will give you a list of files in a directory. changing directories is equivalent to changing directories on a PC. Each forwarrd slash is a subdirectory. 1) Open FTP from start button - Start - Run - FTP 2) Enter URL type Open <CR to: FTP.microsoft.com User <ftp.microsoft.com:<none: FTP <cr Annoyymous access allowed, send identity (e-mail name) as password Password: Enter your e-mail address Annoymous user logged in ftpdir You can use the following commands in FTP HELP - gives list of all command DIR - lists of files in directory CD - Change directory DIR - List file names "geebee" wrote: ok thanks..taking it a step further now... i have the following: With myIE .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918200557769_ZZ-2DRLSD-20CH-20SENT.pdf" ... i would like to know if there is a way i can have something like : .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918" & %(like 0 to 10) & "_ZZ-2DRLSD-20CH-20SENT.pdf" just in case i do not know the exact URL? or is there any way i can LOOP through numbers/possibilities to arrive at the "200557769" part which comes before the "_ZZ? thanks in advance, geebee "Joel" wrote: Remove the ON ERROR statement and try again. Your code is failing and you don't know it. "geebee" wrote: hi, i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
hi,
not sure what you mean by maping the network drive. i tried that but not exactly sure what to do. especially since it is an https site. i hate to not be able to figure this out because ive been working on it for almost 3 months now. im trying to be creative and think of different approaches. i even though about using the IE.navigate when the exact url is not known. but not even sure how i would do that. i even tried validating the https site, but it looks like you have to have a certificate instealled for the site each time its opened for the day or whatever. im not sure how i would automate installing a certificate. im hoping for some ideas. thanks in advance, geebee "Joel" wrote: Can you map the network drive on you PC. Go to Window Explorer (not Internet Explorer). Menu Tools - Map Network Drive. Once this is done then you can use a simple DIR("*.*") to get the filenames. "geebee" wrote: well i tried it manually and it says "unknown host". so i may have to go with the looping thing i had in mind. any ideas regarding that looping thing i mentioned? thanks in advance, geebee "Joel" wrote: You shouldn't be using https instead replace with ftp. They are different protocols. https is hyper-text transfer protocol and ftp is file transfer protocol. Because FTP has a password authorization, you may not need a certificate. "geebee" wrote: well it is an https site so i cant ge tthis to work. ok i just tried to validate url based on https site, but i am getting an error message that says runtime error -2147012851 the certificate authority is invalid. not sure how to handle this. i think it has something to do with having the certificate installed. but how can i deal with this in automation? is there a better way? thanks in advance, geebee "Joel" wrote: You may be able to get filename by using a FTP command. Try it manually first to see if it works. For example. Not all websites allow FTP access or require a password to get access. Some have Annoymous account where the user name is iether FTP or Annoymous. Usual pasword for Anoymous accounts are FTP or nothing. Some sittes give messages to tell you the password. See example of Microsofts FTP account below. The DIR command will give you a list of files in a directory. changing directories is equivalent to changing directories on a PC. Each forwarrd slash is a subdirectory. 1) Open FTP from start button - Start - Run - FTP 2) Enter URL type Open <CR to: FTP.microsoft.com User <ftp.microsoft.com:<none: FTP <cr Annoyymous access allowed, send identity (e-mail name) as password Password: Enter your e-mail address Annoymous user logged in ftpdir You can use the following commands in FTP HELP - gives list of all command DIR - lists of files in directory CD - Change directory DIR - List file names "geebee" wrote: ok thanks..taking it a step further now... i have the following: With myIE .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918200557769_ZZ-2DRLSD-20CH-20SENT.pdf" ... i would like to know if there is a way i can have something like : .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918" & %(like 0 to 10) & "_ZZ-2DRLSD-20CH-20SENT.pdf" just in case i do not know the exact URL? or is there any way i can LOOP through numbers/possibilities to arrive at the "200557769" part which comes before the "_ZZ? thanks in advance, geebee "Joel" wrote: Remove the ON ERROR statement and try again. Your code is failing and you don't know it. "geebee" wrote: hi, i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate url
You can mapped Network drives to your PC assigning the a drive letter like
H:. All large companies and many smaller companies have common drives that ae not located on individual PC's. The drives usually require a user name and password for access depending how the permission are set for the drives. Access to these mapped drives look exactly like any local drive on your PC. Below is the help from Windows XP onNetwrok connections. One you set up the Network connection then you can map a Drive to the connection. Conections can be do another computer or a server. Once the connection is made you can located the files using the DIR command Folder = "C:\Reports\" FName = Dir(Folder & "*.xls") do while FName < "" 'Enter your code here FName = Dir() loop Connecting to a remote computer or networkNetwork Connections provides connectivity between your computer and the Internet, a network, or another computer. Open Network Connections in Control Panel. In Network Connections, double-click the connection you want. Notes To open Network Connections, click Start, click Control Panel, and then double-click Network Connections. If you are connecting to your local area network, the network connection connects automatically. If you need to create a network connection, in Network Connections, under Wizard, double-click Make New Connection. For information about using Network Connections, click the Help menu in Network Connections. For more information, click Related Topics. From you "geebee" wrote: hi, not sure what you mean by maping the network drive. i tried that but not exactly sure what to do. especially since it is an https site. i hate to not be able to figure this out because ive been working on it for almost 3 months now. im trying to be creative and think of different approaches. i even though about using the IE.navigate when the exact url is not known. but not even sure how i would do that. i even tried validating the https site, but it looks like you have to have a certificate instealled for the site each time its opened for the day or whatever. im not sure how i would automate installing a certificate. im hoping for some ideas. thanks in advance, geebee "Joel" wrote: Can you map the network drive on you PC. Go to Window Explorer (not Internet Explorer). Menu Tools - Map Network Drive. Once this is done then you can use a simple DIR("*.*") to get the filenames. "geebee" wrote: well i tried it manually and it says "unknown host". so i may have to go with the looping thing i had in mind. any ideas regarding that looping thing i mentioned? thanks in advance, geebee "Joel" wrote: You shouldn't be using https instead replace with ftp. They are different protocols. https is hyper-text transfer protocol and ftp is file transfer protocol. Because FTP has a password authorization, you may not need a certificate. "geebee" wrote: well it is an https site so i cant ge tthis to work. ok i just tried to validate url based on https site, but i am getting an error message that says runtime error -2147012851 the certificate authority is invalid. not sure how to handle this. i think it has something to do with having the certificate installed. but how can i deal with this in automation? is there a better way? thanks in advance, geebee "Joel" wrote: You may be able to get filename by using a FTP command. Try it manually first to see if it works. For example. Not all websites allow FTP access or require a password to get access. Some have Annoymous account where the user name is iether FTP or Annoymous. Usual pasword for Anoymous accounts are FTP or nothing. Some sittes give messages to tell you the password. See example of Microsofts FTP account below. The DIR command will give you a list of files in a directory. changing directories is equivalent to changing directories on a PC. Each forwarrd slash is a subdirectory. 1) Open FTP from start button - Start - Run - FTP 2) Enter URL type Open <CR to: FTP.microsoft.com User <ftp.microsoft.com:<none: FTP <cr Annoyymous access allowed, send identity (e-mail name) as password Password: Enter your e-mail address Annoymous user logged in ftpdir You can use the following commands in FTP HELP - gives list of all command DIR - lists of files in directory CD - Change directory DIR - List file names "geebee" wrote: ok thanks..taking it a step further now... i have the following: With myIE .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918200557769_ZZ-2DRLSD-20CH-20SENT.pdf" ... i would like to know if there is a way i can have something like : .navigate "https://webaddressdotcom/secure/Save=/Sites/46/Reports/5005_46_16_AppServiceUser_20080918" & %(like 0 to 10) & "_ZZ-2DRLSD-20CH-20SENT.pdf" just in case i do not know the exact URL? or is there any way i can LOOP through numbers/possibilities to arrive at the "200557769" part which comes before the "_ZZ? thanks in advance, geebee "Joel" wrote: Remove the ON ERROR statement and try again. Your code is failing and you don't know it. "geebee" wrote: hi, i have the following: Function IsLink(url) Dim http As Object Dim xmlhttp As Object Set http = CreateObject("MSXML2.ServerXMLHTTP.4.0") Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") On Error Resume Next xmlhttp.Open "GET", url, False 'This alternative can return status 405 in some cases 'xmlhttp.Open "HEAD", url, False xmlhttp.Send "" 'You can get the status of the page, if you wish Status = xmlhttp.Status If Err.Number < 0 Or Status < 200 Then IsLink = False Else IsLink = True End If Set xmlhttp = Nothing then i have: Sub L() IsLink ("HTTP://www.microsoft.com/en/us/default.aspx") MsgBox Status End Sub am i doing something wrong? because i know the website exists, and it should be returning TRUE. thanks in advance, geebee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
validate | Excel Discussion (Misc queries) | |||
validate | Excel Programming | |||
Looking up a name and then being able to validate it | Excel Worksheet Functions | |||
Looking up a name and then being able to validate it | Excel Worksheet Functions | |||
VALIDATE WB NAME | Excel Programming |