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

 

[RayFrye]

Have you installed the ODBC drivers for Microsoft.ACE.OLEDB.12.0?
Go to settings ODBC and look for this driver.

 

[Guinaba]

Have you installed the ODBC drivers for Microsoft.ACE.OLEDB.12.0?
Go to settings ODBC and look for this driver.

Hi @RayFrye,
These are the available drivers in my machine. I don't see any ACE.OLEDB.12 there, do you mind sharing your screen so I can see what I have to look for.

 

[johnnyL]

@Guinaba what data are you trying to get?

 

[Guinaba]

@Guinaba what data are you trying to get?

Hi @johnnyL,

Trying to get the data into excel from another close excel file (xlsb) because is a big file! I did tried powerquery but was taking too long to exctract all these data, so now I'm trying to find an alternative. Any suggestion?

 

[johnnyL]

Please refer to post #4. I asked you what data you are trying to get and you responded with 'the data'. Any particular sheets, all sheets, please be more specific.

 

[Guinaba]

Please refer to post #4. I asked you what data you are trying to get and you responded with 'the data'. Any particular sheets, all sheets, please be more specific.

Try to get just one big sheet with 800000 rows, just showing you the structure below. I need to read this data from this file grouping the data which is daily into weekly buckets.

Date	Description	Measure Names	Ref No	State	UOM	Measure Values	$	Code	Department
8/01/2021​	RSPCA Aprvd Chkn Breast Stir Fry 500g	Smth	2841​	WA	EA	413​	$3,471.31​	1618800​	Meat
8/01/2021​	RSPCA Aprvd Chkn Breast Stir Fry 500g	Smth	2841​	VIC	EA	848​	$7,603.51​	1618800​	Meat

 

[johnnyL]

@Guinaba So you want to 'scrape' a range of data from a sheet in a closed workbook?

Do you know the sheet name in the closed workbook that you want to scrape data from and what exact range do you want to scrape data from in the sheet in the closed workbook Ex: A1:J800000?

 

[Alex Blakenburg]

I suspect that @johnnyL will be better placed to help you on using ADODB going forward but in terms of your immediate problem try this:-

Sub GetDataFromClosedFile()

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
 
    Dim strWorkbook
    strWorkbook = "S:\Everyone\Demand Planning\Primary & VE\Woolworths\WW Daily Scan Sales\Daily Sales 20210708.xlsb"

    With cn
        .Provider = "Microsoft.ACE.OLEDB.16.0"
        .ConnectionString = "Data Source=" & strWorkbook & "; Extended Properties=""Excel 16.0 Macro; HDR=YES"""
        .Open
    End With
  
    cn.Close
End Sub

I can't quite remember which of these were essential but in case it helps:-

 

[Guinaba]

@Guinaba So you want to 'scrape' a range of data from a sheet in a closed workbook?

Do you know the sheet name in the closed workbook that you want to scrape data from and what exact range do you want to scrape data from in the sheet in the closed workbook Ex: A1:J800000?

So you want to 'scrape' a range of data from a sheet in a closed workbook?
Correct, grouping the daily bucket into weekly bucket

The sheet name is: Dump
The file path is: S:\Everyone\Demand Planning\Primary & VE\Woolworths\WW Daily Scan Sales\Daily Sales 20210708.xlsb
The range: A1:J800000