การ Import data และการ ซ่อน row column

[Panuwat]

เรียนท่านอาจารย์ ตามที่ท่านอาจารย์ได้อธิบายเรื่องการ IMPORT DATA ให้กับสมาชิกท่านหนึ่ง ตาม http://www.snasui.com/viewtopic.php?f=3&t=309
นั้น ซึ่งผมอยากนำมา Apply มาเป็นลักษณะงานที่กำลังทำอยู่ จึงอยากขอความอนุเคราะห์จากท่านอาจารย์ แก้ไขปัญหาเป็น ส่วนๆ และ ข้อๆ ดังนี้
การ IMPORT DATA (ไฟล์แนบ ประเมินผล 2.xls , 200451-KPI-Maker.xls)
1. Import data จากไฟล์อีกไฟล์หนึ่ง ในรูปแบบไฟล์ที่เป็น Excel (ชื่อไฟล์.xls)
2. ให้มีการตรวจสอบว่าไฟล์นั้นเป็นไฟล์ที่ต้องการหรือไม่ (ตรวจสอบจากชื่อ ในที่นี้คือให้มีตัวเลข “20” นำหน้า ตามไฟล์แนบคือ 200451-KPI-Maker.xls เนื่องจากไฟล์จะเปลี่ยนแค่ ตัวเลข 4 ตัวหลัง ตามรหัสสาขา
3. ถ้าใช่ ให้นำข้อมูลตามไฟล์ 200451-KPI-Maker.xls มาลงในไฟล์หลัก (Main.xls) ซึ่งปัญหาคือ ไฟล์ 200451-KPI-Maker.xls จะมีการเรียงข้อมูลในลักษณะแนวนอน (ROW) แต่ไฟล์ Main.xls จะมีทำการรับข้อมูลในแนวตั้ง
4. ถ้าไม่ใช่ ให้ขึ้น Message Error “ไฟล์นี้ไม่สามารถนำเข้าฐานข้อมูลได้”
5. tab sheet ชื่อ รับเป้าหมาย

การซ่อน Row และ Column ในส่วนที่ไม่ใช้ (ไฟล์แนบ ประเมินผล 2.xls)
เนื่องจาก อาจารย์เคยแนะนำผมมาแล้วตาม http://www.snasui.com/viewtopic.php?f=3&t=743 แต่ปัญหาคือ โปรแกรมจะตรวจสอบเฉพาะ cell ที่เป็น ค่าว่าง แต่ถ้า Cell นั้นมองว่าเป็นว่าง แต่เป็นลักษณะสูตร จะทำให้โปรแกรมทำงานผิดพลาด จึงขอรบกวนให้ท่านอาจารย์ ช่วยแก้ไขให้ด้วยครับ โดยให้จัดรูปแบบให้พอดี ทั้ง ROW และ COLUMN ( tab sheet ชื่อ รายงานผลงาน ครับ)

ขอแสดงความนับถือ

[snasui]

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

[Panuwat]

เรียนท่านอาจารย์ ผมได้ทำการ ลองเขียน Code โดยดูจากหัวข้อเก่าๆ พบว่ายังเกิด ERROR และไม่เป็นไปตามเงื่อนไขครับ

1.การ IMport ข้อมูล ใน Sheet "รับเป้าหมาย" โดยรับข้อมูลจากไฟล์นอกที่ชื่อ 200451-KPI-Maker.xls
เกิด ERROR
2.การซ่อน Row และ column ใน Sheet "รายงานผลงาน" ไม่เป็นไปตามเงื่อนไข


ขอแสดงความนับถือ
ภาณุวัฒน์

[snasui]

สำหรับการนำข้อมูลจากชีท รับข้อมูล มาวางในชีท รับเป้าหมาย จะต้องมี Field ให้อ้างอิงเพื่อดึงข้อมูลมาวางครับ ชีท รับเป้าหมาย เซลล์ A4:A15 ไม่มีในชีท รับข้อมูล จึงไม่สามารถใช้สูตรดึงข้อมูลมาวางได้

สำหรับการ Import ข้อมูลและตรวจสอบชื่อไฟล์ผมเขียนให้แล้ว ดูตามไฟล์แนบครับ

[Panuwat]

ขอบพระคุณอาจารย์มากครับ ไม่เคยผิดหวังเลย ผมจะนำเอา code ของท่านอาจารย์ไปศึกษา และประยุกต์ใช้ต่อไปครับ

ขอแสดงความนับถือ
ภาณุวัฒน์

[Panuwat]

เรียนท่านอาจารย์ครับ ขอความกรุณาให้อาจารย์ช่วยเหลือ 2 ข้อ ครับ
1. ผมได้นำโปรแกรมที่อาจารย์ได้แนะนำ มาลอง Run ดูแล้วครับ ปรากฏว่า ข้อมูลที่นำเข้าจาก ไฟล์ 200451-KPI-Maker.xls ได้หายไป (ระบุไว้ในไฟล์แนบแล้ว) จึงขอความกรุณาอาจารย์ช่วยตรวจสอบและแก้ไขให้ด้วยครับ
2.สมมุติว่า การ IMPORT ข้อมูลจาก ไฟล์ 200451-KPI-Maker.xls เข้ามาใน ไฟล์หลัก "ประเมิน3.xls" ชีต "รับข้อมูล" แล้ว
จะทำอย่างไรให้ข้อมูลเหล่านั้นถูกส่งไปยังชีต "รับเป้าหมาย" อย่างถูกต้อง ตรงกับ รหัสพนักงาน ชื่อพนักงาน และผลิตภัณฑ์ ครับ

ขอแสดงความนับถือ

[snasui]

ผมปรับ Code ให้ใหม่แล้ว ลองดูตามไฟล์แนบครับ

[Panuwat]

ขอบพระคุณท่านอาจารย์มากครับ อุตส่าห์ทำให้จนเที่ยงคืนกว่าเลย วันนี้ก็ต้องทำงานอีก รักษาสุขภาพด้วยนะครับ
ภาณุวัฒน์

[Panuwat]

เรียนท่านอาจารย์ ผมได้ Download โปรแกรม+code ที่ท่านอาจารย์ทำให้ลองทำดู ปรากฏว่าพบ ERROR ครับ คือเมื่อลอง Import data ในชีตรับเป้าหมายอีกครั้ง พบว่า Cell ทุก Cell ที่มีข้อมูลอยู่จะกลายเป็น 0 หมด และเมื่อตรวจสอบจะพบว่า มีค่า #REF เกิดขึ้นที่สูตรทุก Cell ครับ
ค่าปกติตัวอย่างที่ Cell C4 ของชีต รับเป้าหมาย คือ =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(รับข้อมูล!$B$2:$EF$5000,MATCH(C$3,รับข้อมูล!$C$2:$C$5000,0),MATCH($A4,รับข้อมูล!$B$2:$EF$2,0))))
หลังกดปุ่ม IMPORT DATA อีกครั้ง จะพบว่า ทุก Cell ในชีต รับเป้าหมายมีค่าเป็น 0 จากการตรวจสอบพบว่า สูตรมีค่า #REF เกิดขึ้น คือ =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(รับข้อมูล!$B$2:$EF$4804,MATCH(C$3,รับข้อมูล!$C$2:$C$4804,0),MATCH($A4,รับข้อมูล!#REF!,0))))
2.เมื่อลองกดหาไฟล์อื่นเพื่อรับข้อมูลเข้ามาโปรแกรมจะมีการเตือนจริง แต่หลังจากที่เตือนแล้ว ค่าทุกค่าจะเปลี่ยนเป็น 0 ครับ

ขอความกรุณาแก้ไขให้ผมด้วยนะครับท่านอาจารย์

ขอแสดงความนับถือ

[snasui]

ผมปรับ Code ให้ใหม่เป็นตามด้านล่าง ลองทดสอบดูตามไฟล์แนบครับ

Code: Select all

Sub ImportExcelFile()
Dim sFilename As String
Dim rTarget As Range
Dim i As Integer, k As Integer
Application.ScreenUpdating = False
Sheets("รับข้อมูล").UsedRange.ClearContents
Set rTarget = Worksheets("รับข้อมูล").Range("A1")
sFilename = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , _
           "Select Excel Data File")
For i = 1 To Len(sFilename)
    If Mid(sFilename, i, 1) = "\" Then
        k = i + 1
    End If
Next i
If Mid(sFilename, k, 2) <> "20" Then
    MsgBox "This file can't import. Please check file name."
    Exit Sub
End If
Workbooks.Open Filename:=sFilename
Worksheets("Sheet1").UsedRange.Copy
rTarget.PasteSpecial xlPasteValues
Application.DisplayAlerts = False
ActiveWorkbook.Close
DeletRange
With Worksheets("รับเป้าหมาย")
    .Range("C4").Formula _
    = "=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0," & _
        "INDEX(รับข้อมูล!$B$2:$EF$500,MATCH(C$3,รับข้อมูล!$C$2:$C$500,0)," & _
        "MATCH($A4,รับข้อมูล!$B$2:$EF$2,0))))"
    .Range("C4").Copy .Range("C4:AF25")
End With
MsgBox "Finish"
Worksheets("รับเป้าหมาย").Activate
Application.ScreenUpdating = True
 End Sub

Sub DeletRange()
Dim i As Integer
With Worksheets("รับข้อมูล")
    For i = .Range("C65536").End(xlUp).Row To 1 Step -1
        If .Cells(i, "C") = "" Or .Cells(i, "C") = 0 Then
            .Cells(i, "C").EntireRow.Delete
        End If
    Next i
End With
End Sub

[Panuwat]

ขอบพระคุณอาจารย์มากครับ

ขอแสดงความนับถือ
ภาณุวัฒน์

[Panuwat]

เรียนท่านอาจารย์
เป็นคำถามที่ต่อเนื่องจากที่ผ่านมา เนื่องจากในระหว่างที่กำลัง IMPORT DATA อยู่ โปรแกรมจะใช้เวลาในการ ตรวจสอบ เพื่อ IMPORT ข้อมูล จาก ชีตรับข้อมล มา ชีตรับเป้าหมาย มาก ซึ่งในระหว่างที่รอนี้ ผมกลัวว่าผู้ใข้จะนึกว่าโปรแกรมแฮงค์ ซึ่งในระหว่างที่รอซึ่งต้องใช้เวลาพอสมควร จึงอยากให้มีการแสดง Progress bar ว่าโปรแกรมได้ทำงานไปถึงไหน กี่เปอร์เซ็นต์แล้วครับ

ขอแสดงความนับถือ

[snasui]

ลองดูตัวอย่างการใช้ Userform สร้าง Progress bar ตามไฟล์แนบครับ

[Panuwat]

ขอบคุณมากครับท่านอาจารย์ ทดลองคลิ๊กที่ปุ่ม IMPORT DATA แล้ว ปรากฏว่า ERROR ครับ น่าจะเกิดจาก ในเครื่องของผมไม่มี Tool Progress bar ไม่ทราบว่าเราจะ Download จากที่ไหนครับ (MS OFFICE 2003)
ขอแสดงความนับถือ

[snasui]

ลองเปลี่ยน Code ShowUserform เป็นตามด้านล่างครับ

Code: Select all

Sub ShowUserForm()
    With UserForm1
        .LabelProgress.Width = 0
        .Left = Range("L10").Left
        .Top = Range("L10").Top
        .Show
    End With
End Sub

[Panuwat]

ทำได้แล้วครับ ขอบพระคุณอาจารย์มากครับ

[Panuwat]

อาจารย์ครับ เจอปัญหาอีกแล้วครับ เนื่องจากเดิม การ Import data เพื่อนำข้อมูลจากไฟล์ 200451-KPI-Maker.xls มาใส่ในไฟล์ประเมินผล3.xls ชีต รับข้อมูล แล้วแปลงค่าจากชีตรับข้อมูลมาเป็นข้อมูลในชีตรับเป้าหมาย คือให้เช็คจาก รหัสพนักงาน ตรงกับชื่อ ตรงกับชื่อผลิตภัณฑ์ ทำให้เกิดปัญหาคือ เวลาที่ข้อมูล ชื่อพนักงาน ในไฟล์ 200451-KPI-Maker.xls อาจจะมีการเว้นวรรคระหว่างชื่อ - นามสกุลไม่เหมือนกับไฟล์ ไฟล์ประเมินผล 3.xls (ไฟล์รับข้อมูล) หรือการคีย์ชื่อนามสกุลผิด โปรแกรมก็จะไม่ดีงข้อมูลมาครับ ซึ่งการผิดพลาดเหล่านี้เราไม่สามารถควบคุมไฟล์ที่ส่งมาคือไฟล์ 200451-KPI-Maker.xls ได้ครับ ผมจึงขอความกรุณาให้อาจารย์ เขียน code ให้ใหม่ เอาแค่ว่า รหัสพนักงาน ตรงกับ ชื่อผลิตภัณฑ์ ไม่ต้องตรวจสอบชื่อพนักงานครับ

ขอแสดงความนับถือ

[snasui]

ที่ Procedure ชื่อ ImportExcelFile ปรับเป็นตามด้านล่าง (*** ส่วนที่ใช้ '... คือ Code เดิม)

Code: Select all

Sub ImportExcelFile()
'...
With Worksheets("รับเป้าหมาย")
    .Range("C4").Formula _
    = "=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0," & _
        "INDEX(รับข้อมูล!$B$2:$EF$500,MATCH(C$2,รับข้อมูล!$B$2:$B$500,0)," & _
        "MATCH($A4,รับข้อมูล!$B$2:$EF$2,0))))"
    Call PasteData
End With
'...
 End Sub

สังเกตว่าเปลี่ยนที่สูตร Match เท่านั้นครับ

[Panuwat]

ทำได้แล้วครับ ขอบพระคุณอาจารย์มากครับ
ภาณุวัฒน์