There is data in Employee_information sheet and Income_tax sheet and using vba code I am retrieving the data in the third sheet 'Sheet3' based on the condition that Employee_information.emp_no=Income_tax.emp_no.Macro running correctly with no errors but data is not displaying in the sheet3.Displaying 'Getting External data' in the A1 column but no data found.This is my code.Please someone help me asap.
Thanks in Advance Pushpa K
Code:
Sub Macro3() b = ThisWorkbook.Name currentDir = ThisWorkbook.Path Workbooks(b).Sheets("Sheet3").Activate Application.ScreenUpdating = False On Error Resume Next Worksheets("Sheet3").Range("Table_Query_from_Excel_Files[#All]").Select Selection.Clear With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ "ODBC;DSN=Excel Files;DBQ=" & currentDir & "\" & b & ";DefaultDir=" & currentDir & ";DriverId=1046;MaxBufferS" _ ), Array("ize=2048;PageTimeout=5;")), Destination:=Range("$A$1")).QueryTable '.CommandType = 0 .CommandText = Array( _ "SELECT `Employee_Information$`.`Employee Number`, `Employee_Information$`.`Employee Name`, `Employee_Information$`.`Grade Code`, `Employee_Information$`.`Designation Code`, `Income_Tax$`.`Total Gross`" _ , _ ", `Income_Tax$`.`Total Basic`, `Income_Tax$`.`Total DA`, `Income_Tax$`.`Total HRA`" & Chr(13) & "" & Chr(10) & "FROM `" & currentDir & "\" & b & "`.`Employee_Information$` `Employee_Information$`, `" & currentDir & "\" & b & "`.`Income_Tax$` `Income_Tax$`" & Chr(13) & "" & Chr(10) & "WHERE `Employee_Information$`.`Employee Number` = `Income_Tax$`.`Employee Number`" _ ) .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = "Table_Query_from_Excel_Files" .Refresh BackgroundQuery:=False End With End Sub
