Update file daily from new entries in folder

[nthedog]

Hi Excel Wizards,

Hope 2022 is off to a great start for all of you and everyone around you is well!

I'm pretty new to VBA and PowerQuery and have a fairly straightforward question I think. I'm running Office365 on Windows 10 Enterprise.

So, I receive a daily e-mail with attached excel file, where I manually extract data (strings) to another excel file. I have to omit some collumns and also delete some rows (but this last bit is purely based on subjective criteria).

What I would want to be able to do is (manually) save attachement every day, to the same folder, but have my updating file extract the right collumns (without headers) from the new file in the folder everyday.
I've tried with PowerQuery, but then I still have to manually omit the columns I don't need, which means I can just as easily do everything manually.

So, in my ideal scenario I would save the attachement, open my running file and see that it has been updated with today's data.

Is there a way to do this?

Thanks so much already for taking the time to read this and for your answers!

All the best,

N.

 

[Dermot]

There is a way, but you need to be much more specific, to write VBA to do this
Think about every step you do manually, and write it down clearly.
1. how will Excel know which file to import? By date? By name?
2. When do you want Excel to import? Presumably it should check for a new file when you open it.
3. Which columns do you want?
4. Where do you want to put the data? In its own sheet (named how?) or at the bottom of previous data?
5. Do you want a history list of files imported?

 

[nthedog]

Hi Dermot,

Thanks for your swift reply and follow up questions!

1. Ideally Excel would see that a new file has been added to the folder, so by date? I save the attachements manually from my inbox to the folder and change the name to reflect the date.
2. Correct, when I open the sheet it should look to see if new files were added to the folder.
3. The daily attachement has the same layout, so the exact columns wouldn't change in terms of name and position.
4. The data needs to be added to the bottom of the updating sheet.
5. Not necessarily, but would be usefull to check if any files were skipped. The daily attachement has a date column as well, so that would be an easy way of manually tracking as well.

I'm not sure if this is specific enough?

Thanks again!

 

[Dermot]

I'll send you something tomorrow

 

[nthedog]

Thanks so much!

 

[Dermot]

Try this code, put it in a new VBA code module
Before trying to run it, read the instructions at top carefully, it requires 4 range names in your workbook
Then try it with a spare copy of your spreadsheet in case of error
It should run automatically when you open it

Option Explicit
Option Base 1

'the macro assumes data starts in A1 in each data file
'all data files (and only data files, nothing else) must be in the same folder (this is good practice anyway)

'the following range names are required in the spreadsheet (anywhere is fine)
'DataDirectory - the path of the folder with the data files, eg F:\Project\Data
'DataSheet - the name of the sheet to which data should be copied, eg Datasheet
'LastFilename - the macro will put the name of the latest imported file here, for your reference
'AddDate - the macro will use this to store the save date of the latest file imported, to help it decide which files need importing

Const TitleRows = 1 '<< set this number = how many title rows are at the top of the data being imported (these rows will be skipped)

Sub Auto_open() 'this will run when the spreadsheet opens
  UpdateData
End Sub

Sub UpdateData()
  Dim FS, Fold, f, lastFileDate, lastFilename, newFileDate
  lastFileDate = Range("AddDate")
  Set FS = CreateObject("Scripting.FileSystemObject")
  Set Fold = FS.GetFolder(Range("DataDirectory").Text) 'open data folder
  For Each f In Fold.Files 'loop through files
    If f.datelastmodified > lastFileDate Then 'import data if more recent than last update
      AddData f.Path
      If f.datelastmodified > newFileDate Then 'store the details of the most recent file imported
        newFileDate = f.datelastmodified
        Range("LastFilename") = f.Name
        Range("AddDate") = newFileDate
      End If
    End If
  Next f
End Sub

Sub AddData(f) 'adds data for file f
  Dim D, r, s
  Application.ScreenUpdating = False
  Set s = Sheets(Range("Datasheet").Text)
  Workbooks.Open f
  D = Cells(1, 1).CurrentRegion.Offset(TitleRows, 0) 'copy data range
  ActiveWorkbook.Close savechanges:=False
  With s
    r = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row + 1 'get next blank row at bottom
    .Cells(r, 1).Resize(UBound(D, 1), UBound(D, 2)) = D
  End With
End Sub

 

[nthedog]

Thanks for this!
Just quickly wanted to let you know I haven't had time to try it out, will post update asap!