The excel part simply format a file before importing it.
the reporting date is derived from the file name
sSql deletes null values in the imported table
sSql1 add the reporting date to the end of the table
sSql2 creates a new table where some of the fields have to be changed from text to number
There are other fuctions being called within the code but they have nothing to do with this particular part of the code that doesnt work.
Let me know if you need any moe information.
Thanks for your time. It is much appreciated
Function CreateCFD_DQ_FEED()
Dim db As Database
Set db = CurrentDb
Dim sSql As String
Dim ImportDate As String
Dim ImportTable As String
Dim tmpName As String
Dim i As Integer
Dim vx
Dim strName As String
Dim xlApp As Excel.Application
Const QUOTE = """"
Dim sSql1 As String
Dim strRepdate As Date
Dim Repdate As Date
Dim sSql2 As String
Dim FormatTable As String
Dim rs As Recordset
Dim FilePath As String
Dim OutputFile As String
Const tmpDir As String = "\\lonc11g\controlling14$\cadproj\DOCUMENT\dq\" ' this where the raw ESS files are kept
On Error GoTo Feeds_Err:
DoCmd.Hourglass True
DeleteTablesCfd_Import
DeleteTablesCfd_Format
db.Execute ("DELETE [tbl Ref CFD Reporting Dates].* FROM [tbl Ref CFD Reporting Dates]")
tmpName = Dir(tmpDir & "*.xls")
FilePath = tmpDir & tmpName 'gives the path for excel to open
i = 1
Do While tmpName <> ""
vx = SysCmd(acSysCmdSetStatus, "Working on file " & tmpName & " Number " & i & " in Excel (hidden)")
ImportDate = Mid(tmpName, 9, 6)
OutputFile = tmpDir & "cfds\" & ImportDate & "CFD_Import" & ".xls" 'output the file to a dirrent directory
strName = tmpDir & tmpName
If Len(Trim(strName)) <> 0 Then
Set xlApp = New Excel.Application
xlApp.Workbooks.Open ApplicationPath & "CFD.xls" 'open the cfd spreadsheet. This must be stored in the same place as the database
On Error Resume Next
'* Use in line error handling to counter this bug.
xlApp.Visible = False
xlApp.Run "Controller.PrepareFile", strName, OutputFile 'this runs the module in excel
err.Clear
xlApp.Quit
Set xlApp = Nothing
Else
End If
tmpName = Dir
i = i + 1
ImportTable = ImportDate & "CFD_Import"
vx = SysCmd(acSysCmdSetStatus, "Preparing to Import " & OutputFile)
DoCmd.TransferSpreadsheet acImport, 8, ImportTable, OutputFile, True, "" 'import the file
vx = SysCmd(acSysCmdSetStatus, "Cleaning up Data in " & ImportTable & " Number" & i & "")
sSql = sSql & ""
sSql = sSql & " DELETE [" & ImportTable & "].swap,[" & ImportTable & "].*" '"
sSql = sSql & " FROM [" & ImportTable & "]"
sSql = sSql & " WHERE [" & ImportTable & "].Swap=0 Or [" & ImportTable & "].Swap=null;" 'delete blanks in the table
db.Execute (sSql)
sSql = ""
strRepdate = Format(Mid(ImportDate, 1, 2) & "/" & Mid(ImportDate, 3, 2) & "/" & "20" & Mid(ImportDate, 5, 2), "short date")
sSql1 = sSql1 & ""
sSql1 = sSql1 & " UPDATE [" & ImportTable & "]"
sSql1 = sSql1 & " SET [" & ImportTable & "].ReportingDate=" & QUOTE & strRepdate & QUOTE & ";" 'update the reporting date
db.Execute (sSql1)
sSql1 = ""
FormatTable = ImportDate & "CFD_Format"
vx = SysCmd(acSysCmdSetStatus, "Creating new format table " & FormatTable)
'format the table. File imports with text fields so this creates a new table and changes the data types to numbers
sSql2 = sSql2 & ""
sSql2 = sSql2 & " SELECT [" & ImportTable & "].Book, [" & ImportTable & "].Customer, [" & ImportTable & "].Swap, [" & ImportTable & "].[Stock Ccy], [" & ImportTable & "].[Start Date], [" & ImportTable & "].[Settle Date], [" & ImportTable & "].[End Date], [" & ImportTable & "].[Sec Ticker], [" & ImportTable & "].ISIN, [" & ImportTable & "].Qty, [" & ImportTable & "].[Start Price], [" & ImportTable & "].[Mkt Price], [" & ImportTable & "].[Pay Ccy], CDbl(tval([Current Notional])) AS notional, CDbl(tval([Mkt Value])) AS MktValue, CDbl(tval([MTM])) AS [MTM ccy], [" & ImportTable & "].[Margin Status %], CDbl(tval([Daily Move in Margin])) AS DailyMoveInMargin, [" & ImportTable & "].ReportingDate"
sSql2 = sSql2 & " INTO [" & FormatTable & "]"
sSql2 = sSql2 & " FROM [" & ImportTable & "];" '
db.Execute (sSql2)
sSql2 = ""
Set rs = db.OpenRecordset("tbl Ref CFD Reporting Dates") 'Creates a list of all the tables imported for the cfd feed
'This is used later on to scroll through the formated tables
'
rs.Edit
rs.AddNew
rs![Tablename] = FormatTable
rs![ReportingDate] = strRepdate
rs![ESS_Spreadsheet_Name] = tmpName
rs.Update
rs.MoveNext
rs.Close
Loop
vx = SysCmd(acSysCmdSetStatus, " ")
vx = SysCmd(acSysCmdClearStatus)
funSortRepTable 'opens the reporting table, sorts descending,and scrolls through the fields one by one
DoCmd.Hourglass False
ESS_Feeds_Exit:
Exit Function
Feeds_Err:
MsgBox Error$
DoCmd.Hourglass False
vx = SysCmd(acSysCmdSetStatus, " ")
vx = SysCmd(acSysCmdClearStatus)
Resume ESS_Feeds_Exit
End Function
You use Edit then AddNew. Only the AddNew is necessary. at the end, you MoveNext and immediately close the recordset. Why not open the recordset before the Do..Loop starts and close it when the loop end? Then the MoveNext is logical...
