通过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 '释放内存