最近因為手上的案子需要使用 SQL Server Reporting Service 產生大量的報表,但其中有個報表需要產出所有的資料並且分單位來分頁,再匯出成 Excel 檔案,但是再匯出 Excel 檔案後,Excel 檔案裡面的 Sheet Name 不就是 "工作表1"、"工作表2"、"工作表3" 就是 "Sheet1"、"Sheet2"、"Sheet3"來表示,如果再報表中有設定導覽資訊列的話又會在多一個 "文件引導模式" 的頁籤。

但 User 卻是希望匯出後的 Excel Sheet Name 可以根據單位來標示 Sheet Name,由於詢問過 MS 後,無法在 SSRS 中產生這樣的檔案,因此只好退而求其次,建立一個 Script 檔案,來讓 User 自行自動修改要變更的 Excel 檔案。

renameexcel1.png

此圖為變更前的樣子,下圖為變更後的樣子

renameexcel2.png

 


 

接下來就來介紹一下程式,下面程式我用簡短重要的方式說明,就不把完整的程式給列出來了。所以就列出重要的部份,其他大家在自行加工成最佳化吧~

Sub ReNameExcelSheet(ExcelFile)
	Set objExcel = CreateObject("Excel.Application")
	objExcel.Visible = True
	Set objWorkbook = objExcel.Workbooks.Open (ExcelFile)
	
	'先在第一頁取的所有的連結
	Set objWorksheet = objWorkbook.Worksheets(1)
	Set colLinks = objWorksheet.Hyperlinks
	
	i = 2 '由第二的 Sheet 開始
	For Each objLink in colLinks
		'變更 Sheet 的名稱
		Set objWorksheet = objWorkbook.Worksheets(i)
		objWorksheet.Activate
		objWorksheet.Name = objLink.TextToDisplay
		
		'把連結中的網址也變更成顯示名稱
		objLink.SubAddress = Replace(objLink.SubAddress, "工作表" & i, objLink.TextToDisplay)
		objLink.SubAddress = Replace(objLink.SubAddress, "Sheet" & i, objLink.TextToDisplay)

		i= i + 1
	Next
	
	objWorkbook.Save
	objExcel.Quit
End Sub

第 2、3、4行主要是把要變更的 Excel 檔案開啟,其中第三行只要是要把 Excel 打開後,讓 User 看的到 Excel 被打開了,如不想被看到 Excel 跳出視窗可以設定為 False。

第 7、8行,為取得 "文件引導模式" 中的所有連結,使用 objWorksheet.Hyperlinks 取得比較快速,比一個一個儲存格抓取快速很多也很方便,接下來因為我們要從工作表2開始變更工作表名稱,因此第 10 行的 i 設定由 2 開始。

第15行為主要的程式,由於此範例要將 Sheet 的名稱變更為連結的顯示名稱,所以我們將名稱設定為 objLink.TextToDisplay。

由於我們已經將工作表的名稱都置換掉了,那原本的連結就會因此而失效,因此需要將所有的連結修正一下,由於我們的連結是連結是原本的 Excel 檔案中的工作表,因此連結存放於 SubAddress 中並不是再 Address 中,如果是 http:// 連結將會存於 Address 中,因此我們將他置換一下吧!

最後就是將 Excel 檔案存檔並離開。這樣就大功告成了。


創作者介紹
創作者 王圓外 的頭像
王圓外

威力手記本

王圓外 發表在 痞客邦 留言(0) 人氣()