excel - VBA 从文件名每天都在变化的 URL 下载文件

Question

有人能帮我拔头发吗？我正在尝试从站点下载文件并将其复制到另一个位置。如果我使用下面注释掉的实际文件名，它就可以工作。可悲的是，文件名每天都在变化，我已经在单元格 B1(01 04 2016)中说明了这一点。无论如何，我收到“编译错误:需要常量表达式”并突出显示第一个“&”的错误。有没有人有任何可能有帮助的想法？

{

Option Explicit

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


`enter code here`Sub DownloadFileFromWeb()
Dim i As Integer

    Const strUrl As String = "https://test.com/sites/Field Completions Reported " & Range("B1") & ".xlsb"
    '"https://test.com/sites/Field Completions Reported 01 04 2016.xlsb"

    Dim strSavePath As String
    Dim returnValue As Long

    strSavePath = "C:\Users\1234\Desktop\Field Completions Reported.xlsb"
    returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

End Sub

}

Answer 1

Compile error: Constant expression required

您不能使用 Const与 Variable .当您使用 Const然后传递一些不会改变的东西。例如

Const a = "Sid" '<~~~ This will work
Const a = "Sid" & i '<~~ This will not work. i is a variable
Const a = "Sid" & Range("A1").Value  '<~~ This will not work. Range("A1") is a variable

将您的代码更改为此

Dim strUrl As String

'"https://test.com/sites/Field Completions Reported 01 04 2016.xlsb"
strUrl = "https://test.com/sites/Field Completions Reported " & _
         Range("B1").Value & ".xlsb"