Excel中的VBA宏可保存单元格区域中的html文件 [英] VBA Macro in Excel to save html files from cells range

问题描述

我想使用Excel中的VBA宏将网页保存为html文件。但是，我是全新的VBA宏。为了下载和保存，我找到了此代码，它可以正常工作。

I want to save a web page in a html file using a VBA Macro in Excel. However, I'm totally new doing VBA Macros. For downloading and save I found this code and it works.

Option Explicit
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
  ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
  ByVal lpfnCB As Long) As Long


Public Sub Example1()
    DownloadFile "http://www.betbrain.pl/", "c:\p.html"
End Sub


Private Function DownloadFile(URL As String, LocalFilename As String) As Boolean
    'Thanks Mentalis:)
    Dim lngRetVal As Long
    lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
    If lngRetVal = 0 Then DownloadFile = True
End Function

但是我会怎么做除此之外，喜欢做的就是使URL地址和保存html文件的名称两个参数可供选择。我从代码中期望的是选择一系列包含URL的单元格，以及其他包含路径和名称的单元格来保存文件。我找到了以下示例，并尝试将这两个代码组合在一起，但最终我没有多次尝试成功。

But what I would like to do in addition, is to make the URL address and the name to save the html file two arguments to choose. What I expect from the code is to select a range of cells containing the URLs and others containing the path and names to save the file. I found the following example, and I have tried to combine both codes, but definitively I haven't had success in many tries.

Sub Proper_Case()
    'Updateby20150428
    Dim x As Range
    Dim Workx As Range

    On Error Resume Next

    xTitleId = "KutoolsforExcel"
    Set Workx = Application.Selection
    Set Workx = Application.InputBox("Range", xTitleId, Workx.Address, Type:=8)

    For Each x In Workx
        x.Value = Application.Proper(x.Value)
    Next
End Sub

请给我一些帮助。

在此先感谢Jaime。

Could you please give some help.
Thanks in advance, Jaime.

推荐答案

我认为这可行。我假设您的URL和目标文件范围相同，并且第一个URL和目标文件一起使用。换句话说，第一个URL保存到列表中的第一个目标，第二个URL保存到第二个目标，依此类推。

I think this could work. I'm assuming that your URL and Destination File ranges are the same size, and the first URL and Destination go together. In other words, the first URL saves to the first Destination in the list, second URL saves to second destination, etc.

Sub getURLS()
Dim x       As Range, urlRange As Range, saveToRange As Range
Dim i As Long

i = 1

Set urlRange = Application.InputBox("Range of URLs", "URLS", Type:=8)
Set saveToRange = Application.InputBox("Range of destinations", "Destinations", Type:=8)

For Each x In urlRange
    DownloadFile x.Value, saveToRange.Cells(i, 1).Value
    i = i + 1
Next

End Sub

这篇关于Excel中的VBA宏可保存单元格区域中的html文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！