How to specify string path for opening excel file?

Question

How would I go about specifying a string path for opening a excel file knowing that the excel file's file name will always change or at least plug in the most recent saved excel file in that folder to the string path? For example,

string PATH = @"C:\myFileNameWillAlwaysCHange.xlms or mostRecentFile.xlms";
string connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PATH + ";Extended Properties=Excel 12.0";

I was wondering if the folder that the excel file will be in will be the only excel file, if there was a way to specify the path base on any (only) excel file in that folder and plug it in for the string path?

possible duplicate of [How to find the most recent file in a directory using .NET, and without looping?](http://stackoverflow.com/questions/1179970/how-to-find-the-most-recent-file-in-a-directory-using-net-and-without-looping) — Sinatr, May 13 '15 at 14:49

Sébastien Sevrin · Accepted Answer · 2015-05-13T15:04:59.960

2

You could do it like this

string PATH = Directory.GetFiles("C:\", "*.xlms").Single();

string connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PATH + ";Extended Properties=Excel 12.0";

If you don't want it to throw an exception if more files are found, replace Single with First.
If you don't want it to throw an exception if there are no matching files, replace Single with FirstOrDefault and add a null check

EDIT

To get the most recent file:

string PATH = Directory.GetFiles("C:\", "*.xlms").Select(f => new FileInfo(f)).OrderByDescending(fi => fi.CreationTime).First().FullName

edited May 13 '15 at 15:04

answered May 13 '15 at 14:48

Sébastien Sevrin

5,267
2
22
39

But you are missing the wildcard character before `.xlms` in your search filter. – Sven Grosen May 13 '15 at 14:50
@GrantWinney Yes but the OP changed :), edited to match the new requirements – Sébastien Sevrin May 13 '15 at 14:54
what reference do i need for the .Single() ? – NewKidOnTheBlock May 13 '15 at 14:59
You could use the following if you can't use Single `string PATH = Directory.GetFiles("C:\", "*.xlms").Select(f => new FileInfo(f)).OrderByDescending(fi => fi.CreationTime).First().FullName` – Sébastien Sevrin May 13 '15 at 15:02
couldn't i reorder it by the most recent added date and get the first recent file? – NewKidOnTheBlock May 13 '15 at 15:04
@NewKidOnTheBlock You mean the `Added to folder` date? There is no such date stored in the `FileInfo`... – Sébastien Sevrin May 13 '15 at 15:11
well anything that can help determine what file went in there first, like date modified and date created yes. – NewKidOnTheBlock May 13 '15 at 15:13
so you may want to use `LastWriteTime` instead of `CreationTime` – Sébastien Sevrin May 13 '15 at 15:19
so LastWriteTime will get me the most recent added excel file to that folder? – NewKidOnTheBlock May 13 '15 at 15:21
No, the file with the last modification date, as I told you it is not possible to know the `Added to folder` date, the workaround would be to use a `FileSystemWatcher` but it is out of scope – Sébastien Sevrin May 13 '15 at 15:24

Sven Grosen · Answer 2 · 2015-05-13T14:56:48.493

From what I gather from your question, you are looking to get the path of an Excel file that should be the only such file in a given directory. In that case, you can do this:

string path= Directory.GetFiles("C:\YourDirectory", "*.xlms").Single();
string connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0";

This will get the files with the given file extension in the given directory, and then assert there is only one by doing a Single() call on it. If more than one such file exists in the folder, then you'll get a InvalidOperationException.

EDIT To get the most recent file (I've chosen to go by creation time, but you can use modified date if you'd rather), you can do this:

DirectoryInfo info = new DirectoryInfo("C:\YourDirectory");

string path = info.GetFiles("*.xlms").OrderByDescending (fi => fi.CreationTimeUtc).First().FullName;

This has no exception handling, so if the First() call doesn't return anything, you'll need to handle that.

Well if I'm able to get the most recent file added to that folder, that would be even better. — NewKidOnTheBlock, May 13 '15 at 14:52

score 1 · Answer 3 · answered May 13 '15 at 14:56

I understand you are looking for all the Excel files at one Folder.

this is the code:

        try
        {
        string PATH = @"C:\YourFolderPath";
        DirectoryInfo Dir = new DirectoryInfo(PATH);
        FileInfo[] FileList = Dir.GetFiles("*.xls*", SearchOption.AllDirectories );
        foreach (FileInfo FI in FileList )
        {
        Console.WriteLine(FI.FullName);
        }
        }
        catch (Exception ex)
        {
        Console.WriteLine(ex.Message );
        }

inside the foreach you can put any code you want

How to specify string path for opening excel file?

3 Answers3