Need help solving and ADO code I trying to write

Question

Below is my version of a sub() I found on Youtube. I followed every line exactly, but I keep getting this error on the Execute query line

Sub getDataFromMultipleFiles() 

Dim cn As ADODB.Connection 

Dim sourceFile As String 

Dim sourceFilePath As String 

Dim sourceSheet As String 

Dim query As String 

sourceFilePath = "C:\Users\ayoj\Documents\Dev Files\Dev Files - CODA Data\BrowseBalances-ExportData_FY22\P01_BrowseBalances-ExportData\" 

Set cn = New ADODB.Connection 

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ 

                      "Data Source=C:\Users\ayoj\Documents\CodeTest_forADO.xlsm;" & _ 

                      "Extended Properties='Excel 12.0 Macro;HDR=YES';" 

cn.Open 

    sourceFile = sourceFilePath & "BrowseBalances-ExportData_CSC_P01.xlsx" 

    sourceSheet = "[Excel 12.0;HDR=Yes;DATABASE=" & sourceFile & "]" 

    query = "Insert Into [DumpHere$] Select * From " & sourceSheet & ".[Sheet1$]" 

    cn.Execute query 

cn.Close 

End Sub

Answer 1

The error could be caused by system settings.

Try the steps:

In the Start Menu search box type "run" without the quotes. In the Run box type "msconfig" without the quotes. When the System Configuration Utility opens click the Services tab, check the box that says "Hide all Microsoft services". When the Microsoft services are hidden, disable all remaining services. Click Apply and OK.

Answer 2

I am trying to copy data from, a closed file, "sourceFile", into CodeTest_forADO.xlsm, not from one sheet into another sheet in the same file.

Answer 3

There are no protected sheets on either file.

Answer 4

Hi aojiku01，

This problem occurs because the Excel object model for the chart is disabled on a protected worksheet.

To work around this problem, unprotect the worksheet to enable the macro to run. You can manually unprotect the worksheet or by using the Unprotect method in the macro.

For your reference:

Run-time error-2147467259 (80004005) when you set a property of a chart - Office | Microsoft Learn

Best Regards,

Snow Lu

Answer 5

This worked for me. I created a file 'ADO Test.xlsx' with data in 'Data' sheet and another sheet called 'Ouptut' with just the header row from 'Data' sheet.

Option Explicit 

Sub getDataFromMultipleFiles() 

Dim cn As ADODB.Connection 

Dim sourceFile As String 

Dim sourceFilePath As String 

Dim sourceSheet As String 

Dim query As String 

Set cn = New ADODB.Connection 

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ 

                      "Data Source=C:\Users\vinam\Downloads\ADO Test.xlsx;" & _ 

                      "Extended Properties='Excel 12.0 Macro;HDR=YES';" 

query = "Insert Into [Output$] Select * From " & ".[Data$]" 

cn.Open

cn.Execute query 

cn.Close

End Sub