Excel lมารถส่งออกข้อมูล เป็น Text ได้หรือไม่ครับ

[tigerwit]

ผมออกแบบตารางให้โรงเรียนในสังกัด กรอกข้อมูล
หลังจากนั้นให้ส่งไฟล์มาเพื่อนำรวมกันทุกโรงเรียน

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

กรณีนี้เห็น Access ทำได้
ไม่ทราบว่า excel ทำได้หรือเปล่าครับ

[snasui]

โดยปกติ Excel สามารถ Save As เป็นนามสกุล .txt ได้อยู่แล้วครับ

ถ้าไม่ใช่ที่ต้องการช่วยอธิบายเพิ่มด้วยครับว่าต้องการให้ไฟล์ไหน Sheet ไหนออกเป็น Text File และการส่งออกเป็น Text File ที่ว่านี้ต้องการทำเป็น Macro ใช่หรือไม่ครับ

[tigerwit]

ครับผม
ทำเป็นปุ่มให้ผู้ใช้คลิกส่งออก
จากไฟล์ Sch ชีท S1
โดยส่งออก เฉพาะ เซลที่แสดง ชื่อ ชื่อ-สกุล ,ตำแหน่ง,คศ.,สังกัด/โรงเรียน,เลขที่ตำแหน่ง,เงินเดือน

ส่วนไฟล์ SPAO ทำปุ่ม คลิกนำเข้าข้อมูล จากไฟล์ที่ส่งออกมาจาก
โดยนำเข้าแบบต่อท้ายแถวไปเรื่อย ๆ

[snasui]

ลองหัดเขียน Macro ดูก่อนครับ แล้วค่อยมาปรับ Code กันทีหลัง

1. เริ่มจากการบันทึก Macro เพื่อ Save ออกไปเป็น Text File
2. จากนั้นไปยังไฟล์ที่สองบันทึกการนำเข้า เริ่มโดย

• เข้าเมนู Data > Import External Data > Import Data
• เลือกไฟล์ที่ต้องการ Import แล้วดำเนินการไปจนจบ
• หยุดการบันทึก Macro

3. ทดสอบว่าใช้งานได้หรือไม่ติดปัญหาตรงไหนมาถามกันต่อครับ

Note:
1. การบันทึก Macro เข้าที่เมนู Tools > Macro > Record New Macro
2. การดู Code ที่ได้จากการบันทึก กด Alt+F11

[tigerwit]

ได้เลยครับ
แล้วจะกลับมาถามอีกครั้ง
เจอปัญหาแน่ๆ

[tigerwit]

ลองแล้วครับ (เฉพาะส่งออก)
เริ่มตั้งแต่เลือกเซลที่ต้องการส่งออก
แล้ว copy ไปวางที่ไฟล์ใหม่
จากนั้น save as ไฟล์ใหม่นั้น เป็น Text
แล้วปิดไฟล์นั้นลงไป
ปัญหา คือ ไม่ต้องการให้ Excel ถามว่าต้องการบันทึกหรือใหม่ (ตอนที่ปิดไฟล์ที่สร้างใหม่ Book.Txt)
อีกอย่างที่ต้องการคือ อยากให้ผู้ใช้สามารถเลือกบันทึกลงใน Drive หรือ Folder เองได้

Sub Macro3()
Range("C6:H45").Select
Selection.Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\export\Book3.txt", FileFormat:= _
xlTextMSDOS, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.SmallScroll Down:=-42
Range("C6").Select
End Sub

ต่อไปจะลองนำเข้าครับ

[snasui]

ลองดู Code ตามด้านล่างครับ

Code: Select all

Sub Macro3()
    Dim FileSaveName As String
    Worksheets("S1").Range("C6:H45").Copy
    Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
   FileSaveName = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
     If FileSaveName <> "" Then
        ActiveWorkbook.SaveAs Filename:=FileSaveName
        MsgBox "Already save file " & FileSaveName
    End If
    Application.DisplayAlerts = False
      ActiveWindow.Close
    Range("C6").Select
   Application.DisplayAlerts = True
End Sub

[tigerwit]

ลองแล้วมีปัญหาครับ
เป็นอย่างที่แนบไฟล์มาครับ
ไม่รู้ว่า ส่งออกส่วนไหนมา
งง ครับพยามแกะโค๊ด
น่าจะมีปัญหาบรรทัดนี้ครับ

FileSaveName = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")

[snasui]

ผมปรับ Code มาให้ใหม่ให้กระชับและแก้ไขเรื่องการ Save เป็น Text File ให้แล้วตาม Code ด้านล่างครับ

Code: Select all

Sub Macro3()
    Dim FileSaveName As String
    Worksheets("S1").Range("C6:H45").Copy
    Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    FileSaveName = Application.GetSaveAsFilename( _
        filefilter:="Text Files (*.txt),*.txt")
    If FileSaveName <> "" Then
        ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlText
        MsgBox "Already save file " & FileSaveName
    End If
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Range("C6").Select
    Application.DisplayAlerts = True
End Sub

[tigerwit]

ในส่วนการส่งออก Ok ครับ
ขอบพระคุณอย่างสูง

ในส่วนการนำเข้าข้อมูล
ผมลองบันทึก Macro แล้ว
ไม่ได้ดังใจ
ที่ต้องการคือ..
1. คลิก..ปุ่มแล้วให้ผู้ใช้เลือกโฟลเดอร์ที่เก็บไฟล์นำเข้าได้
2. สามารถนำเข้าไฟล์อื่น (โรงเรียนที่ 2 3 4 5 6) โดยไปต่อแถวสุดท้าย

รบกวนอีครั้งครับผม

[snasui]

ไฟล์ SPAO ที่ใ้ช้ Import ข้อมูลลองใช้ Code ตามด้านล่างครับ

Code: Select all

Sub Import()
    Dim rTarget As Range
    Dim TextFileImport As String
    TextFileImport = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File")
    Set rTarget = Worksheets("School").Range("C65536").End(xlUp).Offset(1, 0)
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TextFileImport, _
        Destination:=rTarget)
                .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 874
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

[snasui]

ผมปรับ Code เพื่อให้สามารถเลือก Import ได้ทีละหลาย ๆ ไฟล์พร้อมกันในคราวเดียว ตามด้านล่างครับ

Code: Select all

Sub Import()
    Dim rTarget As Range
    Dim i As Integer
    Dim TextFileImport As Variant
    On Error GoTo MsgError
    TextFileImport = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File", , True)
    For i = 1 To UBound(TextFileImport)
    Set rTarget = Worksheets("School").Range("C65536").End(xlUp).Offset(1, 0)
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TextFileImport(i), _
        Destination:=rTarget)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 874
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Next i
    Exit Sub
MsgError:
    MsgBox "Please select a file"
    Exit Sub
End Sub

[tigerwit]

ขอบคุณครับ
มีปัญหาให้ช่วยแก้อีกครับ
พวกค่า #N/A #Value ไม่อยากให้มันแสดงผล
รบกวนตามไฟล์ที่แนบมานะครับ

[snasui]

ลองตามนี้ครับ

1. ที่ I2 คีย์

=IF(ISNA(LOOKUP(D2,Salary!$A$48:$K$48,J2:T2)),0,LOOKUP(D2,Salary!$A$48:$K$48,J2:T2))

Entter > Copy ลงด้านล่าง

2. ที่ H2 คีย์

=IF(I2=0,0,G2-E2)

Enter > Copy ลงด้านล่าง

3. ที่ G2 คีย์

=IF(I2=0,0,INDEX(Salary!$A$1:$K$47,I2-(F2*2),D2))

Enter > Copy ลงด้านล่าง

[tigerwit]

ขอบพระคุณอย่างสูง
สำหรับคำแนะนำทุกอย่าง
ผมเองไม่ค่อยจะรู้เรื่องสูตร excel เท่าไหร่
อาศัยจากเว็บนี้ ทำให้มีความรู้ขึ้นมาก
แต่ถึงอยางไรก็ตามผมก็เป็นคนประเภท Copy And Copy
สูตรบางสูตรก็ลอกเขาจากหลาย ๆ ที่
แล้วก็ใช้แบบไม่เข้าใจ

ยกตัวอย่างตามไฟล์ที่แนบมานี้
สูตรนี้ (ในชีท Apil)

=IF(M5=0,0,INDEX(Money!$A$1:$F$63,M5-(I5*2),E5))

อยากเรียนถามว่า ผู้ที่เขียนสูตร มีหลักการใด จึงใช้ INDEX(Money!$A$1:$F$63,M5-(I5*2) แล้วทำให้ excel ไปดึงค่าในชีท Money มาแสดงผล
ที่สงสัย คือทำไม ต้อง M5-(I5*2)

[snasui]

ฟังก์ชั่น Index เป็นการนำข้อมูลในพื้นที่ที่ระบุไว้ล่วงหน้ามาแสดงครับ

โดยมีไวยากรณ์คือ

=Index(ช่วงข้อมูลที่ต้องการนำมาแสดง, บรรทัดของข้อมูล, คอลัมน์ของข้อมูล)

เช่น มีข้อมูลดังนี้

.....A.....B.....C
1...5.....8.....3
2...4.....0.....1
3
4
5

หากเราเขียนสูตร

=Index(A1:B5,1,2)

หมายถึงว่าจากช่วงข้อมูล A1:B5 ให้นำบรรทัดที่ 1 และคอลัมน์ที่ 2 ของช่วงข้อมูลมาแสดง คำตอบคือ 8 ซึ่งเป็นค่าใน B1

ในทุกส่วนประกอบของฟังก์ชั่น Index สามารถใช้สูตรแทนได้ครับ ไม่ว่าจะตรงช่วงข้อมูล ตรงบรรทัดที่ และคอลัมน์ที่ก็ตาม

จากสูตร

INDEX(Money!$A$1:$F$63,M5-(I5*2),E5)

หมายความว่า จากช่วงข้อมูล Money!$A$1:$F$63 ให้นำข้อมูลในบรรทัดที่ได้จากผลการคำนวณ M5-(I5*2) และคอลัมน์ที่เป็นค่าใน E5 มาแสดง

ส่วนที่ว่าทำไมต้องเป็นสูตรต่าง ๆ ก็ขึ้นอยู่หลักการ วิธีการในการระบุตำแหน่งข้อมูลของผู้เขียนสูตรครับ

[tigerwit]

มาลงเวลาเรียนครับผม

จากไฟล์ PP2.xls ที่แนบมานี้
ถ้าในชีท Money ผมลองเปลี่ยนตัวเลขในเซล A64:F64 เป็น 0 1 2 3 4 5 (ไฟล์เดิม pp1.xls เป็น 1 2 3 4 5 6)
แล้วสูตร =IF(M5=0,0,INDEX(Money!$A$1:$F$63,M5-(I5*2),E5)) ยังคงเดิม
ปรากฎว่า การแสดงผล ไม่เป็นไปตามที่ต้องการ (อยากให้ ได้อย่าง PP1.xls คือถ้า เงินเดือน 26170 เพิ่มขั้น 0.5 ขั้น ก็จะเป็น 26690)

เราจะเปลี่ยนสูตร =IF(M5=0,0,INDEX(Money!$A$1:$F$63,M5-(I5*2),E5)) อย่างไรครับ

ยังงงคลำเป้าไม่ถูก

[snasui]

สำหรับฟังก์ชั่น Index ไม่น่าจะมีปัญหาอะไร คงจะมีปัญหาจากสูตรที่เกี่ยวข้อง หากจะปรับให้กระชับขึ้น, ตรงกับความต้องการและใช้เวลาน้อยผมคงต้องทราบเงื่อนไขทั้่งหมด เพื่อเขียนสูตรให้ใหม่เลยครับ

[tigerwit]

อธิบายแนวคิดอย่างนี้ครับ
ที่ทำตอนนี้เป็นการทำโปรแกรมเลื่อนขั้นเงินเดือนข้าราชการครู
มี ชีท Money เก็บฐานข้อมูลเงินเดือน ซึ่งมี 5 แท่ง ประกอบด้วย...
แท่งครูผู้ช่วย (คลอลัมน์ A) 0
แท่งคศ.1 (คลอลัมน์ B) 1
แท่งคศ.2 (คลอลัมน์ C) 2
แท่งคศ.3 (คลอลัมน์ D) 3
แท่งคศ.4 (คลอลัมน์ E) 4
แท่งคศ.5 (คลอลัมน์ F) 5

ชีท Apil เป็นข้อมูลข้าราชการครู มีชื่อ คศ. เงินเดือนของมีนาคม ......ฯลฯ

ที่ต้องการคือ ที่ชีท Apil
ในเซล F5 เป็นเงินเดือนของเดือน มีนาคม (ก่อนเลื่อนขั้นเงินเดือน)
ในเซล G5 เป็นเงินเดือน ณ วันที่ 1 เมษายน ที่ได้รับการเลื่อนขั้นแล้ว (อาจจะ 0.0 0.5 ขั้น หรือ 1.0 ขั้น แล้วแต่การพิจารณาของกรรมการ)
ซึ่งในเซล G5 นี้ เกิดจากการคำนวณ โดยให้อ่านค่าจากเซล I5 ว่า มีค่าเป็น 0.0 0.5 หรือ 1.0
ถ้า I5 เป็น 0.0 ก็ให้แสดงค่า F5
ถ้า I5 มีค่า 0.5 ให้ไปดูว่า E5 และ F5 มีค่าเท่าไหร่ (ตัวอย่างตามไฟลที่แนบ E5 คือ 2 และ F5 คือ 26170)
แล้วไปดูข้อมูลจาก ชีท Money ว่า ค่า 26170 ที่อยู่ในแท่งเลข 2 มีหรือไม่
ถ้ามี ให้ดึงค่าที่อยู่ เหนือ 26170 ขึ้นไป 1 แถวมาแสดง (คือค่า 26690)
ถ้า I5 เป็น 1.0 ดึงค่าเหนือขึ้นไป 2 แถวมาแสดง (27200)

อธิบายไป ก็งงไป

[snasui]

ลองตามนี้ครับ

ที่ G5 คีย์

=INDEX(Money!$A$1:$F$63,MATCH($F5,INDEX(Money!$A$1:$F$63,0,$E5+1),-1)-$K5*2,E5+1)

Enter > Copy ลงด้านล่าง

หากไม่ถูกต้องช่วยระบายสีเซลล์พร้อมระบุค่าที่ถูกต้องมาด้วยครับ