• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to open only today's generated file that contain some fix word

Dear All

VBA code require that search

(1) on fixed path for extention.. ".xlsx" ..
(2) only today's generated file (search from files' property) also file name contain some fix word like "As On" ...
(3) if 2 condition match ...
particular macro run for that file...without display any alert or notification..

if Not found its also display "Today's file Not Found" & ....also found list displayed
that contain that fix word ...& various date of files generated as on ..

if we select any one ...from list that file ..particular (Same macro as above) run for that file

can it possible
Dear All,

Daily ...file have about 47 to 50,000 lines (records)...

I have already a macro for extract only required buyer's records as auto save separate file as required Buyer wise. That already running successfully..

& macro have already coding of Screen Updating=false, EnableEvents=False, Calculation=manual..& after process complete above 3 events..become as regular...that's work fine .

But problem is ...first that file need to open...& that opening process already take huge time..
& that macro's requirement is particular workbook must be Open & active
for above process...

so if starting code available ....that start from run in background..search on fix path, files have contain some fix word, fix extension..(without explicitly open on screen)..just target (in background) that file to process..
so whole process of my macro can complete without explicitly open that file.

hope you can understand the situation ..& if possible it, help will be appreciated...

Chirag Raval


Excel Rōnin
But problem is ...first that file need to open...& that opening process already take huge time..
& that macro's requirement is particular workbook must be Open & active
for above process...
Hi, Chirag R Raval!
If you happen to find how to access data from an Excel file (extendable to any other format) without opening it (however it's done), please advise.
Dear sir JB7,

Sorry for above long post that may be mislead that "No need to open file"
Actually just need small code that search on desktop...... xlsx ...file which contain some
Searchable fix words... That's it....
So I can open it in 3 miracle iviroment...
Screen updating =off , enable event =false, & xlcalculation =manual..

My macro if take 10 seconds to get result ,
Opening process about 5 minute,
& if by mistake press save button
We must go to tea break..

In more short ..code for Search & open file..

Hope your little help...regarding this...


Chirag Raval

Marc L

Excel Ninja
Hi !

That's the main concern with Excel which very not suits big data
as it must load everything in memory !
Not an issue with Access as a true database software …

To load data from a closed workbook you can try classic formulas
as well ADODB activeX, see samples in threads of this forum …


Excel Ninja
To add to what Marc wrote.

If you have PowerQuery (Get & Transform in Excel 2016), it's a great tool to bring in data from closed workbooks and to do data transformation.

Especially when you use 64 bit installation of Office, along with 8gb+ of RAM.
Dear sir,

Many thanks to understand & give me right direction
But Excel 2016 & 8 GB ram is Like a dream as a
view point of my current situation..
Till then..i must use which given to me..
& also look this as "ok. . Let's play..with excel 2010 ."
& I really satisfied ..at current...

& that base..I am ready for some simple code
That 2 way cross check search on desktop for only extension "Xlsx" .
first focus & catch on file property
For when this file created..( date time for latest created )second ckeck as
Search & varify through use wild card that file name has some characters
If Both match. Open that file in background. So i can as next .call user form to enter
Some buyer nos on column "X" on that file.., if click on ok button on that form,
Separate files for that byers ready on fix folder ..

I can understand that I can not see my entry through
Form on that file due to screen updation off..
It is better if I can see on form as small list
As steady displayfor verify For is that entered correct buyer no on sheet..?

Before reach that form..need that
File open & active in background.

I just need for conditional file opening

Chirag Raval
Dear All,

(1) Vba Code which Search on fixed path for file with wild card support ,
(2) also search for when create or Last Modified (Date-Time)
(3) Display List of files if Multiple same found,
(can be choose from list. which file should be open)

Just Click & Open...

Chirag Raval
Dear Sir,

I have a working code that list all files & folders of given path in current active sheet..

can this be modify as per our requirement??

Sub List_All_Files_in_Folder_Starter()
‘make a list of all files in a single folder
'This macro sets up the output sheet, then starts the loop feeding in starting folder

Application.ScreenUpdating = False    'speed macro
Sheets("Sheet1").range("A1:C150").ClearContents    'clear output sheet and reset titles
Sheets("Sheet1").range("A1:C1").Value = Array("File Name", "Created", "Last Modified")

LoopController ("C:\")    'start the loop with the initial top folder

Sheets("Sheet1").Columns.AutoFit      'we're done, cleanup the final output
Application.ScreenUpdating = True      'update screen one time, back to normal

End Sub

Private Sub LoopController(sSourceFolder As String)
'This will loop into itself, first processing the files in the folder
'then looping into each subfolder deeper and deeper until all folders processed
Dim fldr As Object, SubFldr As Object

    Call ListFilesinAllFolders(sSourceFolder & Application.PathSeparator)  'call the main macro with the current folder

    'now check for existence of subfolders in current folder
    Set fldr = CreateObject("Scripting.FileSystemObject").GetFolder(sSourceFolder)
    For Each SubFldr In fldr.SubFolders    ‘start the loop again for each subfolder in current folder
        LoopController SubFldr.path

End Sub

Sub ListFilesinAllFolders(mypath As String)
Dim fso As Object, f As Object, Fld As Object, NR As Long

NR = Sheets("Sheet1").range("A" & Rows.count).End(xlUp).Row    'find last used row of data
Set fso = CreateObject("Scripting.FileSystemObject")
Set Fld = fso.GetFolder(mypath).Files

For Each f In Fld          'process each file found in the folder
    NR = NR + 1        'increment to the next empty row
    Sheets("Sheet1").range("A" & NR).Value = f.Name    'add info to output
    Sheets("Sheet1").range("B" & NR).Value = f.DateCreated
    On Error Resume Next
    Sheets("Sheet1").range("C" & NR).Value = f.DateLastModified
    On Error GoTo 0
Next f
End Sub
Dear Sir,

More Found on Microsoft.
'Programmatically Selecting Files in Excel for Windows and Excel for the Mac
Sub File_Select_File_Or_Files_Windows()
    Dim SaveDriveDir As String
    Dim MyPath As String
    Dim Fname As Variant
    Dim N As Long
    Dim FnameInLoop As String
    Dim mybook As Workbook

    ' Save the current directory.
    SaveDriveDir = CurDir

    ' Set the path to the folder that you want to open.
    MyPath = Application.DefaultFilePath

    ' You can also use a fixed path.
    'MyPath = "C:\Users\Ron de Bruin\Test"

    ' Change drive/directory to MyPath.
    ChDrive MyPath
    ChDir MyPath

    ' Open GetOpenFilename with the file filters.
    Fname = Application.GetOpenFilename( _
            FileFilter:="Excel 97-2003 Files (*.xls), *.xls", _
            Title:="Select a file or files", _

    ' Perform some action with the files you selected.
    If IsArray(Fname) Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        For N = LBound(Fname) To UBound(Fname)

            ' Get only the file name and test to see if it is open.
            FnameInLoop = Right(Fname(N), Len(Fname(N)) - InStrRev(Fname(N), Application.PathSeparator, , 1))
            If bIsBookOpen(FnameInLoop) = False Then

                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(Fname(N))
                On Error GoTo 0

                If Not mybook Is Nothing Then
                    MsgBox "You opened this file : " & Fname(N) & vbNewLine & _
                          "And after you press OK, it will be closed" & vbNewLine & _
                          "without saving. You can replace this line with your own code."
                    mybook.Close SaveChanges:=False
                End If
                MsgBox "We skipped this file : " & Fname(N) & " because it is already open."
            End If
        Next N
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End If

    ' Change drive/directory back to SaveDriveDir.
    ChDrive SaveDriveDir
    ChDir SaveDriveDir
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Contributed by Rob Bovey
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Dear Sir,

Another Code Found...
Public Function RecursiveDir(colFiles As Collection, _
                              strFolder As String, _
                              strFileSpec As String, _
                              bIncludeSubfolders As Boolean)
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add files in strFolder matching strFileSpec to colFiles
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colFiles.add strFolder & strTemp
        strTemp = Dir

    If bIncludeSubfolders Then
        'Fill colFolders with list of subdirectories of strFolder
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
                    colFolders.add strTemp
                End If
            End If
            strTemp = Dir

        'Call RecursiveDir for each subfolder in colFolders
        For Each vFolderName In colFolders
            Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
        Next vFolderName
    End If

End Function
Public Function TrailingSlash(strFolder As String) As String
    If Len(strFolder) > 0 Then
        If Right(strFolder, 1) = "\" Then
            TrailingSlash = strFolder
            TrailingSlash = strFolder & "\"
        End If
    End If
End Function
Sub Search_Files_on_Path_Sucess()
Dim colFiles As New Collection
    RecursiveDir colFiles, "C:\Users\sganuja\Desktop", "*.xl*", True
    Dim vFile As Variant
    For Each vFile In colFiles
        Debug.Print vFile
    Next vFile
End Sub
Dear All,

Can above code be modify as per requirement..?

(1) Vba Code which Search on fixed path for file with search given word within file name (May be Partial Match) ,
(2) also search for when create or Last Modified (File Created Date-Time-Compare with current system date time& Find Latest-may be use ADODB or FSO)
(3) if found 1 file instantly open that in background ....if found multiple files with contain search word ,then Code should display user form with list of found .....we can choose from that list ..

Just Click & Open...

Chirag Raval
Dear experts

User form display list with following+with hyperlink on file name click & open file

Search on desktop in folders & Sub folders

All Files listed name contain word "dispatch"
All files name contain word "status"

If possible date created should be display to make discission which file to be open...


Chirag Raval
Dear Sirs,

Can any one help to share little code for below?

User form display with list with path of all excel files on desktop's
Folders & Subfolers file name with date created & with hyperlink to open it.. List only for File name contain words "status" & list only for file name contain word "dispatch"


Chirag Raval