如何自动化Web查询登录以在Microsoft Excel 2007中下载数据？

Is there anyone that can share knowledge about how to automate a web query in Ms.Excel 2007 by starting a session automatically?

是否有人可以通过自动启动会话来分享有关如何在Ms.Excel 2007中自动化Web查询的知识?

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

我的问题是我每次都需要手动登录网页查询....

3 个解决方案

#1

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

通过以下方式使用C#COM接口,可以将任何数据输入到EXCEL ......用以下搜索关键字点击谷歌,你将得到100的结果

using Excel = Microsoft.Office.Interop.Excel;

使用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.

现在上面的内容基本上只能在ASP.NET中使用。另外,使用相应的编程语言获得excel接口。

Change in behaviour & generation of nullreference exception

行为的改变和null引用异常的产生

see this u will get some idea..

看到这个你会得到一些想法..

#2

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.

根据我的经验,这是不可能的。 Excel的Web查询界面就没有这方面的功能。我已经通过VBA获取Web数据并将其粘贴到工作表中以自动执行登录部分。请参阅http://www.dailydoseofexcel.com/archives/2011/03/08/get-data-from-website-that-requires-a-login/,并务必阅读评论。

#3

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

使用一点Vba宏我可以自动化数据传输..但我仍然需要手动启动会话..

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;

只需一点代码就可以自动将数据转移到excel中,您可以使用一些do循环自动逐行记下每个数据。例如;

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

#1