VBA for multiple files with date - maximum values

[Them]

Hi guys,
New here..
I am trying to solve the famuos "index on closed files". I found out the hard way that INDIRECT did not work
I have multiple files (more than 100, and more will come every day), looking like this file name:
OOCPB0202Z mix data - 2021-08-11_2021-08-11.xls
OOCPB0202Z mix data - 2021-08-12_2021-08-12.xls


ONLY difference is the date stamp in the file name --> all files also look the same in respect to rows and columns when opening.
I have my "master sheet", where I have rows coresponding to the date of the data files above.
06-08-2021 - 07-08-2021 - 08-08-2021 etc. --> no problem to fix this date to look different.
Lets assume this is ROW 1 (and 06-08-2021 is in "A")


Here comes my problem (and the reason why I need VBA) for the dynamic solution:
I want, without opening the data files have the Maximum value of the following columns:
”AL”
”AN”
”AT”
”AR”
”BD"
”AZ”
”BB”


E.g. Maximum value from "AL" of file 06-08-2021, I want in Masters sheet A2, "AN" in A3 etc.
From 07-08-2021 then moving to B2 and B3.
I have tried different stuff, but I need some help here.


Can anybody help me?
Thanks!

[snb]

Integrate all those files into one (Why were they separated in the first place ?)
Do not correct a wrong structure by unnecessary and complicated code.
Alone in fairy tales it is possible to see what is inside without opening the door.

[Them]

snb, Thanks for you reply.
Problem is that I want to use this also in the future, and the data from the datalogger will be exported daily.
So.. I need this system and solution for everything to be easier :)
In 3 years I have 1000 files.

[p45cal]

Attach 4 files; 1 file being the file containing the code and the Master sheet, and 3 other sample data files.

[Them]

28838288392884028841

p45cal, thanks for your reply - and sorry about my late reply :)
I have attached 3 data files and the "master sheet" file.

Thanks for helping.

[p45cal]

I see these are all .xls files and not .xlsx or .xlsm; what version of Excel are you using?

[Them]

I am using Excel 2016 - I have made the Master sheet, but the data files are from a datalogger (which could be why) ? :)

[p45cal]

In the attached there is a macro blah.
It creates formulae in the cells by looking at cells B1, B2, B3, A6, A7, A9, A10, A11, A13, A14 and the dates in row 5, and nothing else.
The dates in row 5 should be real dates, it doesn't matter what format they show as.
The files corresponding to all the dates should exist, otherwise you'll get a dialogue box popping up for every cell whose filename it doesn't find.
Obviously, you'll need to adjust the value in cell B1.
There's a comment in the code regarding including a line of code to remove the formulae from the cells and replace with plain values.
The columns it will add formulae/values to is from column D to the rightmost cell in row 5 which has data in it, any sort of data.
Note the column headers in column A are now of the form 'AL' and not 'AL:AL'.
The first line of code, currently With Sheets("August") I realise may not remain the same; you can change this to act on the active sheet with With ActiveSheet, but be aware then that the active sheet MUST be the sheet you want the macro to act on. If not then it'll leave a mess. This includes if the active sheet is not in the same Master workbook!
I've made the assumption that the data in the xls files always start from row 5, and never go below row 65536.

Sub blah()
With Sheets("August") 'ActiveSheet
Set TopRightCell = .Cells(5, .Columns.Count).End(xlToLeft) '.Range("F5")
For Each cll In Intersect(Range(.Cells(6, 4), TopRightCell).EntireColumn, .Range("6:7,9:11,13:14")).Cells
datestr = Format(.Cells(5, cll.Column).Value, "yyyy-mm-dd") 'date string
fnPt2 = datestr & "_" & datestr & ".xls]" 'file name second part
cll.Formula = "=MAX('" & .Range("B1").Value & "[" & .Range("B2").Value & fnPt2 & .Range("B3").Value & "'!$" & .Cells(cll.Row, "A").Value & "$5:$" & .Cells(cll.Row, "A").Value & "$65536)"
'cll.Value = cll.Value 'include this line if you want plain values instead of formulae.
Next cll
End With
End Sub
I hope there's no problem with international variants of the formulae.

ps. With Excel 2016 I think you can install Power Query (aka Get & Transform Data) which is built in to later versions of Excel, which could mean you only have to point to the folder where all the files reside and a table will be created using the data from all the files it finds there, without using vba at all.

[Them]

I have tried the Power Query and it covers it all .
Thanks for your help!

[Them]

How do i assign the state "SOLVED"?

[p45cal]

I have tried the Power Query and it covers it all .
That was quick! I'd be interested in seeing the M-code.

[Them]

How do I share that M-code ? :)

[p45cal]

How do I share that M-code ? :)

https://docs.microsoft.com/en-us/power-query/share-query

[Them]

Sorry this is in Danish:


let
Kilde = Folder.Files("C:\Users\CHRISTM\Downloads\Solcelle"),
#"Skjulte filer er filtreret1" = Table.SelectRows(Kilde, each [Attributes]?[Hidden]? <> true),
#"Aktivér brugerdefineret funktion1" = Table.AddColumn(#"Skjulte filer er filtreret1", "Transformér fil fra Solcelle", each #"Transformér fil fra Solcelle"([Content])),
#"Omdøbte kolonner1" = Table.RenameColumns(#"Aktivér brugerdefineret funktion1", {"Name", "Source.Name"}),
#"Fjernede andre kolonner1" = Table.SelectColumns(#"Omdøbte kolonner1", {"Source.Name", "Transformér fil fra Solcelle"}),
#"Udvidet tabelkolonne1" = Table.ExpandTableColumn(#"Fjernede andre kolonner1", "Transformér fil fra Solcelle", Table.ColumnNames(#"Transformér fil fra Solcelle"(Eksempelfil))),
#"Ændret type" = Table.TransformColumnTypes(#"Udvidet tabelkolonne1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type text}, {"Column74", type text}, {"Column75", type text}, {"Column76", type text}, {"Column77", type text}, {"Column78", type text}, {"Column79", type text}, {"Column80", type text}, {"Column81", type text}, {"Column82", type text}, {"Column83", type text}, {"Column84", type text}, {"Column85", type text}, {"Column86", type text}, {"Column87", type text}, {"Column88", type text}, {"Column89", type text}, {"Column90", type text}, {"Column91", type text}, {"Column92", type text}, {"Column93", type text}, {"Column94", type text}, {"Column95", type text}, {"Column96", type text}, {"Column97", type text}, {"Column98", type text}, {"Column99", type text}, {"Column100", type text}, {"Column101", type text}, {"Column102", type text}, {"Column103", type text}, {"Column104", type text}, {"Column105", type text}, {"Column106", type text}, {"Column107", type text}, {"Column108", type text}, {"Column109", type text}, {"Column110", type text}, {"Column111", type text}, {"Column112", type text}, {"Column113", type text}, {"Column114", type text}, {"Column115", type text}, {"Column116", type text}, {"Column117", type text}, {"Column118", type text}, {"Column119", type text}, {"Column120", type text}, {"Column121", type text}, {"Column122", type text}, {"Column123", type text}, {"Column124", type text}, {"Column125", type text}, {"Column126", type text}, {"Column127", type text}, {"Column128", type text}, {"Column129", type text}, {"Column130", type text}, {"Column131", type text}, {"Column132", type text}, {"Column133", type text}, {"Column134", type text}, {"Column135", type text}, {"Column136", type text}, {"Column137", type text}, {"Column138", type text}, {"Column139", type text}, {"Column140", type text}, {"Column141", type text}, {"Column142", type text}, {"Column143", type text}, {"Column144", type text}, {"Column145", type text}, {"Column146", type text}, {"Column147", type text}, {"Column148", type text}, {"Column149", type text}, {"Column150", type text}, {"Column151", type text}, {"Column152", type text}, {"Column153", type text}, {"Column154", type text}, {"Column155", type text}, {"Column156", type text}, {"Column157", type text}, {"Column158", type text}, {"Column159", type text}, {"Column160", type text}, {"Column161", type text}, {"Column162", type text}, {"Column163", type text}, {"Column164", type text}, {"Column165", type text}, {"Column166", type text}, {"Column167", type text}, {"Column168", type text}, {"Column169", type text}}),
#"Fjernede kolonner" = Table.RemoveColumns(#"Ændret type",{"Column1", "Column3"}),
#"Filtrerede rækker" = Table.SelectRows(#"Fjernede kolonner", each ([Column2] <> null and [Column2] <> "1" and [Column2] <> "address")),
#"Fjernede kolonner1" = Table.RemoveColumns(#"Filtrerede rækker",{"Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column35", "Column37", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98", "Column99", "Column100", "Column101", "Column102", "Column103", "Column104", "Column105", "Column106", "Column107", "Column108", "Column109", "Column110", "Column111", "Column112", "Column113", "Column114", "Column115", "Column116", "Column117", "Column118", "Column119", "Column120", "Column121", "Column122", "Column123", "Column124", "Column125", "Column126", "Column127", "Column128", "Column129", "Column130", "Column131", "Column132", "Column133", "Column134", "Column135", "Column136", "Column137", "Column138", "Column139", "Column140", "Column141", "Column142", "Column143", "Column144", "Column145", "Column146", "Column147", "Column148", "Column149", "Column150", "Column151", "Column152", "Column153", "Column154", "Column155", "Column156", "Column157", "Column158", "Column159", "Column160", "Column161", "Column162", "Column163", "Column164", "Column165", "Column166", "Column167", "Column168", "Column169", "Source.Name"}),
#"Omdøbte kolonner" = Table.RenameColumns(#"Fjernede kolonner1",{{"Column4", "Ppv(W)"}, {"Column5", "Vpv1(V)"}}),
#"Fjernede kolonner2" = Table.RemoveColumns(#"Omdøbte kolonner",{"Vpv1(V)", "Column7", "Column9", "Column11", "Ppv(W)", "Column6", "Column8", "Column10", "Column12", "Column13", "Column14", "Column15", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column36"}),
#"Omdøbte kolonner2" = Table.RenameColumns(#"Fjernede kolonner2",{{"Column38", "Epv1Today(kWh)"}}),
#"Fjernede kolonner3" = Table.RemoveColumns(#"Omdøbte kolonner2",{"Column39", "Column41", "Column43", "Column45", "Column47", "Column49", "Column51", "Column53", "Column55", "Column57"}),
#"Omdøbte kolonner3" = Table.RenameColumns(#"Fjernede kolonner3",{{"Column40", "Epv2Today(kWh)"}, {"Column42", "EpvToday(kWh)"}, {"Column44", "Edischarge1Today(kWh)"}, {"Column46", "Echarge1Today(kWh)"}, {"Column48", "EacChargeToday(kWh)"}, {"Column50", "EacToday(kWh)"}, {"Column52", "EtoUserToday(kWh)"}, {"Column54", "EtoGridToday(kWh)"}, {"Column56", "ElocalLoadToday(kWh)"}}),
#"Filtrerede rækker2" = Table.SelectRows(#"Omdøbte kolonner3", each ([Column2] <> "Time")),
#"Filtrerede rækker1" = Table.SelectRows(#"Filtrerede rækker2", each true),
#"Omdøbte kolonner4" = Table.RenameColumns(#"Filtrerede rækker1",{{"Column2", "Time"}}),
#"Opdel kolonne efter afgrænser" = Table.SplitColumn(#"Omdøbte kolonner4", "Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Time.1", "Time.2"}),
#"Ændret type1" = Table.TransformColumnTypes(#"Opdel kolonne efter afgrænser",{{"Time.1", type date}, {"Time.2", type time}}),
#"Omdøbte kolonner5" = Table.RenameColumns(#"Ændret type1",{{"Time.1", "Date"}, {"Time.2", "Time"}}),
#"Ændret type2" = Table.TransformColumnTypes(#"Omdøbte kolonner5",{{"Echarge1Today(kWh)", type number}, {"Epv1Today(kWh)", type number}, {"Epv2Today(kWh)", type number}, {"EpvToday(kWh)", type number}, {"Edischarge1Today(kWh)", type number}, {"EacChargeToday(kWh)", type number}, {"EacToday(kWh)", type number}, {"EtoUserToday(kWh)", type number}, {"EtoGridToday(kWh)", type number}, {"ElocalLoadToday(kWh)", type number}})
in
#"Ændret type2"

[p45cal]

Trying to get this working over here; is there a function or functions or other queries missing (#"Transformér fil fra Solcelle")?

[snb]

In VBA


Sub M_snb()
c00 = "G:\OF\"
c01 = dir(c00 & "*.xls")
redim sp(6,createobject("scripting.filesystemobject").getfolder(c00).files.count - 1)
st = array(38,40,46,44,56,51,54)
n = 0

do until c01 = ""
with getobject(c00 & c01)
for j = 0 to 6
sp(j,n) = application.max(.sheets(1).usedrange.columns(st(j)))
next
.close 0
end with
n = n + 1
c01 = dir
loop

Thisworkbook.sheets(1).cells(1).resize(ubound(sp)+1,ubound(sp,2)+1) = sp
End Sub

[Them]

From The transformer file:
let
Kilde = (#"Parameter for eksempelfil1") => let
Kilde = Excel.Workbook(#"Parameter for eksempelfil1", null, true),
#"historical data1" = Kilde{[Name="historical data"]}[Data]
in
#"historical data1"
in
Kilde

[p45cal]

Thanks, I was able to get your code working (or at least a near duplicate of it).
In the attached file vbaExpress69081QueryDuplicate.xlsx is that near duplicate. I note it doesn't produce the Max value of any columns but seems to (if I've got it right) produce one consolidated table from all the files in a folder. Instead of loading the result of the query to a table straight on to a worksheet, I've loaded it to a Pivot table report. One thing to note about the Max of EpvToday(kWh) of this pivot table: This value is the max of that column in the .xls file of that date, which seems to be the addition of the Epv1Today(kWh) and Epv2Today(kWh) columns in the same row in those files. It could be that is not the same as your file in msg#1 shows; that would be the case if the max in Epv1Today(kWh) does not happen in the same row as the max in Epv2Today(kWh) - which it mostly does.

The second file attached is just some food for thought and shows some different ways to do things. You've done a lot of the processing in one query (the Data query in vbaExpress69081QueryDuplicate.xlsx). In the vbaExpress69081QueryAlternative.xlsx I've done some work in the processing of individual files in the Transform Sample File query, while the Data query brings them together.

Your code does a fair number of steps removing columns, renaming them. You'll see I used Remove Other columns which results in a line beginning Table.SelectColumns(… instead of Remove Columns (line beginning Table.RemoveColumns(…).
This means I only specify (select) the columns I want to keep (12 of them) instead of the 140 columns I want to remove.
You'll see I renamed columns in a differnet way, by promoting the top line of data to headers instead of explicitly renaming then one by one.

Finally, you'll see I did some Grouping to find the max in each column. I did this in the Transform Sample File query,but could easily have done it in the Data query (if you look at the Data query of the vbaExpress69081QueryDuplicate.xlsx file in Advanced editor, you'll see a commented-out bit of code at the end which could do this grouping.

Of course you'll have to edit the paths in the query to match where your files are located.
Once done, in both these .xlsx files attached, all you need to do is update the .xls files in the folder then refresh the table/pivot table.