CODEWITHSUNDEEP
c#exceldatatableoledbconnection
NewKidOnTheBlock
NewKidOnTheBlock

Reputation: 91

How to specify string path for opening excel file?

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?

Upvotes: 1

Views: 5532

Answers (3)

Sébastien Sevrin
Sébastien Sevrin

Reputation: 5405

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

Upvotes: 2

Sven Grosen
Sven Grosen

Reputation: 5636

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.

Upvotes: 2

Eliad
Eliad

Reputation: 140

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

Upvotes: 1

Related Questions