การเขียน VBA เพื่อดึงชื่อไฟล์ และข้อมูลจากไฟล์ Excel เท่านั้น มารวมกันอีกไฟล์หนึง

[WanPalakorn]

ผมไม่มีพื้นฐาน VBA เลยครับ
ลองค้นหาในเน็ตแล้ว แต่ไม่เข้าใจ และทำไม่ได้ครับ
เลยอยากสอบถาม ดังนี้
ผมมีข้อมูล Excel ไฟล์ประมาณ 300 ไฟล์ โดยมีการตั้ง Folder ย่อย ๆ 6 Folder ในการเก็บไฟล์ Excel 300 ไฟล์
รูปแบบการเก็บข้อมูลในไฟล์ Excel ที่เก็บข้อมูลมีลักษณะเหมือนกัน คือ เก็บข้อมูลเป็นรายเดือน จำนวนทั้งสิ้น 5 ปี (12*5 = 60 ชุดข้อมูล)
ตามไฟล์ข้อมูล T1 และ T2 (ข้อมูลสมมติ)

ผมอยากทราบว่ามีสูตรการเขียน VBA อย่างไรเพื่อดึงชื่อไฟล์ทั้ง .xls และ .xlsx และข้อมูลทั้ง 300 ไฟล์มารวมกันในไฟล์รวมทั้งตั้งขึ้นใหม่ โดยไม่ต้องเปิดไฟล์ข้อมูล Excel ย่อย ๆ ทั้ง 300 ไฟล์ เพื่อให้ผมสามารถนำข้อมูลดังกล่าวมาทำการวิเคราะห์และนะสอต่อไปครับ

ขอบคุณสำหรับคำแนะนำทุกท่าน

[snasui]

สำหรับปัญหา VBA จำเป็นต้องเขียนมาเองก่อนตามกฎการใช้บอร์ดข้อ 5 ด้านบนครับ

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

ตัวอย่างการรวมข้อมูลจากหลายไฟล์อยู่ใน Folder ต่างกัน ดูตัวอย่างได้ที่นครับ



ส่วนด้านล่างนี้เฉพาะการรวมข้อมูลจากหลายไฟล์

[WanPalakorn]

ขอบคุณสำหรับ Clip ครับอาจารย์
เด๊่ยวลองศึกษาก่อนนะครับ ติดขัดประการใด ผมจะมาปรึกษาใหม่นะครับ
ตอนนี้เขียนคำสั่งเพื่อ List ไฟล์ใน Folder เดียวได้แล้วครับ

Sub ListFileInFolder()
Directory = "D:\Anamai\SystemTest\TestForm\*.xlsx"
r = 2
ListFile = Dir(Directory, vbNormal)
Do While ListFile <> ""
r = r + 1
Cells(r, 2) = ListFile
ListFile = Dir()
Loop
End Sub

[snasui]

ยินดีด้วยครับ

การโพสต์ Code ดูตัวอย่างตามกฎการใช้บอร์ดข้อ 5 ด้านบน จะได้ง่ายต่อการอ่านเพราะแตกต่างอย่างชัดเจนกับข้อความปกติครับ

[WanPalakorn]

เรียน อ.snasui
ผมลองเขียนคำสั่ง VBA ตาม Clip ที่อาจารย์แนะนำมาปรากฏว่าไม่สามารถดึงชื่อไฟล์จาก SubFolder มาได้ ผมจึงใช้คำสั่งที่ผมเคยเขียนได้ดึงชื่อไฟล์ในแต่ละ SubFolder แทนครับ

แต่ทั้งนี้ผมลองใช้คำสั่งในการ CollectData ตามที่อาจารย์แนะนำ ปรากฏว่ามันขึ้น Debug ผมไม่แน่ใจว่าผิดพลาดประการใด
รบกวนอาจารย์ช่วยแนะนำด้วยครับ
คำสั่งที่ใช้ได้ครับ

Code: Select all

Sub ListFileInFolder()
Directory = "D:\Anamai\SystemTest\TestForm\*.xlsx"
r = 2
ListFile = Dir(Directory, vbNormal)
    Do While ListFile <> ""
    r = r + 1
    Cells(r, 2) = ListFile
    ListFile = Dir()
    Loop
End Sub

คำสั่งในการ Collectdata ติด debug ตรง Set wb = Workbook.Open(strPath(i)) ครับ
** ไฟล์ข้อมูลทั้งหมดต้องอยู่ใน Folder เดียวกันหรือไม่ครับ **

Code: Select all

Sub Colletcdata()
    Dim myFolder As String, mySubFolder As String
    Dim wb As Workbook, s As Worksheet, db As Worksheet
    Dim strPath As Variant, i As Integer, f As Byte
    myFolder = "D:\Anamai\TestVBA\DataTest"
    mySubFolder = Dir(myFolder & "*", vbDirectory)
    strPath = Application.GetOpenFilename(FileFilter:="Excel File(*.xls*),*.xls*", _
        MultiSelect:=True)
    If TypeName(strPath) = "Boolean" Then Exit Sub
    Set db = ThisWorkbook.Sheets(1)
    db.UsedRange.ClearContents
    Application.ScreenUpdating = False
    For i = 1 To UBound(strPath)
        Set wb = Workbook.Open(strPath(i))
        For Each s In wb.Worksheets
            f = IIf(db.Range("a1").Value = "", 0, 1)
            If s.Range("a1").Value <> "" Then
               s.UsedRange.Offset(f, 0).Copy
                With db
                    .Range("a" & .Rows.Count).End(xlUp).Offset(f, 0) _
                        .PasteSpecial xlPasteValues
                End With
            End If
         Next s
         wb.Close False
         Application.CutCopyMode = False
    Next i
    Apllication.ScreenUpdating = True
    MsgBox "Finished.", vbInformation
End Sub

[WanPalakorn]

ตอนนี้ผมเขียน VBA เพื่อดึงข้อมูลมาแสดงที่ไฟล์เดียวกันได้แล้วนะครับ
แต่อยากสอบถามว่าผมจะให้ข้อมูลเรียงกันตามชื่อ sheet Y1 ของทุกไฟล์ excel ต้องทำยังไงครับ
และอีกคำถามคือ เราสามารถเขียน VBA กำหนดช่วง cell ในการดึงข้อมูลได้หรือไม่ครับ
ผมใช้ Code VBA นี้ตามที่อาจารย์สอนในคลิป ดึงข้อมูลครับ
จาก Code นี้สามารถแก้ไขเพื่อให้ตรงกับความต้องการตามข้างต้นผมได้หรือไม่ครับ

ขอบคุณทุกคอมเม้นต์และคำแนะนำครับ

Code: Select all

Sub CollectDataFromMultiFile()
    Dim wb As Workbook, s As Worksheet, db As Worksheet
    Dim strPath As Variant, i As Integer, f As Byte
    strPath = Application.GetOpenFilename( _
        FileFilter:="Excel File (*.xls*),*.xls*", _
        MultiSelect:=True)
    If TypeName(strPath) = "Boolean" Then Exit Sub
    Set db = ThisWorkbook.Sheets("Total")
    db.UsedRange.ClearContents
    Application.ScreenUpdating = False
    For i = 1 To UBound(strPath)
        Set wb = Workbooks.Open(strPath(i))
        For Each s In wb.Worksheets
            f = IIf(db.Range("a1").Value = "", 0, 1)
            If s.Range("a1").Value <> "" Then
                s.UsedRange.Offset(f, 0).Copy
                With db
                    .Range("a" & .Rows.Count).End(xlUp).Offset(f, 0) _
                        .PasteSpecial xlPasteValues
                End With
            End If
        Next s
        wb.Close
        Application.CutCopyMode = False
    Next i
    Application.ScreenUpdating = True
    MsgBox "Finished", vbInformation
End Sub

[WanPalakorn]

เพิ่มเติมไฟล์ข้อมูลที่ลองออกแบบสำหรับดึงข้อมูลจากไฟล์ (Data1 และ Data 2)
ผมต้องการข้อมูลใน Data1 Sheet-Y1 มาเรียงกับ Data2 Sheet-Y1 ก่อน แล้วค่อยเอา Data1 Sheet-Y2 มาต่อครับ

[snasui]

ผมต้องการข้อมูลใน Data1 Sheet-Y1 มาเรียงกับ Data2 Sheet-Y1 ก่อน แล้วค่อยเอา Data1 Sheet-Y2 มาต่อครับ

ตาม Code นี้เป็นการเปิดทีละไฟล์แล้ว Loop ไปยังแต่ละชีตของไฟล์นั้น ๆ จนครบแล้วไปเปิดไฟล์ถัดไป ไม่ได้เปิดไฟล์มาพร้อมกันทั้งหมดแล้วค่อย Loop เพื่อให้สะดวกต่อการเลือกไฟล์เลือกชีตที่ต้องการได้ ที่สำคัญการเปิดไฟล์ขึ้นมาพร้อมกันทั้งหมดแล้วค่อย Loop ย่อมจะไม่สะดวกในการเลือกข้อมูล นอกจากนั้นยังสิ้นเปลือง Memory หากต้องเปิดพร้อม ๆ กันหลาย ๆ ไฟล์

กรณีต้องการเรียงข้อมูลใหม่จาก Code เดิมควรทำหลังจากที่มีการวางข้อมูลทั้งหมดแล้ว ในไฟล์ที่ใช้รวมข้อมูลให้เพิ่มคอลัมน์สำหรับระบุชื่อชีตเพิ่มมาอีก 1 คอลัมน์ เมื่อมีการวางข้อมูลให้เติมค่าในคอลัมน์นี้ด้วยว่าวางจากชีตไหน เมื่อวางครบทุกข้อมูลแล้วค่อยจัดเรียงข้อมูลตามคอลัมน์นี้อีกทีครับ