Hi - i have this file which i download everyday in a CSV format. I have written a macro to arrange the data, which requires querying some data from access. However, this doesnt seem to work on the CSV file when i run it.

I get an error "User-defined type not defined", whenever i try to run this on the csv file. I have the macro stored in personal.xlsb as we

However, this is resolved when i manually copy and paste the data from the CSV file into the workbook where the macro is initially created - a xlsm file.

Is there any way to automate and run this macro in my CSV file?

Sub Security()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim myconn As String
    Dim TARGET_DB As String
    Dim qry As String
    Dim strSQL As String
    Dim recordID As String
    Dim idType As String
   
      
   
    TARGET_DB = "ADB.accdb"
    
    Set cnn = New ADODB.Connection
 
    Set rst = New ADODB.Recordset
 
    myconn = "\\AAA\" & TARGET_DB
 
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open myconn
    End With
    
        
    Worksheets("Sheet1").Select
    Columns("A:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("G1").Select
    
    Do While Not IsEmpty(ActiveCell.Value)
        
        recordID = ActiveCell.Value
        ActiveCell.Offset(0, 20).Value = "IBContractID"
        idType = ActiveCell.Offset(0, 20).Value

        
        strSQL = "SELECT SecurityID FROM tblSecurity WHERE " & idType & "=" & "'" & recordID & "'" & ";"
     
        
        rst.Open strSQL, cnn
                
        ActiveCell.Offset(0, -5).CopyFromRecordset rst
        
        rst.Close
        
        ActiveCell.Offset(1, 0).Select
        
    Loop
 
 cnn.Close
 
    Columns("AA").Delete
    
End Sub