VBA程式,如何將2個特定字串間之字串取出

情境說明:
Excel表,第一列為標題列，A欄為空白，B欄為超連結文字，C欄為超連結網址(如下所示)

        A                         B                                        C
1                    examples1公司超連結簡介    https://www.example1.com.tw/Link?pageId=12345&type

想將C欄中,取出 pageId=  與 &type 間,之數字 取出,並將它複製至 A欄位中，應該如何做


PROMPT提示訊息下法:
excel  表,第一列為標題列,請用VBA程式,將C欄位中,pageId=  右邊字串,到 &type=  為止之字串,複製到A欄位

Sub CopyString()   ' 取出網址列特定編號,自C欄位,複製至 A欄位
    Dim lastRow As Long
    Dim i As Long
    Dim str As String

    ' 取得最後一列的行數
    lastRow = Cells(Rows.Count, "C").End(xlUp).Row

    ' 逐行處理，從第二列開始
    For i = 2 To lastRow
        ' 判斷C欄位中是否包含 "pageId="
        If InStr(1, Cells(i, "C").Value, "pageId=") > 0 Then
            ' 取得 "pageId=" 右邊的字串
            str = Mid(Cells(i, "C").Value, InStr(1, Cells(i, "C").Value, "pageId=") + Len("pageId="))
            ' 判斷是否包含 "&type="
            If InStr(1, str, "&type=") > 0 Then
                ' 取得 "&type=" 左邊的字串
                str = Left(str, InStr(1, str, "&type=") - 1)
            End If
            ' 將字串複製到A欄位
            Cells(i, "A").Value = str
        End If
    Next i
End Sub

WebSite updating and migrating 網站更新建罝移轉參考資訊

 公司網站更新移轉建置參考資訊

一、需求及功能面向

☺開發廠商風評考慮?(服務經驗及口碑、系統人員服務量能與品質)
☺開發廠商是否充份掌握既有網頁功能，如何將舊網頁資料無痛移轉(移轉計劃合宜度及應變方案處置規劃，是否完善?)
☺開發廠商是否充份瞭解，並與各業務部門使用需求進行訪談並給予建議，達到更換需求目的?(如:訂單管理、電子報、前台及後台介面方便度等)
☺行銷部分SEO紀錄如何管理調校?(如:客戶瀏覽分析與制定管理策略)
☺跨平台規劃定位考量(如:不同瀏覽器支援、手機支援、圖片影像、網站效能及流量考量?)

二、安全性考量面向

☺設計架構是否有考量更新便利性，如採開放源碼元件,如:PHP、JAVA 、Tomcat、Linux、Jquery等後續多久更新?如何更新,權責應清楚?(作業系統面、資料庫面、網站程式面等遭遇問題分工機制?)
☺網站是否有使用HTTPS通訊協定，後續金鑰定期更換機制?
☺網站開發驗收前及程式大改版後，是否需做原始碼安全性檢測(如:OWASP TOP10檢測)
☺網站是否有CAPTCHA防止機器人暴力破解
☺網站使用者，是否有提供定期自動要求變更密碼、密碼複雜度要求、密碼三代不重覆、忘記密碼處理機制
☺安全性日誌，是否保留(如:作業系統LOG、網頁伺服器LOG)，並建議定期備份與檢視

☺如何規劃災害還原處理?系統備份及異地還原機制，多久還原測試(如:遭勒索軟體加密後,異地備份整機還原測試)

三、版更及後續維運

☺公司是否有足夠人力，對新系統持續維運或需委託原開發廠商維運?
☺版本如何更新?(如:上架資料有問題、上架功能未達公司需求等，如何修正或調整?)
☺版權及原始碼提供?
☺程式碼及功能如需擴增更新，費用如何計算，方便評估後續委外維運成本
☺網頁維護廠商，後續售後服務為何?(叫修後於幾天內完成)
☺遠端維護，如何對搖控端主機進行安全性要求?(如:限縮指定來源IP、對方主機是否有定期更新、是否裝防毒軟體並維持最新、是否裝P2P軟體等)、是否通過ISO27001、相關人等填寫保密切結書?

四、教育訓練及使用手冊，教導同仁如何操作及使用

AI品牌相關工具，初步瞭解分析(請AI協助)

為儘快對AI工具粗淺認識，藉由AI(ChatGPT)協助。

(以下各家AI工具，回答出來不見得相同，僅供參考)

工具名稱	功能面強向	費用	語系支援	優點
CHATGPT	聊天機器人、文字生成、翻譯、問答	免費、付費	多國語言	功能強大、語言能力佳、價格實惠
BING	搜尋、翻譯、地圖、雲端儲存	免費	多國語言	功能齊全、使用介面簡潔、普及度高
BARD(Gemini)	聊天機器人、文字生成、翻譯、問答	免費	多國語言	功能強大、語言能力佳、不斷更新
Claude	生成文字、翻譯語言、撰寫不同創意內容	免費	多國語言	提供上傳檔案(如PDF檔)可協助分析重點、語言能力佳
Perplexity	文字生成、翻譯、問答	免費、付費	多國語言	功能強大、語言能力佳、價格實惠
Copilot	程式碼輔助	付費	多國語言	程式碼輔助效果佳、可節省開發時間
ChatEverywhere	程式碼輔助、聊天機器人	免費、付費	多國語言	可在多個平台使用、功能齊全
Monica	時間管理、任務管理	免費、付費	多國語言	功能多元、可自訂介面、價格實惠
Notion	筆記、待辦事項、協作	免費、付費	英文為主	功能多元、可自訂介面、協作效率高

 PROMPT提示訊息下法:

請協助將下列AI品牌工具(如:CHATGPT、BING、BARD(gemini)、Notion、Perplexity、Claude、Copilot、ChatEverywhere、Monica),分別以表格及繁體中文方式呈現,進行初步瞭解,並包括功能面、費用、語系支援、優點，進行分析。

 

PROMPT詢問範例: ( 想瞭解最近資訊議題 AUTO ML機器學習 或 gemini 教學詢問方式,抽換Google gemini關鍵字,可查YOUTUBE網站與關鍵字相關領域學習參考資訊)，可向AI網站詢問

Google geminiの応用的な使い方に関連するYOUTUBEチャンネルを教えてください

延續先前分公司，各自單獨建立分頁  之範例

Excel 分頁過多(分公司多,以致於移動分頁困擾)，希望於每個分頁的K1儲存格(即各個分公司 分頁K1儲存格)，
再設定一個回總索引頁(即「 各分公司索引清單」)


Perplexity.ai
藉由Perplexity AI(ChatGPT)協助將每個各自獨立分公司分頁中的K1儲存格，建立回總索引頁(即「 各分公司索引清單」)之超連結，方便作業。
PROMPT提示訊息下法:
EXCEL表，有多個分頁，其中 各分公司索引清單，已有指向各分頁之超連結，請用EXCEL VBA程式，幫忙於 各分頁中 K1儲存格，增設  各分公司索引清單 超連結 ,並將K1更名為  回索引清單。

Sub AddHyperlinkAndRename()
    Dim ws As Worksheet
    Dim wsRef As Worksheet
    Dim LastRow As Long
    Dim LastCol As Long
    Dim i As Long
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "各分公司索引清單" Then
            LastRow = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
            LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            
            For i = 2 To LastRow
                ws.Range(ws.Rows.Count, i).Value = ""
                ws.Range(ws.Rows.Count, i).EntireRow.Hidden = True
            Next i
            
            ws.Range("K1").Value = "回索引清單"
          
            
              ' 在各分頁的K1儲存格增設超連結
              
              ws.Range("K1").Hyperlinks.Add Anchor:=ws.Range("K1"), Address:="", SubAddress:="'" & "各分公司索引清單" & "'!A1"
                    
            
        End If
    Next ws
End Sub


請按照以下步驟使用此程式：
STEP1:開啟您的Excel檔案。
STEP2:按下Alt + F11進入VBA編輯器。
STEP3:在VBA編輯器中，插入一個新的模組Module。
STEP4:將上述VBA程式碼，複製並貼上到新的模組Module中。
STEP5:在VBA編輯器中，按下F5運行程式。

 

相關應用:

PivotTable樞鈕分析表，搭配Formula公式運用，詳列出   各個" 部門別"之擁有  台數之報表產製

用EXCEL VBA程式，各自單獨建立分公司分頁表，並增設超連結方便索引

因業務需要，有5個分公司(甲~戊)業務要管，想用EXCEL表，

提供給各單位填報回傳資料進行管理，並以分公司名稱 各自單獨建立1個工作表，

同時增設超連結方便索引 。

EXCEL表已先建立好各分公司名稱於A欄位中，如下:
         A
1  分公司名稱  
2  甲分公司
3  乙分公司
4  丙分公司
5  丁分公司
6  戊分公司


藉由AI(ChatGPT)協助建立各自建立分公司獨立分頁，並提供超連結設置，方便檢索

PROMPT提示訊息下法:
EXCEL表中，自A2儲存格起,有多個分公司 放於A欄位中,請提供VBA程式，幫忙建立 分公司分頁 ，並且設置超連結，方便檢索


Sub CreateCompanySheets()
    Dim ws As Worksheet
    Dim company As Range
    
    ' 在第一個工作表中建立一個名為 「 各分公司索引清單」的分頁
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "各分公司索引清單"
    
    ' 在「 各分公司索引清單」分頁中設置標題
    ws.Range("A1").Value = "分公司"
    
    ' 從A2儲存格起，逐一遍歷分公司名稱
    For Each company In ThisWorkbook.Sheets(1).Range("A2:A" & ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row)
        ' 檢查分公司分頁是否已存在
        If Not SheetExists(company.Value) Then
            ' 如果分公司分頁不存在，則建立一個新的分頁
            ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = company.Value
        End If
        
        ' 在「 各分公司索引清單」分頁中設置超連結
        ws.Hyperlinks.Add Anchor:=ws.Range("A" & ws.Cells(Rows.Count, 1).End(xlUp).Row + 1), _
                          Address:="", _
                          SubAddress:="'" & company.Value & "'!A1", _
                          TextToDisplay:=company.Value
    Next company
    
    ' 回到  第一個工作表
    ThisWorkbook.Sheets(1).Activate

End Sub

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    
    SheetExists = False
    For Each ws In ThisWorkbook.Sheets
        If ws.Name = sheetName Then
            SheetExists = True
            Exit Function
        End If
    Next ws
End Function


請按照以下步驟使用此程式：

STEP1:開啟您的Excel檔案。
STEP2:按下Alt + F11進入VBA編輯器。
STEP3:在VBA編輯器中，插入一個新的模組Module。
STEP4:將上述VBA程式碼，複製並貼上到新的模組Module中。
STEP5:在VBA編輯器中，按下F5運行程式。

VBA程式，將會在您的Excel檔案中建立一個名為「 各分公司索引清單」的分頁，並在該分頁中列出 所有分公司的超連結。點擊超連結即可跳轉到相對應的分公司 分頁 。

將多個不同分公司EXCEL成本花費表,合併成單一EXCEL總表

總公司有許多分公司,甲、乙、丙分公司,各自維護獨立 成本花費EXCEL報表,每月需匯整成單一EXCEL總表中

甲、乙、丙分公司各自有成本花費EXCEL報表,且它們成本花費報表  ,其首欄位都有相同花費成本細項，但欄位放置位置都不一定相同

匯整甲、乙、丙分公司，成本花費EXCEL報表欄位全數欄位有    地點,姓名,加班費,差旅費,雜費,租賃費,其它 等欄位

所以,總公司,每月均需匯整甲、乙、丙分公司之成本花費EXCEL報表,合併成單一 EXCEL成本花費總表

To merge the three Excel files into a single file named "MergeOK.xlsx" in the "總公司" folder which located in the D drive,

藉由AI(ChatGPT)寫合併EXCEL
PROMPT提示訊息下法:
在D槽中有 總公司 資料夾，存放3個EXCEL檔,每份表格的首欄為標題,分別有地點,姓名,加班費,差旅費,雜費,租賃費,其它 欄位,每個表格的首欄標題位置皆不同,請寫PYTHON程式,將3個EXCEL表格,合併成 MergeOK.XLSX


import pandas as pd

# File paths 甲分公司檔名為file1.xlsx 、乙分公司檔名為file2.xlsx、丙分公司檔名為file3.xlsx

folder_path = 'D:/總公司/'
file_names = ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']

# Read each Excel file into a DataFrame
dfs = []
for file_name in file_names:
    file_path = folder_path + file_name
    df = pd.read_excel(file_path)
    dfs.append(df)

# Merge the DataFrames
merged_df = pd.concat(dfs)

# Save the merged DataFrame to a new Excel file
output_file = folder_path + 'MergeOK.xlsx'
merged_df.to_excel(output_file, index=False)


相關查詢:
How to join Excel data from Multiple files?

複数のExcelファイルを1つにまとめる方法？


後記:
合併多個EXCEL檔成單一檔方法很多,可以用EXCEL本身功能,或用VBA、或用PYTHON,如果要改成VBA，本範例是用PYHTON，如要使用VBA，只要將PROMPT提示訊息下法，將上面  請寫PYTHON程式  ，改成 EXCEL VBA程式即可

用PYTHON程式,對Disk Analysis磁碟空間剖析

隨著儲存空間愈做愈大，如果沒有做管理將造成備份時間、儲存空間成本的增加、使用效能下降等，因此對磁碟儲存狀態剖析管理就相對重要，可透過PYTHON程式，對磁碟機進行分析檔案目錄、檔名、修改日期、檔案大小進行分析，將結果產出至OUTPUT.TXT，方能掌控磁碟空間之使用(以下為對D槽進行分析)。

import os
from datetime import datetime, timedelta

def analysis_directory(path):
    with open('OUTPUT.TXT', 'w', encoding='UTF-8') as f:
        for root, dirs, files in os.walk(path):
            for file in files:
                file_path = os.path.join(root, file)
                file_size = os.path.getsize(file_path)
                file_date = os.path.getmtime(file_path)
                file_date_str = datetime.fromtimestamp(file_date).strftime('%Y/%m/%d %H:%M:%S')
                f.write(f'{file_path}\t{file_date_str}\t{file_size}\n')

analysis_directory('d:\\')      # 將 'D:\' 改為您想要剖析的磁碟機路徑

 

備註:

上面程式，測試如有型態錯誤訊息,可做調整

#程式開頭，另再加入此行
import time 

#請將datetime物件，替換成下面time物件

datetime.fromtimestamp(file_date).strftime('%Y/%m/%d %H:%M:%S')

#改用time物件

time.strftime('%Y-%m-%d %H:%M:%S')

########################################################

除上面剖析方式外，可搭配DOS指令 TREE 樹狀圖型結構，快速檢視檔案架構

步驟一: 開始  | 執行 | cmd   ,進入DOS 命令提示列

步驟二:  tree /f d: > d:\TreeView.txt     

(將D槽檔案結構，結果產出至   d:\TreeView.txt     )