前三篇文章介紹了百度地圖生成器、源代碼編輯器、GPS經緯度批量轉換工具、源代碼編輯器中添加自定義功能按鈕和地圖控件。這些寫好的Java Script代碼雖然可以實現所有期望的結果,但畢竟不是一個HTML文件,不便于傳播和使用,更無法變成一個類似APP的存在。
本文將介紹,如此借助VBA編程,用Excel生成一個HTML文件。雙擊打開文件,會調用百度地圖,并可實現之前代碼所有功能。
01
—
創建FSO對象模型
這里主要用到的是VBA FSO對象模型。文件系統對象FSO的英文全稱是File System Object。FSO對象模型包含在Scripting 類型庫(Scrrun.Dll)中,它同時包含了Drive、Folder、File、FileSystemObject和TextStream五個對象。本例中用到了FileSystemObject,它是FSO對象模型中最主要對象,并提供了一套完整的可用于創建、刪除文件和文件夾,收集驅動器、文件夾、文件相關信息的方法。
創建FSO對象模型:
一、引用法:
首先需要在在工程中引用這個文件,單擊“工程”,“引用”,然后在“引用”對話框中選中“Microsoft Scripting Runtime”前的復選框,然后單擊“確定”。
之后,可以用如下語句定義該對象:
Dim fs As New FileSystemObject;
二、創建法:
通過CreateObject方法創建一個FSO對象:
Set?fs?=?CreateObject("Scripting.FileSystemObject")
本例中使用創建法。創建之后,就可以利用創建的對象模型的方法進行相關操作了。
02
—
使用CreateTextFile方法
CreateTextFile方法創建一個新的文件并返回其TextStream對象,微軟Office 開發人員中心對其解釋如下:
在本例中通過以下代碼,實現了對CreateTextFile方法的調用:
Set f = fs.CreateTextFile(ThisWorkbook.Path & sName & ".txt", True)
上述代碼,創建了一個新文本文件。之后,還需配合使用f.writeline寫入向該文本文件中寫入字符串,也即拼接HTML文件代碼的過程。完整的生成HTML文件的代碼及注釋如下:
Sub?CreateHTML()Dim sName As String '定義一個字符串變量Dim fs As Object '定義一個對象Dim fDim?r?As?RangesName?=?"\差旅協議酒店地圖查詢工具"On?Error?Resume?Next?'容錯處理'如果當前文件夾中已經存在同名文件,則先刪除該文件Kill ThisWorkbook.Path & sName & ".html"'先創建Txt文件Set fs = CreateObject("Scripting.FileSystemObject")Set f = fs.CreateTextFile(ThisWorkbook.Path & sName & ".txt", True) 'writeline寫入字符傳并加入一個換行符???f.writeline?(Worksheets("Code").Range("B2").Value)???'判斷:若為手機版,則使用C2單元格代碼。否則寫入C3單元格代碼。???If?Worksheets("Code").Range("C5")?=?True?Then??????????f.writeline?(Worksheets("Code").Range("C2").Value)???Else????????f.writeline?(Worksheets("Code").Range("C3").Value)???End?If '判斷:若為手機版,則無直線測距功能;若為PC版,則增加直線測距功能;???If?Worksheets("Code").Range("C5")?=?True?Then For Each r In Worksheets("Code").Range("E4:E10") f.writeline (r.Value)????????Next?r???Else For Each r In Worksheets("Code").Range("E3:E10") f.writeline (r.Value)????????Next?r???End?If???f.writeline?(Worksheets("Code").Range("F2").Value) ???Call?CombinePlotter ’調用字符串合并函數???f.writeline?(MarkerPlotter)???f.writeline?(Worksheets("Code").Range("G2").Value)f.Close'將生成的 TXT文件重命名為HTMLName ThisWorkbook.Path & sName & ".txt" As ThisWorkbook.Path & sName & ".html"End Sub
代碼說明:
1.整段代碼功能是將下圖中著色單元格內容通過f.writeline 寫入HTML文件,其本質上是對原始HTML文件代碼進行分隔,然后在分隔處插入自定義的部分。
2.這里Call CombinePlotter調用了一個自定義子過程,其作用是將之前生成的協議酒店列表組裝成一個字符串,然后寫入txt文件的指定位置。
其中子過程CombinePlotter的代碼如下:
Sub CombinePlotter()Dim?r?As?Range,?rng?As?RangeWith Worksheets("酒店列表") Set rng = .Range("S2:S128") '如有多于128行,可修改此處End?WithMarkerPlotter?=?EmptyFor?Each?r?In?rng????If?Not?r?=?""?Then????On?Error?Resume?Next????MarkerPlotter?=?MarkerPlotter?&?r.Value????End?IfNext rMarkerPlotter?=?Right(MarkerPlotter,?Len(MarkerPlotter)?-?1)End Sub
至此,我們便可以將之前已經運行成功的Java Script代碼,重新組裝成TXT文件并通過重命名繼而生成了可傳播可存儲的HTML文件,其保留了所有自定義功能,并可雙擊后通過瀏覽器打開。
03
—
神器的DLL動態鏈接庫
以上VBA代碼運行后只能生成一個HTML文件,但并不會直接用瀏覽器將其打開。如果想在代碼運行后需要直接查看效果,需要用到神奇的"shell32.dll"。
VBA本身并不完美,不具備一些系統底層的操作。但其提供了一種方式可以直接調用由C語言編寫的動態鏈接庫(DLL)中的過程。Windows 操作系統主體由成千上萬個過程(API)以及各種用其他語言編寫的例程構成。通過DLL開發人員可以訪問所有這些Windows 操作系統下的過程及例程。這就意味著,VBA無所不能。在此過程中,DLL被VBA 看做服務和資源的提供者, VBA通過Declare 語句向 DLL發出合法的請求, 從而獲得DLL中過程的使用權。
通過以下代碼,便可以在創建好HTML文件后,直接用瀏覽器打開。這樣做的一個好處是,不必每次運行完程序后,還需找到文件夾下剛剛生成的HTML文件進行雙擊打開操作。
Private Declare Function ShellExecute _ Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hWnd As Long, _ ByVal Operation As String, _ ByVal FileName As String, _ Optional ByVal Parameters As String, _ Optional ByVal Directory As String, _ Optional ByVal WindowStyle As Long = vbMinimizedFocus _ ) As LongSub?MapPlotterExecution()Application.ScreenUpdating?=?False?'暫停刷新屏幕Call?CreateHTMLCall?ShellExecute(0,?"Open",?ThisWorkbook.Path?&?"\差旅協議酒店地圖查詢工具.html")Application.ScreenUpdating?=?True?'恢復刷新屏幕End Sub
如果是一次性生成HTML文件,其優勢尚不明顯;若后續想將其作為一個小工具,按照自定義的列表和要實現的功能,反復地運行程序查看結果,優勢就很明顯了。對比如下:
當如下代碼被注釋掉的時候,點擊顯示地圖運行主程序,只是單純地生成了名為差旅協議酒店地圖查詢工具的HTML文件;而取消注釋啟用代碼后,點擊顯示地圖可直接調用打開瀏覽器。
Call?ShellExecute(0,?"Open",?ThisWorkbook.Path?&?"\差旅協議酒店地圖查詢工具.html")
至此,我們便將所有需求的功能和樣式實現了。如需下載Excel源文件,請回復關鍵詞"工具"。
寫到這里,該連載系列就正式步入了尾聲。借道文件系統對象FSO,我們從VBA成功地生成了HTML文件;通過shell32.dll,我們可以直接調用并打開瀏覽器,打開HTML文件,完美地實現了即時交互。這也意味著我們可以依托百度地圖API接口,基于VBA進行一系列數據地圖IT系統的開發,新世界的大門從此打開。本質上還是用Java Script這種直譯式腳本語言進行的網頁前端編程,其呈現效果堪比任何BI軟件或者其他編程語言。本文只做思路上的啟發和引導,不再進行更深入的分享。如果有興趣的小伙伴可以自行深入探索:
1.分指標顯示區縣色溫圖
2.分指標顯示郵編色溫圖
3.顯示經銷商覆蓋范圍公里環
4.顯示客戶便利度地圖
5.顯示銷量熱力圖
正所謂運用之妙,存乎一心。Excel是一種非常強大的工具,即便在Tableau崛起、Python橫行的今日,依舊有巨大的學習價值。當然,很多Tableau和Python更擅長的事,也不要強Excel所難。尺有所短,寸有所長,畢竟最終目標是探尋最優方案解決工作中面臨的實際問題。
"當你手里有一把錘子時,看什么都像釘子"。不拘泥于某一種工具而是進行多元化的學習,不斷突破自身框架束縛,了解多個領域的最佳實踐,方為上策!
該連載系列文章合集如下:
1.用Excel開發移動端"APP"
2.連載系列【1】Excel開發移動端"APP"
3.連載系列【2】Excel開發移動端"APP"
4.連載系列【3】Excel開發移動端"APP"