遇到以下問題, 希望有能加的人解答, 萬分感謝
最近出勤的系統換了, 但公司原有系統需要把新系統的資料轉換成系統對應的格式
舊系統對應格式
TT CCCCCCCCCC↓CCCCCCCCCCCC UUU FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF NNNNNNNNNNNNNNNNNNNN YY/MM/DD hh:mm:ss
如下圖的樣本
01 Front Door In 123 Jason Chow Jason 18/07/16 07:50:43
新系統輸出的格式如下
01 Front Door In 123 Jason Chow Jason 21/01/18 07:14:41
舊系統的對應格式是用空格對齊的
想把新系統的輸出格式用VBA or VB or C#變換成舊系統的對應格式
不知有誰神大大能幫忙,萬分感謝
已邀請的邦友 {{ invite_list.length }}/5
如果這個是你要的結果
VBA 程式如下
Option Explicit
Sub Macro1()
Dim strSource, strTarget, strFormat As String
Dim arrLen As Variant
Dim arrTarget(7), arrSource
arrLen = Array(2, 22, 3, 30, 20, 8, 8)
strSource = "01 Front Door In 123 Jason Chow Jason 21/01/18 07:14:41"
Debug.Print "Source String:" & strSource
strFormat = "TT CCCCCCCCCCCCCCCCCCCCCC UUU FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF NNNNNNNNNNNNNNNNNNNN YY/MM/DD hh:mm:ss"
Debug.Print "Format String:" & strFormat
arrSource = Split(strSource, " ")
arrTarget(0) = arrSource(0) '"01"
arrTarget(1) = arrSource(1) & " " & arrSource(2) & " " & arrSource(3) '"Front Door In"
arrTarget(2) = arrSource(4) '"123"
arrTarget(3) = arrSource(5) & " " & arrSource(6) '"Jason Chow"
arrTarget(4) = arrSource(7) '"Jason"
arrTarget(5) = arrSource(8) '"18/07/16"
arrTarget(6) = arrSource(9) '"07:50:43"
strTarget = Padr(arrTarget(0), arrLen(0)) & " " & _
Padr(arrTarget(1), arrLen(1)) & " " & _
Padr(arrTarget(2), arrLen(2)) & " " & _
Padr(arrTarget(3), arrLen(3)) & " " & _
Padr(arrTarget(4), arrLen(4)) & " " & _
Padr(arrTarget(5), arrLen(5)) & " " & _
Padr(arrTarget(6), arrLen(6))
Debug.Print "Target String:" & strTarget
End Sub
Function Padr(ByVal s As String, ByVal strlen As Integer) As String
Padr = s & Space(strlen - Len(s))
End Function
已修正如上(增加第一列及第三列)
請重新複製程式碼再試試看
03 Front Door In 706 MARK LO LAWRENCE 21/01/18 07:08:04
03 Front Door In 711 JOEY CHAN IVY 21/01/18 07:14:41
03 Front Door In 712 CHIIY CHAN CHIIY 21/01/18 07:14:44
03 Front Door In 702 LO KA CHUN PETER 21/01/18 07:20:41
03 Front Door In 708 CHOLE WONG CHOLE 21/01/18 07:23:24
03 Front Door In 704 PAUL CHAN PAUL 21/01/18 07:27:25
03 Front Door In 704 SEOL CHAN SEOL 21/01/18 08:15:42
03 Front Door In 712 SEAW SEAW 21/01/18 10:18:43
03 Front Door In 708 NICO WONG NICO 21/01/18 11:58:27
03 Front Door In 704 ANSON CHAN ANSON 21/01/18 12:51:06
03 Front Door In 711 IVY CHAN IVY 21/01/18 13:00:28
你好, 我把一小撮的新系統格式上傳這裏, 發現按下執行後並沒有動作發生
公司一共幾百人.......
TT CCCCCCCCCC↓CCCCCCCCCCCC UUU FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF NNNNNNNNNNNNNNNNNNNN YY/MM/DD hh:mm:ss
多了出來的用空格填滿, 達到以上舊格式排列
謝謝大大用心編寫
iThome鐵人賽
看影片追技術