If the filename is predictable based on current day's date, construct the filename using date arithmetic. For example, if the filename has yesterday's date, it would be like this:DECLARE @filename VARCHAR(255) = 'Person Catalog '+CONVERT(CHAR(10),DATEADD(dd,-1,GETDATE()),110);
SELECT @filename;
If the filename cannot be derived from current day's date, and instead, is just the latest file available in the folder, then construct the filename like this. My command shell skills are rather limited - I constructed this using something Lamprey posted a few days ago here; if this does not do exactly what you are looking for, refer to his post to see the logic I am using http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180630
DECLARE @Foo TABLE (id int identity(1,1), Val NVARCHAR(4000))
INSERT @Foo
EXEC xp_cmdshell N'dir T:\Bedrock\AAS Calatogues\*.xlsx /O-D /B'
DECLARE @filename VARCHAR(255);
SELECT TOP 1 @filename = val FROM @foo order by id;
In either case, you may need to make your select query into dynamic SQL because openrowset may not let you use variables to construct the argument.
Import Excel Date Modified
Posted - 11/21/2012 : 07:34:48
