时间戳

首页 > 解决方案 > 从 href 下载文件并循环所有文件以保存在 C 驱动器工作簿中的单独选项卡中

excel - 从 href 下载文件并循环所有文件以保存在 C 驱动器工作簿中的单独选项卡中

问题描述

尝试从 href 下载文件并通过循环将数据保存在 C 驱动器上工作表的单独选项卡中。

感谢 Tim Williams 帮助我将代码放入下载 href 文件。但是将下载的数据复制到excel的代码。这对我不起作用。

Sub download()

    Dim ie As InternetExplorer
    Dim el As Object, els As Object

    Set ie = New InternetExplorer
    With ie
        .Visible = True
        .Navigate2 "http://www.nafed-india.com/Home/ProcDispoDetails"

        While .Busy Or .readyState < 4: DoEvents: Wend
    End With

    'Note: querySelectorAll unless you only want one element
    Set els = ie.document.querySelectorAll("a[href^='/Documents/ProcurementDisposal']")
    For Each el In els
        Debug.Print el.href 

    Dim i As Long

    Workbooks.Open Filename:="C:\Users\Desktop\Sample.xlsx"

    For i = 1 To ThisWorkbook.Sheets.Count
    Workbooks.Worksheets(i).Range("a1").Select
    ActiveCell.FormulaR1C1 = el.herf(i)
    Next i

    ActiveWorkbook.Save
    ActiveWindow.Close
        Next
End Sub

标签： excelvba

解决方案

尝试这样的事情。调整以适合您的具体情况。

Sub download()

    Dim ie As InternetExplorer, i As Long
    Dim el As Object, els As Object, wb As Workbook, smpl as workbook

    Set smpl = Workbooks.Open(Filename:="C:\Users\Desktop\Sample.xlsx")

    Set ie = New InternetExplorer
    With ie
        .Visible = True
        .Navigate2 "http://www.nafed-india.com/Home/ProcDispoDetails"

        While .Busy Or .readyState < 4: DoEvents: Wend
    End With

    'Note: querySelectorAll unless you only want one element
    Set els = ie.document.querySelectorAll("a[href^='/Documents/ProcurementDisposal']")
    i = 1
    For Each el In els
        Debug.Print el.href 
        Set wb = Workbooks.Open(el.href)
        wb.sheets(1).Range("A1").CurrentRegion.Copy smpl.Sheets(i).Range("A1")
        wb.close false
        i = i +1
   Next el

   smpl.Save

End Sub

注意：接受任何对您有帮助的答案是有礼貌的 - 这有助于后来出现并查看您的问题以了解哪些回答有效。花几分钟时间为你以前的帖子做这些 - 如果你忽略了你可能会发现帮助枯竭......

推荐阅读