Macro DTS import of Excel spreadsheet failing

Quite a few years ago, I did an automated upload of pricing information from an Excel spreadsheet into a MS SQL database table.

The VBA macro created a new spreadsheet with the required data from the Excel worksheet and saved it in a transfer folder. The macro then fired off a DTS package to upload the data into the table. More magic happens afterward but is not relevant.

Everything was working perfectly for a few years until the client upgraded from Excel 2003 to Excel 2007.  The new worksheet was being saved just fine but the client was now getting the following error message on the DTS job:

DTS Error

I couldn’t figure it out. The Excel spreadsheet exported and looked fine but the job was still failing. Clearly, the DTS job was being triggered, what gives?

The key is in the new file format of Excel 2007. The ActiveWorkbook.SaveAs was not respecting the .xls extension.

Below is the relevant code fragment:

LFilename = LPath & LDate & "_PriceList.xls"
If Dir(LFilename) <> "" Then Kill LFilename
ActiveWorkbook.SaveAs Filename:=LFilename

With Excel 2007 and up, you need to set another parameter when saving a file otherwise, it will assume you would like the default Excel 2007 xlsx file format regardless of the extension.

Below is the fixed code:

LFilename = LPath & LDate & "_PriceList.xls"
If Dir(LFilename) <> "" Then Kill LFilename
ActiveWorkbook.SaveAs Filename:=LFilename,FileFormat:=56

File Formats in Excel 2007/2010:

51 = xlOpenXMLWorkbook (without macro’s in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro’s in 2007-2010, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

Problem solved.

BTW, before anyone asks “did I try to change the DTS job to support Excel 2007/2010 xlsx format?” The answer is yes but my MS SQL server did not have the driver.

Warning: The FileFormat throws an error when used on Excel 2003.

If you need to support both 2003 and 2007/2010 then you must modify the SaveAs code to detect which version of Excel you are using:

If Val(Application.Version) < 12 Then
      ActiveWorkbook.SaveAs Filename:=LFilename
 Else
     ActiveWorkbook.SaveAs Filename:=LFilename, FileFormat:=56
 End If

The above will work on both old and new version of Excel.

Props to:

http://www.rondebruin.nl/saveas.htm

This entry was posted in MS Office and tagged , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*