Uploading data into a Table from Excel - Values Check (1 Viewer)

[sonaljain123]

Hi All,

I have a VB Module that I use on a daily basis to upload a set of data containing 2599 rows and two columns (one for account number, other for balance value).

I want to include a feature in the Module program where

1) I can check if on any day the number of rows become less or more than 2599

2) I can tally the account numbers from the previous day and see if they match with today's account numbers, if not, if it can return a message saying a "account number ABCDE added or removed"

Following is the code that I am running for now:

Public Sub importFile()
Dim inDate As String
Dim iDate As String
Dim iFDate As String
inDate = InputBox("Please enter the date for which you would like to add the data in mm/dd/yyyy format")
iFDate = Format(inDate, "mm/dd/yyyy")
iDate = Format(inDate, "mmddyy")
ImportProtected iFDate, "K:\CLE03\M\daily comparison_ " & iDate & ".xls", ""
End Sub
Private Sub ImportProtected(iDate As String, strFile As String, strPassword As String)
Dim oExcel As Object, oWb As Object
Dim iQuery1 As String
Dim iQuery2 As String
Dim iQuery3 As String
Dim iQuery4 As String
Dim iQuery5 As String
DoCmd.SetWarnings False
Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, Password:=strPassword, ReadOnly:=True)
DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, tablename:="Temp", FileName:=strFile, Hasfieldnames:=True, Range:="daily comparison!B"
oWb.Close SaveChanges:=False
oExcel.Quit
Set oExcel = Nothing
iQuery5 = "SELECT [F1],[UPDATED thru###] INTO Temp_Data FROM Temp;"
DoCmd.RunSQL iQuery5
iQuery1 = "INSERT INTO HD_GL_ACCT_BALANCE ( [DATE], GL_ACCOUNT_NO, GL_BALANCE ) "
iQuery2 = "SELECT distinct #" & iDate & "# AS [DATE], Mid([F1],1,InStr([F1]," & """" & "-" & """" & ")-1) AS GL_ACCOUNT_NO, Nz(Temp_Data.[UPDATED thru###],0) AS GL_BALANCE "
iQuery3 = "FROM Temp_Data WHERE (((Temp_Data.F1) Is Not Null) AND ((InStr([F1]," & """" & "-" & """" & "))<>0)); "
iQuery4 = iQuery1 & iQuery2 & iQuery3
DoCmd.RunSQL iQuery4
DoCmd.DeleteObject acTable, "Temp"
DoCmd.DeleteObject acTable, "Temp_Data"
DoCmd.SetWarnings True
End Sub

Please suggest changes...

Thank you!