I have recorded Union Query in MS Excel , and want to modify its path to Active Workbook without selecting or browsing any file
Can anyone help me to modify Workbook Path in recorded Macro to Active workbook ?
Can anyone help me to modify Workbook Path in recorded Macro to Active workbook ?
PHP:
Sub FO_PFO_Union_Table()
Cells.DeleteCells.DeleteApplication.Run "delete_Connections"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ "ODBC;DSN=Excel Files;DBQ=C:\USERS\ZUBAI_000\DESKTOP\Automated LD Record R-1.xlsm;DefaultDir=C:\USERS\ZUBAI_000\DESKTOP;DriverId=790;" _ ), Array("MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$C$1")). _ QueryTable '.CommandType = 0 .CommandText = Array( _ "SELECT `'PFO R1$'`.Outage__Type, `'PFO R1$'`.Month, `'PFO R1$'`.`Cap# Capacity__MWh`, `'PFO R1$'`.`Add#Cap#__Capacity MWh`, `'PFO R1$'`.`Wt#Cap#__MWh`, `'PFO R1$'`.`Wt#Add#Cap#__MWh`, `'PFO R1$'`.`Wt#" _ , _ "Total#__MWh`" & Chr(13) & "" & Chr(10) & "FROM `C:\Users\zubai_000\Desktop\Automated LD Record R-1.xlsm`.`'PFO R1$'` `'PFO R1$'`" & Chr(13) & "" & Chr(10) & "UNION ALL" & Chr(13) & "" & Chr(10) & "SELECT `FO$`.Outage__Type, `FO$`.Month, `FO$`.`Cap# Capacity__MWh`, `FO$`.`Add#Cap#__Ca" _ , _ "pacity MWh`, `FO$`.`Wt#Cap#__MWh`, `FO$`.`Wt#Add#Cap#__MWh`, `FO$`.`Wt#Total#__MWh`" & Chr(13) & "" & Chr(10) & "FROM `C:\Users\zubai_000\Desktop\Automated LD Record R-1.xlsm`.`FO$` `FO$`" & Chr(13) & "" & Chr(10) & "" _ ) .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 Range("A1").Select End Sub