通過vbscript批量更新excel文檔oracle資料源(版本2：ListObjects.refresh方法)

*************************************************** 原文： blog.csdn.net/clark_xu 徐長亮的專欄
** 微信公衆号：clark_blog      
************************************************* 
       
dim xlsApp 'wsript對象
dim xlsWorkBook	'workbook對象
dim xlsSheet		'worksheet對象
dim xlscon			'connection對象
dim xlsquery		'querytable對象
dim i						'connection基數
dim j						'内部循環
dim z						'sheet内部循環

dim fileapp 'wscript檔案對象
dim folderdir
dim folderobject
dim subfolderobject
dim filename
dim subdirpath

'wscriptExcel 對象
Set xlsApp = CreateObject("Excel.Application")
xlsApp.Visible = True
xlsApp.DisplayAlerts=False
'msgbox xlsApp.DisplayStatusBar

'wscript 檔案系統對象
Set fileapp=CreateObject("Scripting.FileSystemObject")

'檔案根目錄
folderdir=InputBox("檔案夾路徑,例如E:\15-VB", MsgTitle)
'folderdir="E:\15-VB"

'folder對象,Subfolder對象
Set folderobject=fileapp.GetFolder(folderdir)
Set subfolderobject=folderobject.SubFolders

'其他excel的文檔的操作
for each i in folderobject.files
 			if i.type="Microsoft Office Excel 97-2003 工作表"  or i.type="Microsoft Office Excel 工作表" then
 			  filename=folderdir&"\"&i.name
 			  'msgbox filename 
        '建立workbooks對象
				set xlsWorkBook=xlsApp.Workbooks.open(filename)
				for j=1 to xlsWorkBook.sheets.count
					set xlsSheet=xlsWorkBook.sheets(j)
					'msgbox xlsSheet.name
					for z=1 to xlsSheet.ListObjects.count
						'msgbox xlsSheet.ListObjects(z)'表_ExternalData_1786
						'msgbox xlsSheet.ListObjects(z).SourceType '3
						msgbox xlsSheet.ListObjects(z).refresh
						'msgbox xlsSheet.ListObjects(z).ListColumns(1) 'TABLE_NAME
						'沒有資料的時候報對象變量未設定 msgbox xlsSheet.ListObjects(z).ListColumns(1).DataBodyRange 'INFT04_SB_GRSDS_SRNSMX
						'msgbox xlsApp.StatusBar
						On Error Resume Next
					next '結束周遊sheet内所有表_external_data
				next'結束周遊book中所有sheet
				sleep 120000
			  xlsWorkBook.Save		
 			end if
next '結束周遊所有excel檔案


'子目錄處理
for each subdirpath in subfolderobject
 			for each i in subdirpath.files
 				if i.type="Microsoft Office Excel 97-2003 工作表"  or i.type="Microsoft Office Excel 工作表" then
 			  	filename=subdirpath&"\"&i.name
 			  	 'msgbox filename 
      		 '建立workbooks對象
					set xlsWorkBook=xlsApp.Workbooks.open(filename)
					for j=1 to xlsWorkBook.sheets.count
						set xlsSheet=xlsWorkBook.sheets(j)
						'msgbox xlsSheet.name
						for z=1 to xlsSheet.ListObjects.count
							'msgbox xlsSheet.ListObjects(z)'表_ExternalData_1786
							'msgbox xlsSheet.ListObjects(z).SourceType '3
							msgbox xlsSheet.ListObjects(z).refresh
							'msgbox xlsSheet.ListObjects(z).ListColumns(1) 'TABLE_NAME
							'沒有資料的時候報對象變量未設定 msgbox xlsSheet.ListObjects(z).ListColumns(1).DataBodyRange 'INFT04_SB_GRSDS_SRNSMX
							'msgbox xlsApp.StatusBar
							'On Error Resume Next
						next '結束周遊sheet内所有表_external_data
					next'結束周遊book中所有sheet
					sleep 120000
			  	xlsWorkBook.Save		
 				end if
			next '結束周遊所有excel檔案
next '結束周遊所有子目錄
		
 '退出操作
xlsWorkBook.Close
xlsApp.Quit

Set xlsSheet=Nothing '釋放記憶體
Set xlscon=Nothing '釋放記憶體
Set xlsWorkBook = Nothing '釋放記憶體
Set xlsApp = Nothing  '釋放Excel對象
Set subfolderobject=Nothing '釋放記憶體
Set folderobject=Nothing '釋放記憶體
Set fileapp=Nothing '釋放記憶體