如何仅从保存在预定义文件夹中的新Excel文件复制数据? [英] How to copy data from only the new excel files that are saved in a predefined folder?

问题描述

我想从存储在特定文件夹中的excel文件中复制特定范围并将其粘贴到另一个excel文件中.我能够这样做.但是，每次我运行代码时，它都从该文件夹中的第一个文件开始我只想复制以前未更新过的文件中的数据，有没有办法做到这一点? EG:

I want to copy specific range from excel files stored in a specific folder and paste it in another excel file.I am able to do so.However,every time i run the code it starts with the very first file in the folder.I want to copy data from only the files that haven't been updated before.Is there a way to do that? EG:

"file1.xlsx"和"file2.xlsx"在文件夹中.我想从给定文件中复制数据并将其粘贴到"NewFile.xlsm"中(我能够实现)，但是，如果我在文件夹中添加"file3.xlsx"和"file4.xlsx"，然后运行宏，它也会同时复制"file1.xlsx"和"file2.xlsx"中的数据.我希望它这次仅复制"file3.xlsx"和"file4.xlsx"中的数据，因为前两个文件中的数据已经存在已保存. (我的代码在下面给出)

"file1.xlsx" and "file2.xlsx" are in a folder. I want to copy data from the given files and paste it in "NewFile.xlsm" (I'm able to achieve this) However, if I add "file3.xlsx" and "file4.xlsx" in the folder and then run the macro, it copies data from "file1.xlsx" and "file2.xlsx" as well.I want it to copy data only from "file3.xlsx" and "file4.xlsx" this time as the data from previous 2 files is already saved. (The code i have is given below)

Path = "C:\Users\National\Desktop\TEST Codes\PO\Excel\"
Filename = Dir(Path & "*.xls")
Do While Filename <> ""

If Filename = "Z master for PO.xlsm" Then
Exit Sub
End If
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

Sheets("DETAILED").Range("A3:S15").Copy

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ActiveWorkbook.Close



Dim LASTROW As Long, WS As Worksheet, LS As Long
Set WS = Sheets("sheet1")
LASTROW = WS.Range("R" & Rows.Count).End(xlUp).Row + 1

WS.Range("A" & LASTROW).Select

ActiveSheet.Paste Destination:=WS.Range("A" & LASTROW)

Application.CutCopyMode = False

Filename = Dir()
Loop

Range("A7").Select

推荐答案

一种方法是查看DateLastAccessed属性或DateLastModified属性.这些都是File对象的属性，请参见此MS文档.

One way of doing this is by looking at the DateLastAccessed property, or the DateLastModified property. These are both properties of the File object, see this MS documentation.

您可以设置一个最小日期/时间，该日期/时间应排除不需要处理的文件.

You can set a minimum date/time, which should exclude the files you don't want processed.

请确保设置正确的参考

Option Explicit

Sub GoThroughFiles()

Dim Path As String, Filename As String,
Dim fso, fileinfo

Set fso = CreateObject("Scripting.FileSystemObject")
Path = "C:\Users\National\Desktop\TEST Codes\PO\Excel\"
Filename = Dir(Path & "*.xls")

Set fileinfo = fso.GetFile(Path & Filename)

Do While Len(Filename) > 0 
    If fileinfo.DateLastAccessed > DateAdd("n", -5, Now) 'If the file was last accessed less than 5 minutes ago
        'Do stuff with the file
    End If
    FileName = Dir()
Loop

End Sub

此外，避免同时使用Select和Activate，因为同时使用它们会使代码易于出错. 此处是有关如何避免这种情况的主题.紧接着，我添加了Option Explicit，以确保您避免其他由于拼写错误引起的错误.

Furthermore, avoid using Select and Activate as using both will make your code prone to errors. Here is a thread on how to avoid it. Next to that, I added Option Explicit which makes sure you avoid other errors caused by, for example, spelling mistakes.

这篇关于如何仅从保存在预定义文件夹中的新Excel文件复制数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！