แปลงไฟล์ xls เป็น xlsx ด้วย vba

ปกติ แปลงไฟล์ .xls เป็น .xlsx กันอย่างไร? เพียงแค่เปิดเอ็กเซลขึ้นมา แล้วก็ “save as” หรือ “บันทึกเป็น” ง่าย ๆ แค่นั้นใช่มั้ย? แต่ถ้ามีไฟล์จำนวนมากล่ะ สมมติในตัวอย่างนี้มีจำนวนมากกว่า 5,000 ไฟล์ งานง่าย ๆ ก็กลายเป็นงานน่าเบื่อถ้าต้องมาทำทีละไฟล์ เรามาเขียนโค้ดเพื่อช่วยทำงานกันดีกว่า

เจอปัญหาอย่างหนึ่งมา คือได้รับไฟล์ .xls จากแหล่งหนึ่งมา ปรากฏว่า ไฟล์เหล่านี้ ไม่สามารถใช้โค้ดไพธอนให้อ่านได้ ไม่ว่าจะพยายามแก้ไขโค้ดอย่างไรก็ตาม

ก็เลยเปิดไฟล์ .xls ดู ก็เจออะไรแปลก ๆ ตั้งแต่เปิดมาก็พบ pop up เตือน

The file format and extension of “ชื่อไฟล์” don’t match. The file could be corrupted or unsafe. Unless you trust its source, don’t open it. Do you want to open it anyway?

แน่นอน ว่าเราก็ต้องเปิดมัน

และเมื่อ save as แล้ว พบว่าประเภทไฟล์ที่ขึ้นมาให้อัตโนมัติ กลายเป็น XML Spread Sheet 2003 (.xml)

เดาว่า ต้นทางคง export ไฟล์ .xls นี้ด้วยอะไรสักอย่างแบบไม่ค่อยสนใจคนรับปลายทาง คือไม่ได้ export ออกมาเป็น xls จริง ๆ และเมื่อพบปัญหานี้ก็ได้แจ้งแจ้งต้นทางว่ามีปัญหาแบบนี้ ก็คงเป็นแค่แจ้งให้ทราบ คือรับรู้เฉย ๆ ไม่สนใจที่จะแก้ไขอะไรทั้งสิ้น คือถ้าปลายทางเปิดได้ก็คือเปิดได้ ฉันทำตามหน้าที่แล้ว ไม่แก้ซะอย่าง มีอะไรมั้ย?

ทำงานร่วมกับคนแบบนี้ก็ได้แต่ทำใจ และต้องหาวิธีแก้ไขปัญหาเอง

จริง ๆ วิธีแก้ปัญหานี้ไม่ยากเลย แค่ใช้เอ็กเซลเปิดไฟล์แล้วก็เซฟเป็นไฟล์ใหม่ ง่าย ๆ แค่นี้เอง

แต่ปัญหาคือมีประมาณ 5,000 กว่าไฟล์ ถ้าต้องเสียเวลาเปิดไฟล์และเซฟใหม่ไฟล์ละ 1 นาที อาจจะต้องเสียเวลาถึง 5,000 นาที หรือประมาณ 83 – 84 ชั่วโมง ทำงานวันละ 8 ชั่วโมงแบบไม่หยุดพักเลยก็ประมาณ 11 วัน

บ้าบอ!

ด้วยเหตุนี้แหละ การเขียนโค้ดเพื่อช่วยให้การทำงานซ้ำ ๆ มันไม่น่าเบื่อจนเกินไป

จะเขียน VBA เพื่อใช้สำหรับงานนี้ โดยใช้ ChatGPT สุดที่รักช่วยเขียนโค้ด ซึ่ง DefExcel ได้เขียน prompt สำหรับเป็นแนวทางให้ ChatGPT ข่วยเชียนดังนี้

I need VBA code for use in Excel task to auto convert .xls file to .xlsx file
[Step 1]
Make a pop up to select source folder of .xls file
[Step 2]
Make a pop up to select destination folder of .xlsx file
[Step 3]
Convert .xls file in source folder to .xlsx file in destination folder

แปลเป็นไทยก็คือ ต้องการโค้ด VBA เพื่อใช้ในงาน Excel เพื่อแปลงไฟล์ .xls เป็นไฟล์ .xlsx โดยอัตโนมัติ
[ขั้นตอนที่ 1] สร้างป๊อปอัปเพื่อเลือกโฟลเดอร์ต้นทางของไฟล์ .xls
[ขั้นตอนที่ 2] สร้างป๊อปอัปเพื่อเลือกโฟลเดอร์ปลายทางของไฟล์ .xlsx
[ขั้นตอนที่ 3] แปลงไฟล์ .xls ในโฟลเดอร์ต้นทางเป็นไฟล์ .xlsx ในโฟลเดอร์ปลายทาง

นอกจากนี้ยังกำชับว่า ขอให้อธิบายโดยละเอียดด้วย — please explain step by step

และนี่คือโค้ด VBA ที่ได้รับจาก ChatGPT

Option Explicit

Sub ConvertXLStoXLSX()
    Dim sourceFolder As String
    Dim destinationFolder As String
    Dim fDialog As FileDialog
    Dim fileName As String
    Dim wb As Workbook
    Dim fso As Object
    Dim filePath As String
    Dim newFilePath As String
    
    ' Initialize FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Step 1: Select Source Folder
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .Title = "Select Source Folder Containing .xls Files"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            MsgBox "No source folder selected. Operation cancelled.", vbExclamation
            Exit Sub
        End If
        sourceFolder = .SelectedItems(1)
    End With
    
    ' Step 2: Select Destination Folder
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    With fDialog
        .Title = "Select Destination Folder for .xlsx Files"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            MsgBox "No destination folder selected. Operation cancelled.", vbExclamation
            Exit Sub
        End If
        destinationFolder = .SelectedItems(1)
    End With
    
    ' Ensure destination folder path ends with a backslash
    If Right(destinationFolder, 1) <> "\" Then
        destinationFolder = destinationFolder & "\"
    End If
    
    ' Step 3: Convert .xls to .xlsx
    fileName = Dir(sourceFolder & "\*.xls") ' Get first .xls file
    
    If fileName = "" Then
        MsgBox "No .xls files found in the selected source folder.", vbInformation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Do While fileName <> ""
        filePath = sourceFolder & "\" & fileName
        newFilePath = destinationFolder & fso.GetBaseName(fileName) & ".xlsx"
        
        ' Open the .xls file
        Set wb = Workbooks.Open(filePath)
        
        ' Save as .xlsx
        wb.SaveAs Filename:=newFilePath, FileFormat:=xlOpenXMLWorkbook
        
        ' Close the workbook
        wb.Close SaveChanges:=False
        
        ' Get next .xls file
        fileName = Dir
    Loop
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    MsgBox "Conversion completed successfully!", vbInformation
End Sub

เฮ้ย!!! โค้ดนี้ใช้ได้จริงด้วย!!!

แปลงไฟล์ xls เป็น xlsx

เปิดเอ็กเซลล์

กดปุ่ม ALT + F11 เพื่อเปิด Visual Basic for Applications (VBA) editor

(อ่านวิธีการเปิด Visual Basic for Applications editor)

คลิด Insert > Module เพื่อสร้าง Module ใหม่

สำเนาโค้ดข้างต้นลงไป

ปิด VBA editor เพื่อกลับไปที่ Excel

เรียกใช้แมโคร

กด ALT + F8 เพื่อเปิดกล่องโต้ตอบ Macro

เลือก ConvertXLStoXLSX จากรายการ (เพราะเราตั้งชื่อไว้แบบนี้

คลิก Run

จะปรากฏหน้าต่างให้เลือกโฟลเดอร์ต้นทางที่มีไฟล์ .xls เลือกโฟลเดอร์ที่ต้องการแล้วคลิก OK

จากนั้นจะมีอีกหน้าต่างปรากฏขึ้นให้เลือกโฟลเดอร์ปลายทางที่จะบันทึกไฟล์ .xlsx เลือกโฟลเดอร์ที่ต้องการแล้วคลิก OK

จากนั้นก็รอ ปล่อยให้เป็นหน้าที่ของโค้ดจัดการประมวลผลไฟล์ .xls ทั้งหมดในโฟลเดอร์ต้นทาง แปลงเป็น .xlsx และบันทึกไว้ในโฟลเดอร์ปลายทาง

เมื่อเสร็จสิ้น กล่องข้อความจะยืนยันการแปลงสำเร็จ

ข้อควรระวัง

สำรองไฟล์ทุกครั้งเพื่อความไม่ประมาท: ก่อนเรียกใช้แมโคร ควรสำเนาไฟล์ .xls เดิมเพื่อป้องกันเรื่องไม่คาดคิด

การตั้งค่าความปลอดภัย: ตรวจสอบให้แน่ใจว่าการตั้งค่าเอ็กเซลล์ของท่านอนุญาตให้แมโครทำงานได้ อาจต้องปรับการตั้งค่าความปลอดภัยของแมโครในเอ็กเซล ผ่าน File > Options > Trust Center > Trust Center Settings > Macro Settings

ทำความเข้าใจโค้ด

ในหัวข้อนี้ ทำความเข้าใจโค้ดนี้ เป็นคำอธิบายโค้ดจาก ChatGPT ซึ่งได้ขอให้แยกย่อยว่าแต่ละส่วนของโค้ดทำอะไรบ้าง:

ประกาศตัวแปร:

sourceFolder และ destinationFolder: เก็บเส้นทางของโฟลเดอร์ต้นทางและปลายทาง

fDialog: สร้างกล่องโต้ตอบสำหรับเลือกโฟลเดอร์

fileName: เก็บชื่อไฟล์ .xls ในขณะวนลูป

wb: แทนไฟล์ .xls ที่เปิดอยู่

fso: ใช้สำหรับจัดการการทำงานของระบบไฟล์

filePath และ newFilePath: เก็บเส้นทางเต็มของไฟล์ต้นทางและไฟล์ .xlsx ใหม่

เริ่มต้นใช้งาน FileSystemObject:

Set fso = CreateObject(“Scripting.FileSystemObject”): สร้างอินสแตนซ์เพื่อใช้เมธอดเกี่ยวกับระบบไฟล์ เช่น GetBaseName

เลือกโฟลเดอร์ต้นทาง:

ใช้ Application.FileDialog(msoFileDialogFolderPicker) เพื่อเปิดกล่องโต้ตอบเลือกโฟลเดอร์

ถ้าผู้ใช้ยกเลิกกล่องโต้ตอบ โค้ดจะแสดงข้อความและออกจากโปรแกรม

เลือกโฟลเดอร์ปลายทาง:

ทำคล้ายกับการเลือกโฟลเดอร์ต้นทาง

ตรวจสอบให้แน่ใจว่าเส้นทางของโฟลเดอร์ปลายทางลงท้ายด้วยเครื่องหมายแบ็กสแลช () เพื่อการต่อเส้นทางไฟล์ที่ถูกต้อง

กระบวนการแปลง:

fileName = Dir(sourceFolder & “\*.xls”): ดึงไฟล์ .xls แรกในโฟลเดอร์ต้นทาง

ตรวจสอบว่ามีไฟล์ .xls อยู่หรือไม่ ถ้าไม่มีแจ้งให้ผู้ใช้ทราบและออกจากโปรแกรม

ปิดการอัปเดตหน้าจอและการแจ้งเตือนเพื่อเพิ่มความเร็วและหลีกเลี่ยงการแจ้งเตือนที่ไม่จำเป็น

วนลูปผ่านแต่ละไฟล์ .xls:

สร้างเส้นทางเต็มของไฟล์ต้นทางและไฟล์ .xlsx ใหม่

เปิดไฟล์ .xls

บันทึกเป็น .xlsx โดยใช้ wb.SaveAs และรูปแบบ xlOpenXMLWorkbook

ปิดเวิร์กบุ๊กโดยไม่บันทึกการเปลี่ยนแปลง (เนื่องจากบันทึกเป็น .xlsx แล้ว)

เปิดการแจ้งเตือนและการอัปเดตหน้าจอหลังจากวนลูป

แจ้งให้ผู้ใช้ทราบเมื่อเสร็จสิ้น

แก้ไขโค้ด

โค้ดด้านบนนี้ หากว่าในโฟลเดอร์ปลายทางมีไฟล์ .xlsx ชื่อเดียวกันอยู่แล้ว แมโครนี้จะเขียนทับโดยไม่แจ้งเตือน เพื่อป้องกันการเขียนทับโดยบังเอิญ ท่านสามารถเพิ่มการตรวจสอบเพื่อดูว่าไฟล์ปลายทางมีอยู่แล้วหรือไม่

    Do While fileName <> ""
        filePath = sourceFolder & "\" & fileName
        newFilePath = destinationFolder & fso.GetBaseName(fileName) & ".xlsx"
        
        ' Check if the destination file already exists
        If Not fso.FileExists(newFilePath) Then
            ' Open the .xls file
            Set wb = Workbooks.Open(filePath)
            
            ' Save as .xlsx
            wb.SaveAs Filename:=newFilePath, FileFormat:=xlOpenXMLWorkbook
            
            ' Close the workbook
            wb.Close SaveChanges:=False
        Else
            MsgBox "File " & newFilePath & " already exists. Skipping conversion for this file.", vbInformation
        End If
        
        ' Get next .xls file
        fileName = Dir
    Loop

หวังว่าจะเป็นประโยชน์กับท่านบ้าง