How to automate a web query login to download data in Microsoft Excel 2007?

Is there anyone that can share knowledge about how to automate a web query in Ms.Excel 2007 by starting a sessi开发者_开发问答on automatically?

My problem is that I need to manually login through a web query each time....

USE C# COM INTERFACE FOR EXCEL THROUGH WHICH U CAN INJECT ANY DATA INTO EXCEL...... hit google with following search keywords and u will get 100's of result for it

using Excel = Microsoft.Office.Interop.Excel;

now the above is basically possible in ASP.NET ONLY. OTHERWISE get an interface for excel with your corresponding programming language.

Change in behaviour & generation of nullreference exception

see this u will get some idea..

In my experience, it's not possible. Excel's web query interface just doesn't have the facility to do it. I've resorted to getting the web data through VBA and pasting it into a sheet to automate the login portion. See http://www.dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/ and be sure to read the comments.

I am having the exact problem..

With a little Vba Macro i can automate the data transfer.. but i still have to start session manually..

With only a little code you can automate data trasfer into the excel, you can write down each data row by row automatically by using some do - loops. For example;

Do 

If getVars = "" or getVars = Null Then
    Exit Do
End If

Set RangeOfStyles = Range("A1:Z400")

'Clear the xlSheet
For Each Cell In RangeOfStyles
        On Error Resume Next
        ActiveWorkbook.Styles(Cell.Text).Delete
        ActiveWorkbook.Styles(Cell.NumberFormat).Delete
Next Cell

DoEvents
'Use a counter for detecting the range of recieved data in table
'This only works if there is nospace inside the recieved data
'Create a start point
i = 2

'Find the start point
'Will be used if there are some data already found..
'If the starting cell is not empty than start counting
If Cells(i, 2) <> ""  Then

    Do
        Do
            i = i + 1          '2 cause my data starts at column "B2" and row 2
        Loop Until Cells(i + 1, 2) = "" 'if next cell is empty than it ends here
        'im leaving an empty row to seperate each data
        'i must check the row after the empty row to find out if there are more data
        '+1 for empty cell and +1 for starting cell
        i = i + 2
    Loop Until Cells(i, 2) = ""

End If

'Now that we are ready we can paste our next data to the next rows of our worksheet
'Get ur url pasted to the excel
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://127.0.0.1" + getVars, _ 'I used your url here to make it more simpler
    Destination:=Range("B" & i, "I" & i))
    'Use this ability only if you need to gather a specific table in that page
    .WebSelectionType = "xlSpecifiedTables"
    'Webtables = "ChosenTable"
    .WebTables = "10"
    'The other attributes! Nothing fancy here..
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True   
End With


Loop