การใช้ VBA แก้ไข text File
Data on text file 537941718|66805689820|2012-12-02 09:49:13|66804539280|SMS (Out) - Domestic|Area 1|Area 1|1|1.06| 537941718|66805689820|2012-12-02 14:46:31|66815779483|SMS (Out) - Domestic|Area 1|Area 1|1|A|1.06| 537941718|66805689820|2012-12-02 14:51:16|66815779483|SMS (Out) - Domestic|Area 1|Area 1|1|1.06| 537941718|66805689820|2012-12-02 15:20:39|66815779483|SMS (Out) - Domestic|Area 1|Area 1|1|B|1.06| 537941718|66805689820|2012-12-03 22:23:43|66815779483|SMS (Out) - Domestic|Area 1|Area 1|1|1.06| 537941718|66805689820|2012-12-04 14:29:52|66853538561|SMS (Out) - Domestic|Area 1|Area 1|1|1.06| 537941718|66805689820|2012-12-04 15:21:31|66890084262|SMS (Out) - Domestic|Area 1|Area 1|1|1.06| 537941718|66805689820|2012-12-04 19:11:18|66804539280|SMS (Out) - Domestic|Area 1|Area 1|1|1.06| 537941718|66805689820|2012-12-06 06:45:28|66879224601|SMS (Out) - Domestic|Area 1|Area 1|1|1.06|
Requirement 1. ต้องการตัด 2 คอลัมน์แรกออก 2. บรรทัดไหนมี 9 ช่องไม่ต้องทำอะไร แต่ถ้ามี 8 ช่องให้เพิ่มแทรกก่อนคอลัมน์สุดท้ายให้ครบ 9 ช่อง 3.ปรับวันที่จาก YYYY/MM/DD ให้เป็น DD/MM/YYYY 4.ทำการสร้าง ฟอร์ม เพื่อให้เลือก file ต้นฉบับ และกำหนด ชื่อ file output ได้เอง
Output หรือ ผลลัพท์ที่ได้ 02/12/2012|09:49:13|66804539280|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 02/12/2012|14:46:31|66815779483|SMS (Out) - Domestic|Area 1|Area 1|1|A|1.06| 02/12/2012|14:51:16|66815779483|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 02/12/2012|15:20:39|66815779483|SMS (Out) - Domestic|Area 1|Area 1|1|B|1.06| 03/12/2012|22:23:43|66815779483|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 04/12/2012|14:29:52|66853538561|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 04/12/2012|15:21:31|66890084262|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 04/12/2012|19:11:18|66804539280|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 06/12/2012|06:45:28|66879224601|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 06/12/2012|06:45:31|66879224601|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 06/12/2012|07:42:57|66879224601|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 06/12/2012|08:53:50|66890587945|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 06/12/2012|15:22:35|66895275375|SMS (Out) - Domestic|Area 1|Area 1|1||1.06| 06/12/2012|17:20:19|66804539280|SMS (Out) - Domestic|Area 1|Area 1|1||1.06|
หน้าจอ
code ของ ปุ่ม Browse Private Sub CommandButton1_Click() Call Module1.browse_Text End Sub
code ของ ปุ่ม run Private Sub CommandButton2_Click() Call Module1.procedureControl2 ' RUN ON TEXT FILE 'is work End Sub
code ของ ปุ่ม close Private Sub CommandButton3_Click() Me.Hide End Sub
code on module1 Option Explicit Global strSelectedFile As String Sub Adijustment() UserForm1.Show End Sub Sub browse_Text() 'MsgBox "thailand" Dim fdg As FileDialog, vrtSelectedItem As Variant strSelectedFile = "" Set fdg = Application.FileDialog(msoFileDialogFilePicker) With fdg .AllowMultiSelect = False .InitialView = msoFileDialogViewDetails If .Show = -1 Then For Each vrtSelectedItem In .SelectedItems 'onby be 1 'strSelectedFile = vrtSelectedItem & "#" & vrtSelectedItem strSelectedFile = vrtSelectedItem 'get path and filename of the datafile to global variable Next vrtSelectedItem [UserForm1].[TextBox1].Value = Module1.getNameOnly(strSelectedFile) [UserForm1].[TextBox2].Value = "_" & Module1.getNameOnly(strSelectedFile) Else 'The user pressed Cancel. End If End With Set fdg = Nothing 'Set fd = Nothing End Sub Function getNameOnly(Nme As String) Dim i, n, m As Integer For i = 1 To Len(Nme) If Mid(Nme, i, 1) = "" Then m = i End If Next i getNameOnly = Right(Nme, (Len(Nme) - m)) End Function Function getPathOnly(Nme As String) Dim i, n, m As Integer For i = 1 To Len(Nme) If Mid(Nme, Len(Nme) - i, 1) = "" Then m = i Exit For End If Next i getPathOnly = Left(Nme, (Len(Nme) - m)) End Function Sub procedureControl2() Call Module1.ACS_Adjusment End Sub Sub ACS_Adjusment() ' RUN ON TEXT FILE -----------It is worked 'µéÍ¡ÒÃãËéµÑ´ 2 column ááÍÍ¡ 'éÒºÃ÷Ѵä˹ÁÕ 9 ªèÍáÅéÇ äÁèµéÍ·ÓÍÐäà 'éÒºÃ÷Ѵä˹ÁÕ 8 ãËéà¾ÔèÁ column ¡è͹ column ÊØ´·éÒ 'data on test4.txt
Dim sBuf As String Dim sTemp As String Dim iFileNum As Integer Dim sFileName As String Dim i, a, b, c As Integer
' Edit as needed 'sFileName = "d:data10test-textfiletest4.txt" sFileName = getPathOnly(strSelectedFile) & UserForm1.TextBox1
'load upto the variable ------>sTemp iFileNum = FreeFile Open sFileName For Input As iFileNum Do Until EOF(iFileNum) Line Input #iFileNum, sBuf '------- Check "|" (count column) 8 or 9 b = 0 For i = 1 To Len(sBuf) If Mid(sBuf, i, 1) = "|" Then b = b + 1 End If Next i '------- if 9 column (|=8)----->insert column ("|" ) at before last column If b = 9 Then a = 0 For i = 1 To Len(sBuf) If Mid(sBuf, i, 1) = "|" Then a = a + 1 If a = 8 Then sBuf = Left(sBuf, i) & "|" & Right(sBuf, Len(sBuf) - i) Exit For End If End If Next i End If '------- cut 2 column from left hand c = 0 For i = 1 To Len(sBuf) If Mid(sBuf, i, 1) = "|" Then c = c + 1 If c = 2 Then sBuf = Mid(sBuf, i + 9, 2) & "/" & Mid(sBuf, i + 6, 2) & "/" & Mid(sBuf, i + 1, 4) & "|" & Right(sBuf, Len(sBuf) - (i + 11)) Exit For End If End If Next i '------- collect each row(Line) into Stemp sTemp = sTemp & sBuf & vbCrLf Loop
'------- close read Close iFileNum
'---------Save txt file as (if possible) iFileNum = FreeFile sFileName = getPathOnly(strSelectedFile) & UserForm1.TextBox2 Open sFileName For Output As iFileNum Print #iFileNum, sTemp
'------- close write Close iFileNum MsgBox "completed" End Sub
| Create Date : 26 มกราคม 2556 |
| Last Update : 26 มกราคม 2556 9:03:07 น. |
|
0 comments
|
| Counter : 2020 Pageviews. |
 |
|
ฝากข้อความหลังไมค์
Rss Feed
ผู้ติดตามบล็อก : 4 คน [
