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
Bookmarks