Snasui Excel Forum

ขอบคุณมากเลยนะคะ จะลองนำมาปรับใช้ในงานดูค่ะ

Sub CollectAllFile()
Dim nb As Workbook, ob As Workbook
Dim r As Range
Dim nb1 As Range, nb2 As Range
Dim nb3 As Range, nb4 As Range
Dim i As Integer, lng As Long
Dim FileSelected As Variant
    
Application.ScreenUpdating = False
On Error GoTo MsgError
lng = Rows.Count
Set ob = Workbooks("summary.xlsm")
FileSelected = Application.GetOpenFilename("Excel Files (*.xlsx),*.xlsx", , _
       "Select your Excel's File", , True)
For i = 1 To UBound(FileSelected)
    Workbooks.Open Filename:=(FileSelected(i)), UpdateLinks:=0
    Set nb = ActiveWorkbook
    With nb.Worksheets("Data")
        Set nb1 = .Range("G3")
        Set nb2 = .Range("C12:C23")
        Set nb3 = .Range("E10:N10")
        Set nb4 = .Range("E12:N23")
    End With
    Set r = ob.Worksheets("Summary").Range("A" & lng).End(xlUp).Offset(2, 0)
    nb1.Copy: r.PasteSpecial xlPasteValues
    nb2.Copy: r.Offset(0, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    nb3.Copy: r.Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    nb4.Copy: r.Offset(1, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    Application.CutCopyMode = False
    nb.Close False
Next i
Application.ScreenUpdating = True
MsgBox "Finish."
Exit Sub
    
MsgError:
    MsgBox "Please try again."
    Exit Sub

End Sub

[quote="janry"]ตอนนี้ทำงานได้แล้ว แต่ไม่อยากให้ถามว่า save fie หรือป่าว ไม่อยากให้ขึ้น pop up จะเขียน code อย่างไรดีคะ step แรกที่ขึ้น OK ไม่เป็นไร ถามหา file link แต่ step ที่ 2 ไม่อยากให้ขึ้นคะ [/quote]
8-) เยี่ยมเลยครับ แต่เนื่องจากยังต้องอ้างอิงชื่อไฟล์เข้าไปใน Code แบบตรง ๆ ซึ่งอาจจะเป็นปัญหาในการทำงานกับไฟล์จำนวนมาก ผมเขียน Code มาให้ใหม่ สามารถเลือกได้เองว่าจะ Copy ข้อมูลจากไฟล์ไหนมาบ้าง ทั้งยังไม่ให้แสดงการ Update Link และปิดโดยไม่ต้อง Confirm ลองดูตัวอย่างตามด้านล่างครับ
[code]Sub CollectAllFile()
Dim nb As Workbook, ob As Workbook
Dim r As Range
Dim nb1 As Range, nb2 As Range
Dim nb3 As Range, nb4 As Range
Dim i As Integer, lng As Long
Dim FileSelected As Variant

Application.ScreenUpdating = False
On Error GoTo MsgError
lng = Rows.Count
Set ob = Workbooks("summary.xlsm")
FileSelected = Application.GetOpenFilename("Excel Files (*.xlsx),*.xlsx", , _
"Select your Excel's File", , True)
For i = 1 To UBound(FileSelected)
Workbooks.Open Filename:=(FileSelected(i)), UpdateLinks:=0
Set nb = ActiveWorkbook
With nb.Worksheets("Data")
Set nb1 = .Range("G3")
Set nb2 = .Range("C12:C23")
Set nb3 = .Range("E10:N10")
Set nb4 = .Range("E12:N23")
End With
Set r = ob.Worksheets("Summary").Range("A" & lng).End(xlUp).Offset(2, 0)
nb1.Copy: r.PasteSpecial xlPasteValues
nb2.Copy: r.Offset(0, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
nb3.Copy: r.Offset(1, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
nb4.Copy: r.Offset(1, 1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
nb.Close False
Next i
Application.ScreenUpdating = True
MsgBox "Finish."
Exit Sub

MsgError:
MsgBox "Please try again."
Exit Sub

End Sub[/code]

ตอนนี้ทำงานได้แล้ว แต่ไม่อยากให้ถามว่า save fie หรือป่าว ไม่อยากให้ขึ้น pop up จะเขียน code อย่างไรดีคะ step แรกที่ขึ้น OK ไม่เป็นไร ถามหา file link แต่ step ที่ 2 ไม่อยากให้ขึ้นคะ
ตัวอย่างตามไฟล์แนบค่ะ

แนบ file ที่เป็น summary.xlsm มาแล้วค่ะ

:shock: ไฟล์ summary นามสกุล .xlsx ไม่สามารถแนบ Code มาได้ครับ

ไฟล์ที่จะแนบ Code ได้ต้องนามสกุล .xlsm หรือ .xls (กรณีเป็นไฟล์ Version 2003 ลงไป)

อยู่ใน file Summary ค่ะ

:? :roll: ตัวอย่าง Code อยู่ในไฟล์ไหนครับ :?:

ลองใช้ macro recorder ดู ทำทีละ step แต่เวลา run แล้ว แสดงข้อมูลไม่ถูกต้องค่ะ สลับไปสลับมา
1. เปิดไฟล์ Summary >> record macro >>และ เปิดทีละไฟล์ 2200,2300,2400,2500 >> ลากข้อมูล E12:N23 ของแต่ละไฟล์ >> นำไป Paste special เลือก value >> เลือก Transpose ทำแต่ละไฟล์ >> stop macro
2. ลอง run macro

คำถามค่ะ
1. ในกรณีที่ทำจากหลาย ๆ ไฟล์ เวลา run ต้องเปิดแต่ละไฟล์ไว้ด้วยคะ พอจะมีวิธีเขียนแบบอื่น ไหมคะ ที่ไม่ต้องเปิดไฟล์ไว้ เพราะถ้ามีไฟล์เป็นร้อย ทำให้ทำงานยาก
2. เวลาที่จะนำไฟล์แต่ละไฟล์มาไว้ที่ sheet summary ต้องเปิดไฟล์ต่าง ๆ ไว้ ไม่สามารถเปิด ณ ขณะทำแต่ละไฟล์ได้เลย ต้องเปิดทิ้งไว้ก่อน



ขอบคุณค่ะ

[quote="janry"]หรืออีกแบบ
1. มี File หลาย ๆ ไฟล์ เช่น 2100.xlsx,2300.xlsx,2400.xlsx มีประมาณ 50 file จะนำข้อมูลของแต่ละ file มาสรุปรวม อยู่ที่ file Summary
ขอตัวอย่างการเขียน macro หรือ VBA

[/quote]
:lol: หากต้องการใช้ VBA ให้เขียนมาก่อนครับ ติดตรงไหนก็มาถามกัน ผมไม่ได้ทำตัวอย่างไว้ล่วงหน้าในทุก ๆ เรื่อง จะเน้นแก้ที่ติดปัญหาเป็นหลัก เป็นเรื่อง ๆ ไปครับ :mrgreen:

:lol: การนำรายงานไปทำรายงานไม่ง่ายครับ [color=#0000FF]สูตรสามารถทำได้แบบเดือนใดเดือนหนึ่ง[/color] คือเมื่อคีย์ในชีท Data เพียงเดือนใด ข้อมูลก็จะแสดงในชีท Summary เฉพาะเดือนนั้น

[color=#FF4000]ไม่ได้หมายความว่า[/color] เดือนที่แล้วคีย์มกราคมแล้วข้อมูลจะไปแสดงที่เดือนมกราคมในชีท Summary พอมาเดือนนี้คีย์กุมภาพันธ์แล้วจะแสดงของเดือนเก่าคือมกราคมและกุมภาพันธ์ไปด้วยกัน เมื่อคีย์ทับที่เดิม ข้อมูลเก่าก็จะเปลี่ยนเป็นข้อมูลใหม่ทันที

หากใช้ VBA จัดการก็สามารถทำได้ แต่ต้องเขียนมาก่อน ติดตรงไหนก็มาถามกัน

ทางเลือกอื่นคือให้ทำข้อมูลเป็น Database หมายความว่าให้คีย์ที่ชีท Data แล้วข้อมูลไปเก็บที่ชีท Database ก่อน จากนั้นค่อยใช้สูตรดึงไปแสดงผลที่ชีท Summary วิธีนี้ใช้ร่วมกันทั้ง VBA และ สูตร ขอให้ลองค้นด้วยคำว่า Database ในฟอรัมนี้แล้วลองปรับปรุงไฟล์มาดู ติดตรงไหนก็มาถามกันต่อ ค่อย ๆ ปรับไปครับ

[quote="janry"]:roll: :lol: เราจะใช้ function หรือ Macro ไหนดี ที่ได้ข้อมูลตามต้องการ

1. Key แผนกตามช่อง G3 Input data ของแต่ละตำแหน่ง(E10:N10) เช่น ตำแหน่ง M1 มี 1 คน ,J3 = 3 คน , J2 = 8คน ตั้งแต่ Jan-Dec
เดือน(C12:C23) , แผนก(B12:B23 โดย Key มาจาก G3)

2.ให้ไปแสดงที่Sheet ชื่อ Summary เป็นแนวนอนของแต่ละเดือน jan-dec ตามตำแหน่ง

หมายเหตุ >>ชื่อแผนก (G3 มีการเปลี่ยนชื่อแผนกได้ ตัวเลขของแต่ละตำแหน่ง เปลี่ยนไปตามแต่ละแผนก)


เราจะเขียนสูตรอย่างไรดีให้ได้ข้อมูลที่ต้องการ โดยที่เราไม่ต้องมา manual ทำในแต่ละเดือน

ปัจจุบันที่ทำอยู่จะใช้ copy >> paste specital >>traspose[/quote]


หรืออีกแบบ
1. มี File หลาย ๆ ไฟล์ เช่น 2100.xlsx,2300.xlsx,2400.xlsx มีประมาณ 50 file จะนำข้อมูลของแต่ละ file มาสรุปรวม อยู่ที่ file Summary
ขอตัวอย่างการเขียน macro หรือ VBA

:roll: :lol: เราจะใช้ function หรือ Macro ไหนดี ที่ได้ข้อมูลตามต้องการ

1. Key แผนกตามช่อง G3 Input data ของแต่ละตำแหน่ง(E10:N10) เช่น ตำแหน่ง M1 มี 1 คน ,J3 = 3 คน , J2 = 8คน ตั้งแต่ Jan-Dec
เดือน(C12:C23) , แผนก(B12:B23 โดย Key มาจาก G3)

2.ให้ไปแสดงที่Sheet ชื่อ Summary เป็นแนวนอนของแต่ละเดือน jan-dec ตามตำแหน่ง

หมายเหตุ >>ชื่อแผนก (G3 มีการเปลี่ยนชื่อแผนกได้ ตัวเลขของแต่ละตำแหน่ง เปลี่ยนไปตามแต่ละแผนก)


เราจะเขียนสูตรอย่างไรดีให้ได้ข้อมูลที่ต้องการ โดยที่เราไม่ต้องมา manual ทำในแต่ละเดือน

ปัจจุบันที่ทำอยู่จะใช้ copy >> paste specital >>traspose