• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2925
  • Last Modified:

excel connection string not working for excel 2007

Hello, I have the following excel connection string that works fine for excel version 97-2003 but does not work for excel version 2007:

Dim sExcelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(sExcelFileName) & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""

How can I modify my excel connection string to handle both excel versions?

           
0
gogetsome
Asked:
gogetsome
  • 3
  • 3
1 Solution
 
Bob LearnedCommented:
Look here for a reference:

http://www.connectionstrings.com/?carrier=excel2007

Example:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0;HDR=YES";

Bob
0
 
gogetsomeAuthor Commented:
Thanks for the help. That should work for a 2007 document. I assume that the oledb12.0 and excel 12.0 are backward compatible with excel 97-2003 ?
0
 
Bob LearnedCommented:
No, the connection string that you showed in the question was the one for Excel 2003, and the 2007 connection string cannot be used for 2003.

Bob
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
gogetsomeAuthor Commented:
Ok, so I would first have to discover the excel type then use the appropriate string. Is there an easy way to do this?
0
 
Bob LearnedCommented:
One way to check for version is to look in the Windows registry for the appropriate key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0
   or
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\11.0

Bob
0
 
gogetsomeAuthor Commented:
Thank for your help Bob. I will check that out. Have a great day!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now