Macro to search network folder for new excel files and copy sheets to active workbook

[wallen1605]

Hi all.

I have a specific requirement as follows:

Each day we receive a number of emails with 1 or a few excel attachments into a specific outlook account which outlook vba then saves the attachment/s to a network server folder and renames the files to todays date and time as well as the value in cell C3 from sheet1 in each file.

what I need to do, is create a macro to run from an active workbook which will check the network server folder at periodic times for new excel files added from outlook throughout the day, then if new files are found, copy sheet1 from each new file into the active workbook and rename the sheet to match the file name of the new excel file that the sheet was copied from. I will then use a barcode module to do further work once the sheets are imported.

I don`t know where to start with this, or if the above way is the best way to do this?

Your help would be greatly appreciated.

 

[offthelip]

This should do it, I have assume that the path to the server folder is in cell A1 of the active sheet but obviously you can edit directly in the VBA
Also to find the file I am just checking that the date the file was created was today date. You can obviously change this if you need to.
Note you must add the reference to Microsoft scripting runtime in the VBA window under tools references

Sub tsr()
    Dim FSO As New FileSystemObject
    ' set reference to Microsoft Scripting Runtime
    Dim myFolder As Folder
    Dim myFile As File
    SPath = Cells(1, 1)
wkbkname = ActiveWorkbook.Name


Set myFolder = FSO.GetFolder(SPath)

For Each myFile In myFolder.Files
   datec = myFile.DateCreated
   Namef = myFile.Name
   
   dd = Day(datec)
   dm = Month(datec)
   dy = Year(datec)
   Nowd = Now()
   Nd = Day(Nowd)
   Nm = Month(Nowd)
   ny = Year(Nowd)
   datec1 = dd & dm & dy
   now1 = Nd & Nm & ny
   
   If datec1 = now1 Then
   
   ' MsgBox (datec1 & "/" & now1 & " " & Namef)
    Workbooks.Open Filename:=SPath & "\" & Namef
    tempname = ActiveWorkbook.Name
    ActiveWorkbook.Sheets("Sheet1").Copy _
       after:=Workbooks(wkbkname).Sheets("Sheet1")
     ActiveSheet.Name = Namef
     
    Workbooks(tempname).Close
    
   End If
 
Next


End Sub

 

[wallen1605]

Many thanks for the code that`s great, just a few queries. Can i change the cell A1 to a static path e.g instead of cells(1, 1), can i state the server path such as "\\server\Excel Files"? Also how could i change the code to check last date modified rather than creation date, because the excel file template is used multiple times and will only have new information added on the same day it is sent to us (and put into the folder on the server)?

Ideally, would it be better to check the file name for the date and time? Because the file name will already have a date and time and a reference to the person sending us it.

 

[wallen1605]

Also, will this code check every so often for new files added to the server folder, as we receive files throughout the day?

Many thanks

 

[offthelip]

To change the code to static path you just need to put the path into Spath:
eg.

spath="C:\Username\MrExcel\Test"

For a network path I find the easiest way of getting the exact path is to put this code into a workbook, then save it on the network address and then run this macro which will put the path in to cell A1, then copy and paste the text from cell A1 into the code

Cells(1,1)=Activeworkbook.path

To change to the date modified just change :

datec=myFile.DateLastModified

To change to checking the files a number of times a day is quite a major change in particular determining which files have already been processed. there are a number of possible solutions: 1: Keep a list of which files have been processed in the main workbook. 2: Record the time the macro was last run and compare this with the time lastmodified. ( This might not be 100% reliable), 3: Move all processed files to a subdirectory
Also to change to checking the time and date in the filename is a completely different solution and depends on the format of the time and date in the filename because that will be a string. Quite possible though

 

[wallen1605]

Hi, that`s great thank you. I think option 3 would be best suited and move the processed files to another directory. How would I include this into the code you have given me earlier?

 

[offthelip]

To move the file just add this code under the workbook close:

  Workbooks(tempname).Close    
' add this code
 sourcepath = SPath & "\" & Namef
 Destpath = SPath & "\Processed\" & Namef
 Name sourcepath As Destpath

 

[wallen1605]

Excellent everything is working great, many thanks for your help.

 

[offthelip]

Glad to be of help, it is quite easy when you define your requirements so clearly, a lot of posters on this forum could learn from you.