vba下载并保存在C：\user [英] vba download and save in C:\user

问题描述

我希望Excel使用VBA从互联网下载文件。此文件是一个数据库，其中包含有关员工列表的信息。我希望将文件保存在C：\Users\用户名 \FCMMIS\

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

1. 使用VBA的Excel，从指定的网址下载文件。


2. * 下载的文件保存在C：\Users\用户名 \FCMMIS *


3. Excel通过VBA与下载的数据库链接以获取

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

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

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

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

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

但是，我在讨论仅操作数据库本身中的数据还是从已创建的工作表中使数据工作是否有效。

解决方案

以下代码来自 Matt Vidas的文章

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

 函数Download_File（ByVal vWebFile作为字符串，ByVal vLocalFile作为字符串）布尔值
昏暗oXMLHTTP作为对象，i作为长度，vFF作为长度，oResp（）作为字节
 
'您也可以设置引用。到Microsoft XML，然后将oXMLHTTP设置为MSXML2.XMLHTTP 
设置oXMLHTTP = CreateObject（ MSXML2.XMLHTTP）
 oXMLHTTP.Open GET，vWebFile，False'打开套接字以获取网站
 oXMLHTTP.Send'发送请求
 
'等待请求完成
 oXMLHTTP.readyState<> 4 
 DoEvents 
循环
 
 oResp = oXMLHTTP.responseBody'将结果作为字节数组返回
 
'创建本地文件并将结果保存到其中
 vFF = FreeFile 
如果Dir（vLocalFile）<> 然后杀死vLocalFile 
打开vLocalFile以二进制形式作为#vFF 
放入#vFF，，oResp 
关闭#vFF 
 
'清除内存
设置oXMLHTTP =没有
结束函数
 
 
 
 Sub Testing（）
 Download_File http://example.org/yourDataBaseFile， C：\用户\& Environ（用户名）&  \yourDataBaseFile 
结束子
  

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\

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.

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.

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.

EDIT (to be done after along with part 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.2: The printed data is then used for various functions in the worksheet.

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.

解决方案

The following code is sourced from an article by Matt Vidas

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

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

这篇关于vba下载并保存在C：\user的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！