0

I have an issue running an SQL query on an excel spreadsheet which I'm looking for some help on:

I'm using Excel and Access 2007 on a windows xp machine sp3.

I recently found this post which showed me how to run an Access crosstab query on a worksheet in excel. http://datapigtechnologies.com/blog/index.php/running-crosstab-queries-in-excel/

I am running the below code in excel and attempting to pivot (crosstab) my data.

Everything was running perfectly well. But when I set my excel workbook to read-only, which I need to do, I suddenly started to get errors.

When I run the below code with the workbook as read-only, I get the following error: External table is not in the expected format. -2147467259 (80004005)

Dim Myconnection As adodb.Connection
Dim Myrecordset As adodb.Recordset
Dim Myworkbook As String
Dim strSQL As String

Set Myconnection = New Connection
Set Myrecordset = New Recordset

'Identify the workbook you are referencing
Myworkbook = Application.ThisWorkbook.FullName

'Open connection to the workbook. This is where I get the first error.

Myconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & Myworkbook & ";" & _
                    "Extended Properties=Excel 8.0;" & _
                    "Persist Security Info=False"



'Build SQL Statement. This statement runs perfectly well when I copy and paste it into Access
strSQL = "TRANSFORM Last(Field1) AS LastOfField1 " & _
            "SELECT Field7, Field6 AS CLIENT, [Field2], [Field3], [Field4], Field5 " & _
            "FROM [RawData$A1:K1000] " & _
            "GROUP BY Field7, Field6, [Field2], [Field3], [Field4], Field5 " & _
            "ORDER BY Field6 " & _
            "PIVOT Field8 ;"


'Load the Query into a Recordset
Myrecordset.Open strSQL, Myconnection, adOpenStatic

So, I read a post which suggested I try changing my connection string. So I changed it to the following:

Myconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                    "Data Source=" & Myworkbook & ";" & _
                    "Extended Properties=Excel 12.0;" & _
                    "Persist Security Info=False"

After I changed it, the connection opened, however, I then got at error on the following line:

'Load the Query into a Recordset
Myrecordset.Open strSQL, Myconnection, adOpenStatic

No value given for one or more required parameters. -2147217904

There are no parameters in my query and I've checked all the field names and they are spelt correctly. The SQL is good when run in MS Access. After some investigation, if I change my sql to a standard select query, it runs. So maybe this connections string does not like crosstab queries.

Also, I work in a controled IT environment so I can't run any registry fixes, if needed.

I'm a bit stuck now. Any help would be appreciated.

Eddie
  • 611
  • 3
  • 13
  • 23

1 Answers1

0

Just a quick idea. As far as you have a macro in Excel file I believe it's saved as .xlsm. Therefore I think you need the following type of ConnectionString (change where required):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;
          Extended Properties="Excel 12.0 Macro;"

Source: www.ConnectionStrings.Com

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
  • Thx for the tip. It is a .xlsm. I chandged it but got a different error this time. Could not find installable ISAM. -2147467259 (80004005) I get this error if I run it as read-only or not. It's strange how the original connection which uses Jet works perfectly well when not in read-only mode. With that, I have no issues. – Eddie Jan 17 '14 at 12:07
  • try without HDR = YES as changed in my answer now. – Kazimierz Jawor Jan 17 '14 at 12:17
  • try also with the following settings for `.Open method`: `Myrecordset.Open strSQL, Myconnection, adOpenUnspecified, adLockUnspecified`. – Kazimierz Jawor Jan 17 '14 at 12:18
  • I've been trying different combinations of your suggestions but still no luck. I'm assuming that it's still looking for an extra property in the connection string which is missing. I guess an alternative option is to change the workbook from read-only in my code, but I don't think you can do this at runtime? – Eddie Jan 17 '14 at 12:52