Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 75
Default Macros for WEB Query with password protected site

HI,

I would like to extract data from site which has password protection.
(http://10.6.4.48/institution/Login.jsp) where user name and password needs
to be given. After passing login screen the screen is move to
(http://10.6.4.48/institution/welcome.jsp). The particular address has few
dropdown menu's and on selection of dropdown menu what I required the
address changes to (http://10.6.4.48/institution/TransactionReport.jsp).

On providing dates (from and to) and by pressing OK button the data is
displayed. I want these data but unable to write macro for the same.

The macro recorded does not include all above steps or it directly goes to
(http://10.6.4.48/institution/TransactionReport.jsp) and unable to get any
data.

Can anyone help me out to write macro for these.

Thank you


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 75
Default Macros for WEB Query with password protected site

HI

The URL mentioned by me is our internal office software URL. I would like to
extract data and import to excel. I have tried to write macro which is as
under;


Dim ie As Object
On Error GoTo 1
Set ie = CreateObject("InternetExplorer.Application")
With ie
.navigate "http://10.6.4.48/institution/Login.jsp"
Do While .busy: DoEvents: Loop
Do While .ReadyState < 4: DoEvents: Loop
With .Document.Forms(0)
.UID.Value = "GANESHS" 'Change to YOUR Login ID
.Pass.Value = "GANESHS" 'Change to YOUR password
.submit.Click
End With
.Visible = True
Date = Format$(Now(), "MM/DD/YYYY")
.navigate "http://10.6.4.48/institution/TransactionReport.jsp"
With .Document.Forms(0)
.txtFromDt.Value = Date
.txtToDt.Value = Date
.submit.Click
End With
End With

Exit Sub


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://10.6.4.48/institution/TransactionReport.jsp",
Destination:=Range( _
"A1"))
' .Name = "TransactionReport"
.Name = "AllEntries"

.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
' .WebTables = x
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


1: MsgBox "Unexpected Error, sorry."
ie.Quit
Set ie = Nothing


The code written by me opens a separate internet explorer and data is shown
at IE. The same cannot be imported to excel. The data which is downloaded
(with above code) shows the first page i.e. login and password.

How is it possible to get data which is appearing on IE with above code. OR
any other alternative. Please...

Thank you.



"Vijay Kotian" wrote:

HI,

I would like to extract data from site which has password protection.
(http://10.6.4.48/institution/Login.jsp) where user name and password needs
to be given. After passing login screen the screen is move to
(http://10.6.4.48/institution/welcome.jsp). The particular address has few
dropdown menu's and on selection of dropdown menu what I required the
address changes to (http://10.6.4.48/institution/TransactionReport.jsp).

On providing dates (from and to) and by pressing OK button the data is
displayed. I want these data but unable to write macro for the same.

The macro recorded does not include all above steps or it directly goes to
(http://10.6.4.48/institution/TransactionReport.jsp) and unable to get any
data.

Can anyone help me out to write macro for these.

Thank you


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Open file on password-protected FTP site pdberger Excel Programming 2 August 29th 07 08:34 PM
Open excel file from password protected Intranet site(IE) Dan McCollick Excel Programming 4 May 17th 06 07:37 PM
Retrieving data from a password protected site 147man Excel Programming 0 May 17th 05 11:17 AM
Retrieving data from a password protected web site 147man Excel Programming 0 May 5th 05 02:12 PM
Web query; protected site?!? Tom Excel Programming 2 February 17th 05 11:02 AM


All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"