August 14, 2008 at 6:45 am
Hi Team,
First of all thanks for all the support provided in helping me to achieve the code.
Would require a small help now.
I have created jobs which will populate 6 excel sheets in one excel file. This job runs daily. What i would require is a code which will help me to delete the rows in the excel file and then populate it again . If this is not done then what happens is the excel file gets appended.
Or else if i could get a code which helps me to dynamically create excel file based on the date, it would be appropriate.
Thanks
Liju
August 14, 2008 at 7:22 am
first, how are you populating the xl sheets? can you post the code? i ask because i'm wondering if there isn't a switch you can use that tells it to overwrite instead of append.
second, there are a number of ways to accomplish what you want to do if you can't set it to overwrite. you can write a single line into your job/proc/whatever that calls xp_cmdshell with a command to delete the xl sheet (but you'd have to enable xp_cmdshell in the surface area configuration), or you can use xp_cmdshell to rename/move the file into an archive folder, or (probably the best solution) you can write one line into your code to calculate a name with today's date embedded into it, like SELECT @XLFileName = REPLACE(CONVERT(VARCHAR(32), GetDate(), 101), '/', ''), and then use that as the xl filename.
August 19, 2008 at 1:39 am
Hi Lenny
Thanks for replying. Please find your queries answered below.
The xl sheet is populated in the following way using the code
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\Book1.xls;',
'SELECT * FROM [Sheet1$]')
select * from testtable
What i will do is, with my small capability of creating codes, i will try and use your codes and will let you know
Thanks so much for the reply
Liju
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply