sairam123
11-29-2013, 03:08 PM
Hi,
I need miising records from the records that are inserted into Database.
My VBA excel is uploading one excel.
The code follows here..
CODE: SELECT ALL
Set wbkB = Workbooks.Open(Filename:=path)
Set varSheetB = wbkB.Worksheets(sheetname).Range(strRangeToCheck)
'here iam getting no of rows that are read in the file being uploaded
Rows = LastRow(wbkB.Worksheets(sheetName).Range(strRangeToCheck))
Msgbox " no of rows " rows-1. 'here iam taking off my file header in the count
Dim wbkB As workbook
Dim varsheet As variant
Dim queryB As String
Dim x As Long
Set wbkB = Workbooks.Open (Filename:=path)
Set varsheetB=wbkB.worksheets (sheetName)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn= New ADODB.Connection
Set rs= New ADODB.Connection
Dim cmdSQL As ADODB.Command
Set cmdSQL = NEW ADODB.Command
cn.Open GetDBConnection_Source 'this is for my database connection
Here iam not mentioning my DB
Set cmdSQL.ActiveConnection =cn
'my selected file is XYZ
If filetype = "XYZ"
'Here my code is inserting XYZ file into database.
Dim Query As string
For x = 2 to rows
Query = " insert INTO Myfile_DB ((select CASE when max(my_id)+1 is NULL from myfile_DB.tbl,"_
& "(case when ( ' " & Replace(varSheetB.Range("A" & x).Value, " ' " , " ' " ) & " ' ) = ' ' THEN NULL ELSE ' " & Replace ( varsheetB.Range("A" & x).value, " ' ", " ' ") & " ' end),"_
'And here my code follows for rest of all..
cmdSQL.CommandText = query
cmdSQL.CommandType=adcmdText
cmdSQL.commandTimeout = 0
Debug.Print query
Set rs = cmdSQL. Execute()
Rows= rows+1
Next X
'Here I am getting no of records that are inserted into my DB from rows.
After inserting records into my DB,if any records are not inserted,those missing records need to be
in a separate sheet...
And iam checking my ranges from strRangeToCheck, and strRangeToCheckA
CODE: SELECT ALL
If Activesheet.ComboBox.Text = "XYZ"
sheetname =" abc"
strRangeToCheck = "A1:AW1"
strRangeToCheckA = "A1:AW1"
I have coloumns from range A1 to AW1 in outside file xyz..Those have data.
So i taken those column names in my VBA excel in a separate sheet . Written column names with my sheet A1 to AW1.
Here iam comaring with my sheet columns with outside uploading file column names range A1:AW1.
Totally i need missing records after insertion to database in a new sheet.
I need miising records from the records that are inserted into Database.
My VBA excel is uploading one excel.
The code follows here..
CODE: SELECT ALL
Set wbkB = Workbooks.Open(Filename:=path)
Set varSheetB = wbkB.Worksheets(sheetname).Range(strRangeToCheck)
'here iam getting no of rows that are read in the file being uploaded
Rows = LastRow(wbkB.Worksheets(sheetName).Range(strRangeToCheck))
Msgbox " no of rows " rows-1. 'here iam taking off my file header in the count
Dim wbkB As workbook
Dim varsheet As variant
Dim queryB As String
Dim x As Long
Set wbkB = Workbooks.Open (Filename:=path)
Set varsheetB=wbkB.worksheets (sheetName)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn= New ADODB.Connection
Set rs= New ADODB.Connection
Dim cmdSQL As ADODB.Command
Set cmdSQL = NEW ADODB.Command
cn.Open GetDBConnection_Source 'this is for my database connection
Here iam not mentioning my DB
Set cmdSQL.ActiveConnection =cn
'my selected file is XYZ
If filetype = "XYZ"
'Here my code is inserting XYZ file into database.
Dim Query As string
For x = 2 to rows
Query = " insert INTO Myfile_DB ((select CASE when max(my_id)+1 is NULL from myfile_DB.tbl,"_
& "(case when ( ' " & Replace(varSheetB.Range("A" & x).Value, " ' " , " ' " ) & " ' ) = ' ' THEN NULL ELSE ' " & Replace ( varsheetB.Range("A" & x).value, " ' ", " ' ") & " ' end),"_
'And here my code follows for rest of all..
cmdSQL.CommandText = query
cmdSQL.CommandType=adcmdText
cmdSQL.commandTimeout = 0
Debug.Print query
Set rs = cmdSQL. Execute()
Rows= rows+1
Next X
'Here I am getting no of records that are inserted into my DB from rows.
After inserting records into my DB,if any records are not inserted,those missing records need to be
in a separate sheet...
And iam checking my ranges from strRangeToCheck, and strRangeToCheckA
CODE: SELECT ALL
If Activesheet.ComboBox.Text = "XYZ"
sheetname =" abc"
strRangeToCheck = "A1:AW1"
strRangeToCheckA = "A1:AW1"
I have coloumns from range A1 to AW1 in outside file xyz..Those have data.
So i taken those column names in my VBA excel in a separate sheet . Written column names with my sheet A1 to AW1.
Here iam comaring with my sheet columns with outside uploading file column names range A1:AW1.
Totally i need missing records after insertion to database in a new sheet.