Option Compare Database
Option Explicit
Public Const PATH = "D:\HFH Doc\programing\PPA\update\VBA\06_29_2009"
Public Const USAGE = "USAGEFTR.xls"
Public Const CRUNCHDB = "CRUNCHDB.mdb"
Function DAOFromExcelIntoAccessTable()
Dim conn As DAO.Connection
Dim rsSub As DAO.Recordset
Dim rsContract As DAO.Recordset
Dim rsDataSOC As DAO.Recordset
Dim rsHardware As DAO.Recordset
Dim rsBilling As DAO.Recordset
Dim rsRoam As DAO.Recordset
Dim rsRevenue As DAO.Recordset
Dim iErrorHandle As Integer
iErrorHandle = ReadInUsageTable(PATH, USAGE)
End Function
Function ReadInUsageTable(UsageFilePath As String, UsageFileName As String) As Integer
Dim rsUsage As DAO.Recordset
Dim iUsageSht_StartRow As Long
Dim iUsageSht_StopRow As Long
Dim iLast_Row As Long
Dim I As Long
Dim ExcelApp As Excel.Application
Dim xlWrkBk As Excel.Workbook
Dim xlsht As Excel.Worksheet
Set ExcelApp = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(UsageFilePath & "\" & UsageFileName)
Set xlsht = xlWrkBk.Worksheets(1)
With xlWrkBk
If xlsht.Range("B1").Value > 0 Then
iLast_Row = xlsht.Range("B1").End(xlDown).Row
iUsageSht_StopRow = iLast_Row - 1 + iUsageSht_StartRow - 1
Else
MsgBox "There seems to be a problem with the SUB.xls report. Please check and make sure that it is as required", vbOKOnly, "SUB.xls"
End
End If
End With
DoCmd.RunSQL "delete * from Source_Usage"
Set rsUsage = CurrentDb.OpenRecordset("Source_Usage_Advance", dbOpenDynaset)
With xlsht
For I = 2 To iLast_Row
rsUsage.AddNew
rsUsage!BAN = .Cells(I, 1).Value
rsUsage!SUBSCRIBER_NO = .Cells(I, 2).Value
rsUsage!BILL_YEAR = .Cells(I, 4).Value
rsUsage!BILL_MONTH = .Cells(I, 5).Value
rsUsage!BILL_CYCLE = .Cells(I, 6).Value
rsUsage!PRICE_PLAN_CODE = .Cells(I, 7).Value
rsUsage!SPECIAL_NUM_TYPE = .Cells(I, 8).Value
rsUsage!AIRTIME_FEATURE_CD = .Cells(I, 9).Value
rsUsage!ACTION_DIRECTION_CD = .Cells(I, 10).Value
rsUsage!CALL_TYPE = .Cells(I, 14).Value
rsUsage!PERIOD_LEVEL_CODE = .Cells(I, 16).Value
rsUsage!CTN_MINS_LOCAL = .Cells(I, 19).Value
rsUsage!NUM_CALLS_LOCAL = .Cells(I, 20).Value
rsUsage!CHRG_AMT_LOCAL = .Cells(I, 21).Value
rsUsage!CTN_MINS_FM = .Cells(I, 35).Value
rsUsage.Update
Next
End With
Set rsUsage = Nothing
xlWrkBk.Close False
End Function
Private Sub ReadIn_CMD_Click()
Open "D:\HFH Doc\programing\PPA\update\VBA\06_29_2009\timeused.txt" For Append As #1
Print #1, Time
DAOFromExcelIntoAccessTable
Print #1, Time
Close #1
CurrentDb.Close
End Sub