沒想到 2007-01-01 就要 coding 呵呵
因為朋友要出個媒體給主管機關, 沒想到他們到現在還規定
中文英數字要全型空白也要全型, 想到最簡單的方式是用 access

input : excel file
output : fixed text file
用 access 的 import 轉入 excel file
執行 vba 後再用 export 成 text file


Option Compare Database



Public Sub test()

Dim con As ADODB.Connection

Dim rs As ADODB.Recordset

Dim sql As String

Dim str As String



Set con = CurrentProject.Connection --> 老是忘記這個語法

i = 0

Set rs = New ADODB.Recordset

rs.CursorLocation = adUseClient

sql = "select * from address"

rs.Open sql, con



Do

    If IsNull(rs(2)) Then

        str = ""

    Else

        str = rs(2)

    End If

    str = "insert into t1 values('" + fill(str) + "','" + Format(rs(1), "0000000") + "')"

    Debug.Print str

    DoCmd.RunSQL str

    rs.MoveNext --> 沒想到 office 到 2003 了, 忘了這句還是會死當 Orz

    DoEvents 

Loop While Not (rs.EOF)

End Sub



Public Function fill(str) As String

Dim s As String

Dim i As Integer

Dim l As Integer



s = Replace(str, " ", "")

l = Len(s)



For i = l To 60

    s = s + "　" --> 這是全型空白

Next



fill = s

End Function

identical 發表在 痞客邦 留言(0) 人氣()