I have a DTS that creates excel files for records in a SQL Server 2000 table and then an ActiveXScript runs that emails the excel file as an attachment.
The problem is that whenever the end user receives the email and tries to open the excel file they get the following error: "File format is not Valid"
So I created a sample excel file on my desktop and pasted in the location where the other excel sheets that created by the Dynamic SQL provided below and had the DTS run and email it, and I was able to open the excel file. So I believe the error is in the Dynamic SQL, the box is SQL Server 2000 and the end users have Excel 2003.
Do I need to install something the sql server box so when the Dynamic SQL runs it creates the excel files with the right version?
Thank you in advance.
DECLARE @OLEDBInitString varchar(1000),
SET @FilePath = @FolderPath + 'ContactAuditList_' + @LOC + '_' + cast(year(getdate()) as varchar) + '_'+ cast(month(getdate()) as varchar) + '_'+cast(day(getdate()) as varchar)+ '.xls'
SET @CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'
EXEC master.dbo.xp_cmdshell @CopyCmd, no_output
SET @OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 8.0;'
DECLARE @Insert varchar(1000)
SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$]
SELECT AccountName, AccountID, FullName, JobTitle, Phone, PhoneExtension, EMAIL,Notes FROM dbo.[Contact Audit List 2008] WHERE AccountID ='''+@LOC + ''''