Posts by gijsmo

    Excel has a habit of interpreting dates as mm dd yy (even if that is not your local date setting).

    You generally need to code around it, maybe try:

    When using an array with RemoveDuplicates, it needs to be a zero based integer array.

    In the example file you posted there are only 11 columns, so the code snippet for this example would be:

    Obviously, you can expand this if your actual workbook has more columns but the principal will be the same.

    Can you please clarify.

    When you say "5 cell data's" does this relate to the number of cells being scraped from each PreTerm sheet or do you mean the issue occurs after 5 workbooks?

    If it relates to cells, the next step would be to try and work out if there is a particular cell on a particular sheet that may be causing the issue.

    It is very hard to diagnose without actual data/workbooks but obviously you cannot upload these if they contain sensitive data.

    One way to do this within the same block of code is as follows:

    Without 200 or so test files, it's difficult to diagnose the issue.

    Have you tried breaking the files up into groups of, say, 20-50 files and test on those ie, not all the files at once but eventually run through all files.

    This might help determine if it's the number of files being processed or if there is one or more files that may be causing the issue.

    One way that might make it easier to adjust the formulas is by changing the date parameters eg, for Jan 21:


    This way, you only need to change the year value.

    I went back to basics and just modified my original code to also prompt for the Year as input, defaulting to the current year.

    This allows the user to select a different year as required and will also just add '2000' to the year value entered for the current century eg '22' entered will be interpreted as '2022'. Otherwise they can simply press enter and accept the default year value.

    ExportToCSV v2.xlsm

    As I indicated in my earlier example, the code worked with the original data provided.

    No error handling should be necessary because the SpecialCells in this case was based on the unique values in Col G in the original sample.

    The new data you provided however uses Col F instead of G and it includes 'unformatted' data such as "1.46505E+14" which needs special handling.

    The change from Col G to Col F and the 'unformatted' values now included in Col F should be able to be handled by the attached revised example. This currently works on the sample provided, there are on guarantees if this data is not representative of the actual data or if, for example, the data format changes.

    DelRows v2.xlsm

    One way to process the input file into the output you have suggested is via a stand-alone macro as per attached:

    InputExtractor v1.xlsm

    This will process all the sheets in the specified input file (based on the sample provided) and write the results to the specified output file.

    The "template" used for output file is stored as the Output sheet in this stand-alone macro.

    Based on the sample provided, the output file looks as per attached:


    What do you expect the "File used" value to look like (on the Output sheet) when there are multiple files making up the formula eg:



    ='E:\DownloadedFiles\[Dummy4.xlsx]dummy 3'!$D$9+'E:\DownloadedFiles\[Dummy4.xlsx]dummy 3'!$D$9+'E:\DownloadedFiles\[Dummy3.xlsx]dummy2'!$D$9

    In the 1st example, the 2 files referenced happen to be the same but in the 2nd example there are 3 files in the formula and 2 of those are unique.

    Not sure if you're looking for a VBA solution but this scenario can be handled by a formula in column C

    eg, the formula for Cell C3 would be:


    I have attached a generic file lister that may be more than you need but it will derive a list of video files and a bunch of related information like duration, aspect ratio, etc etc. It can also be used to list eg image files, audio files or just as a general file lister (depending on what you value select in the "Extra Data to Show" drop list).

    It takes as input the folder that contains the files to be listed and the folder where you want to write the output file to. For video files, the output filename is in the format of Video List dd-mm-yy.xlsx

    There is one small catch to this. It requires the MediaInfo DLL to extract the metadata from the files. More specifically, it requires the 32bit version of the DLL (the macro is only written for 32 bit version of Excel).

    The 32 bit MediaInfo DLL for Windows can be obtained from here:

    Best to just grab the DLL file in zip format as you will need to place this file on the windows path somewhere (eg: C:\Windows\System32). Also, the file needs to be renamed to MediaInfo32.DLL to work with this macro (to avoid confusion in case you have the 64 bit version of the MediaInfo GUI installed).

    If you want to place the file in another folder, you will need to modify the MediaInfoLibDLL class module and adjust all the declarations eg:


    Private Declare PtrSafe Function MediaInfo_New Lib "MediaInfo32.dll" () As LongPtr


    Private Declare PtrSafe Function MediaInfo_New Lib "C:\Temp\MediaInfo32.dll" () As LongPtr

    If you're comfortable doing that, this will provide a decent list of file details.

    There are a bunch of other options that you can play around with but hopefully most of them are self-evident.

    ListFiles v2.5.xlsb