Unable to use Microsoft.ACE.OLEDB.12.0 with Excel16 64-bit

[Guinaba]

Hi guys,

I am trying to get the data from a .xlsb file using ADODB but keep getting the error below:

Run-time error '446':
Object doesn't support named arguments

I've activated the Microsoft ActiveX Data Objects 6.1 Library and I am using Excel16 64bit. Any suggestions?

Sub GetDataFromClosedFile()

   Dim cn As ADODB.Connection 
   Set cn = New ADODB.Connection
   
   cn.ConnectionSting = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=S:\Everyone\Demand Planning\Primary & VE\Woolworths\WW Daily Scan Sales\Daily Sales 20210708.xlsb;" & _
        "Extended Properties='Excel 12.0 ;HDR=YES';"
        
    cn.Open
       
    cn.Close
End Sub

 

[RoryA]

Sub GetDataFromClosedFile()

   Dim cn As ADODB.Connection
   Set cn = New ADODB.Connection
  
   cn.ConnectionSting = _

It's ConnectionString, not ConnectionSting. Not sure how that code would run, to be honest.

 

[Guinaba]

It's ConnectionString, not ConnectionSting. Not sure how that code would run, to be honest.

Thanks @RoryA! Good catch! I didn't see that! But still running my code (corrected) and the code from @Alex Blakenburg, now I keep getting the error:

Run-time error '-214746759 (80004005)':
Could not find installable ISAM
Here is my library, any way THANKS for trying to help me!

 

[Alex Blakenburg]

Run-time error '-214746759 (80004005)':
Could not find installable ISAM

I got past there, did you try my code ?

 

[Guinaba]

I got past there, did you try my code ?

Yes, but didn't work. Need to try to run it in another machine just to see if works...

 

[RoryA]

Change both the Provider and Extended properties to use 12.0 not 16.0 and, as it's an xlsb format file, the extended properties should just be Excel 12.0 not Excel 12.0 Macro

 

[Guinaba]

Change both the Provider and Extended properties to use 12.0 not 16.0 and, as it's an xlsb format file, the extended properties should just be Excel 12.0 not Excel 12.0 Macro

Awesome!!! It is working now @RoryA!!!! Ufff!! Finally!! Thanks a lot!!!