Our training courses

Other training resources

Our training venues

Why we are different

Details for CrickPick

CrickPick has participated in the following threads:

Added by CrickPick on 09 Nov 2023 at 19:22

Hi there,

My coworker and I were running into an error, Run-time error '-2147217865 (80040e37)': Automation error, when we hit the rs.open line. We both tried posting the code in the comments of the YouTube video, but they never seemed to stick. Below is the full code we are using.

Thanks so much in advance for any help with this!

Christina

Dim MyFilesPath As String
Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Sheet1.Range("A1").CurrentRegion.Offset(1, 0).Clear


MyFilesPath = "G:\TR\DATA_TEAM_REPORTS&QC\PerryC"

Set cn = New ADODB.Connection

cn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & MyFilesPath & "DupeIMBTest - CP Edit.xlsx;" & _
    "Extended Properties='Excel 12.0 Xml;HDR=YES';"

cn.Open

Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Source = _
    "SELECT * FROM [Sheet1$] " ' _
'    & "WHERE [Acronym] = 'NWW' AND [Job] LIKE 'Weekly%'"
rs.Open

Sheet1.Range("A2").CopyFromRecordset rs

Sheet1.Range("A1").CurrentRegion.EntireColumn.AutoFit

rs.Close

cn.Close

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...