Hi,
I need miising records from the records that are inserted into Database.
My VBA excel is uploading one excel.
The code follows here..
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 iam 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
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..
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 iam 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
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.