彰化一整天的論壇

 找回密碼
 立即註冊
查看: 1733|回復: 0

如何在Excel把文字處理為urlencode [urlencode excel vba][urlencode函數]

[複製鏈接]
發表於 2014-7-25 09:42:12 | 顯示全部樓層 |閱讀模式
最近因為需要在Excel中作出一個Urlencode的記錄,什麼是Urlencode?

就是例如你在搜尋引擎輸入:我你他,搜尋引擎會幫你搜尋"你我他",但你會看到網址上是顯示:xxx=%E6%88%91%E4%BD%A0%E4%BB%96

%E6%88%91%E4%BD%A0%E4%BB%96=你我他Urlencode後的代碼。

用途是什麼?真是每人不一樣,我只是純粹的分享及記錄下來。 有需要的人也會在搜尋器搜尋urlencode excel vba,有緣也會看到這篇文章。


教學開始:

首先,我相信需要做到urlencode的朋友,電腦能力相信也不會太差,我不作圖文教學,只作純文字教學。

以Excel2010作例

1. 打開你需要使用到urlencode excel vba的文件

2. 並將開發人員的選項顯示出來 – (Excel2010的話:按右上角檔案->選項->自訂工能區->把右邊的開發人員勾上->確定)

3. 回到Excel主視窗後,按開發人員-> Visual Basic->打開了Visual Basic主視窗->頂部選單插入(I)->模組(M)

4. 會出現一個空白的框給你輸入內容,你貼上文章下半部的代碼,然後按儲存(或Ctrl+S)。

5. 如果是舊檔案會有個提示說你錯誤,叫你儲存成其他格式,請選擇:Excel啟用巨集的活頁簿;
 如果是新檔案會有個提示問你儲存什麼,請選擇:Excel啟用巨集的活頁簿

  1. Public Function UrlEncode(ByRef szString As String) As String
  2. Dim szChar As String
  3. Dim szTemp As String
  4. Dim szCode As String
  5. Dim szHex As String
  6. Dim szBin As String
  7. Dim iCount1 As Integer
  8. Dim iCount2 As Integer
  9. Dim iStrLen1 As Integer
  10. Dim iStrLen2 As Integer
  11. Dim lResult As Long
  12. Dim lAscVal As Long
  13. szString = Trim$(szString)
  14. iStrLen1 = Len(szString)
  15. For iCount1 = 1 To iStrLen1
  16. szChar = Mid$(szString, iCount1, 1)
  17. lAscVal = AscW(szChar)
  18. If lAscVal >= &H0 And lAscVal <= &HFF Then If (lAscVal >= &H30 And lAscVal <= &H39) Or _ (lAscVal >= &H41 And lAscVal <= &H5A) Or _ (lAscVal >= &H61 And lAscVal <= &H7A) Then
  19. szCode = szCode & szChar
  20. Else

  21. szCode = szCode & “%" & Hex(AscW(szChar))
  22. End If
  23. Else
  24. szHex = Hex(AscW(szChar))
  25. iStrLen2 = Len(szHex)
  26. For iCount2 = 1 To iStrLen2
  27. szChar = Mid$(szHex, iCount2, 1)
  28. Select Case szChar
  29. Case Is = “0″
  30. szBin = szBin & “0000″
  31. Case Is = “1″
  32. szBin = szBin & “0001″
  33. Case Is = “2″
  34. szBin = szBin & “0010″
  35. Case Is = “3″
  36. szBin = szBin & “0011″
  37. Case Is = “4″
  38. szBin = szBin & “0100″
  39. Case Is = “5″
  40. szBin = szBin & “0101″
  41. Case Is = “6″
  42. szBin = szBin & “0110″
  43. Case Is = “7″
  44. szBin = szBin & “0111″
  45. Case Is = “8″
  46. szBin = szBin & “1000″
  47. Case Is = “9″
  48. szBin = szBin & “1001″
  49. Case Is = “A"
  50. szBin = szBin & “1010″
  51. Case Is = “B"
  52. szBin = szBin & “1011″
  53. Case Is = “C"
  54. szBin = szBin & “1100″
  55. Case Is = “D"
  56. szBin = szBin & “1101″
  57. Case Is = “E"
  58. szBin = szBin & “1110″
  59. Case Is = “F"
  60. szBin = szBin & “1111″
  61. Case Else
  62. End Select
  63. Next iCount2
  64. szTemp = “1110″ & Left$(szBin, 4) & “10″ & Mid$(szBin, 5, 6) & “10″ & Right$(szBin, 6)
  65. For iCount2 = 1 To 24
  66. If Mid$(szTemp, iCount2, 1) = “1″ Then
  67. lResult = lResult + 1 * 2 ^ (24 – iCount2)
  68. Else: lResult = lResult + 0 * 2 ^ (24 – iCount2)
  69. End If
  70. Next iCount2
  71. szTemp = Hex(lResult)
  72. szCode = szCode & “%" & Left$(szTemp, 2) & “%" & Mid$(szTemp, 3, 2) & “%" & Right$(szTemp, 2)
  73. End If
  74. szBin = vbNullString
  75. lResult = 0
  76. Next iCount1
  77. UrlEncode = szCode
  78. End Function
複製代碼
文章出處: http://skychoy.hk/241

回復

使用道具 舉報

您需要登錄後才可以回帖 登錄 | 立即註冊

本版積分規則

 ㄚ母滴雞湯
 員林香純滴雞精

Archiver|手機版|小黑屋|彰化一整天的論壇(Excel,Office)  

GMT+8, 2020-2-25 11:55 , Processed in 0.109002 second(s), 19 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回復 返回頂部 返回列表