Hi there is no code?
Hello, yes this is the sub routine I call, I've highlighted the bit of code that is giving trouble:
Sub createTempCSV(inputPth As Variant, fileName1 As Variant, dbpath As Variant, linkName As Variant, company As Variant, ParamArray flds() As Variant)
Dim inputFile As String, xls As Excel.Application, requiredField As Boolean, xls2 As Excel.Application
Dim i As Long, j As Long, outputFile As String, db As DAO.Database, tbl As DAO.TableDef
Dim u As Long, x As Long, destPth As String, colCount As Long
destPth = "\\leh\eu\fid\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\other\DWH\"
Set e = DAO.DBEngine
e.SystemDB = "\\leh\eu\fid\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\Data for model\DB\MrtggSecurity.mdw"
Set w = DAO.CreateWorkspace("MDB automazione" & Int(Timer), "agaviani", "tittnl", dbUseJet)
Set xls = New Excel.Application
outputFile = "\\leh\eu\fid\groups\fidshare1\mortgage\Mortga ge Platforms\SPML\other\DWH\Temp_" & fileName1
On Error Resume Next
Kill outputFile
On Error GoTo 0
FileCopy inputPth & fileName1, outputFile
xls.Workbooks.Open fileName:=outputFile, ReadOnly:=False
i = xls.Workbooks(1).Worksheets(1).UsedRange.Columns.c ount
GoBack:
For j = 1 To i
requiredField = False
u = UBound(flds, 1)
For x = 0 To u
If InStr(flds(x), xls.Workbooks(1).Worksheets(1).Range(colLetter(j) & 1)) > 0 Then
requiredField = requiredField Or True
End If
Next x
If not requiredField and not InStr("Loan Account Number", xls.Workbooks(1).ActiveSheet.Range(colLetter(j) & 1)) > 0 Then
xls.Workbooks(1).Worksheets(1).Columns(colLetter(j ) & ":" & colLetter(j)).Select
Selection.EntireColumn.Hidden = True
Selection.Delete
i = i - 1
GoTo GoBack
End If
Next j
xls.Workbooks(1).Close (True)
xls.Quit
Set xls = Nothing
Set db = w.OpenDatabase(dbpath, False, False)
On Error Resume Next
db.TableDefs.Delete linkName
On Error GoTo 0
Set tbl = db.CreateTableDef(linkName)
tbl.Connect = "Text;DATABASE=" & destPth & ";TABLE=Temp_" & fileName1
tbl.SourceTableName = "Temp_" & fileName1
db.TableDefs.Append tbl
db.TableDefs.Refresh
db.Close
Set db = Nothing
Set tbl = Nothing
End Sub