logo标签列表

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

excelexcel-web-queryvba
3

有没有人能分享关于如何通过自动启动会话来自动化Ms.Excel 2007中的Web查询的知识？

我的问题是每次需要手动登录到Web查询……

你正在使用 Excel 的内置“Web Query”(qry)功能，对吗？ - AMissico
3个回答
0

使用C# COM接口可以通过它将任何数据注入Excel...... 使用以下搜索关键字在Google上搜索，您将获得数百个结果

using Excel = Microsoft.Office.Interop.Excel;

现在以上基本上只能在ASP.NET中实现。 否则，请使用相应的编程语言获取Excel接口。

行为变化和nullreference异常的生成

看看这个，您会有一些想法。

0
0

我也遇到了同样的问题...

通过一些 VBA 宏，我可以自动化数据传输... 但我仍然需要手动启动会话...

只需写入一些代码，就可以将数据自动传输到 Excel 中。您可以使用一些 do - loops 自动逐行写入每个数据。例如：

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
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接