vba 下载并保存在 C:\user

vba download and save in C:\user

提问by Sancho Almeda

I want excel, using VBA, to download a file from the internet. This file is a database with information about list of employees. I want the file saved in C:\Users\"name of user"\FCMMIS\

我想要使​​用 VBA 的 excel 从互联网下载文件。该文件是一个包含员工名单信息的数据库。我希望文件保存在 C:\Users\"用户名"\FCMMIS\

This is the step-by-step process that I want to achieve:

这是我想要实现的分步过程：

1. Excel using VBA, downloads a file from a specified web address.
2. *The downloaded file is saved in C:\Users\"name of user"\FCMMIS*
3. Excel links with the downloaded database via VBA to get data that may be required.

1. Excel 使用 VBA，从指定的网址下载文件。
2. *下载的文件保存在 C:\Users\"用户名"\FCMMIS*
3. Excel 通过 VBA 链接到下载的数据库以获取可能需要的数据。

As another note, the excel file in question will be used by different people with laptops. I am not sure if saving the file in the "User" folder is possible or should there needs to be permissions to be given in order to allow a save.

另请注意，有问题的 excel 文件将由不同的笔记本电脑用户使用。我不确定是否可以将文件保存在“用户”文件夹中，或者是否需要授予权限以允许保存。

I know that there are better solutions to this like writing a full application but due to the requirement, I am stuck with excel and VBA.

我知道有更好的解决方案，比如编写完整的应用程序，但由于需求，我坚持使用 excel 和 VBA。

EDIT (to be done after along with part 3):

编辑（与第 3 部分一起完成）：

3.1: Once the database is downloaded, then the excel file gets records from the employee table and prints them in a new worksheet in the same excel file.

3.1：下载数据库后，excel文件从employee表中获取记录并将它们打印在同一excel文件中的新工作表中。

3.2: The printed data is then used for various functions in the worksheet.

3.2：打印的数据然后用于工作表中的各种功能。

However, I am in a debate whether is it much efficient to just manipulate the data in the database itself or have it work from the created worksheet.

但是，我正在争论仅操作数据库本身中的数据或让它从创建的工作表中工作是否更有效率。

回答by Larry

The following code is sourced from an article by Matt Vidas

以下代码来自Matt Vidas 的一篇文章

And waiting for your answer to Point 3, no scope?

等待您对第 3 点的回答，没有范围？

Function Download_File(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
    Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte

    'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
    oXMLHTTP.Send 'send request

    'Wait for request to finish
    Do While oXMLHTTP.readyState <> 4
    DoEvents
    Loop

    oResp = oXMLHTTP.responseBody 'Returns the results as a byte array

    'Create local file and save results to it
    vFF = FreeFile
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As #vFF
    Put #vFF, , oResp
    Close #vFF

    'Clear memory
    Set oXMLHTTP = Nothing
End Function



Sub Testing()
    Download_File "http://example.org/yourDataBaseFile", "C:\Users\" & Environ("username") & "\yourDataBaseFile"
End Sub

回答by tyomitch

If the file in question is an excelworkbook you could do this:

如果有问题的文件是Excel工作簿，您可以执行以下操作：

With Workbooks.Open("http://example.org/yourfile.xls")
    .SaveAs "C:\Users\example\yourfile.xls"
    .Close
End With