|
Sub Writout()
Dim r, i, j As Integer
Dim s, sh As String, t As Single
t = Timer
Dim Msg$, bt$, Default$, MyValue, aa
Msg = "输入一个2000到2039之间的数值:"
bt = "年份输入框"
Default = "2010" ' 设置缺省值。
100:
MyValue = InputBox(Msg, bt, Default)
If MyValue < 2000 Or MyValue > 2039 Then
aa = MsgBox("输入年份超出范围,请重新输入或者退出。", 1)
If aa <> vbOK Then Exit Sub
GoTo 100
Else
MsgBox "你要处理的是 " & MyValue & "年的数据!"
End If
r = [B65536].End(xlUp).Row
For i = 2 To r
sh = ActiveSheet.Name
s = s & "1~~" & Cells(i, 2).Value & "~~010000~~1~~" & Format(DateSerial(MyValue, sh, 1), "yyyymmdd") & "~~" & Format(DateSerial(MyValue, sh, Day(DateSerial(MyValue, sh + 1, 1) - 1)), "yyyymmdd") & "~~" & Day(DateSerial(MyValue, sh + 1, 1) - 1) & "~~" & Cells(i, 3).Value & "~~" & "2000~~~~" & vbCrLf
Next
Open ThisWorkbook.Path & "\" & sh & ".txt" For Output As #1
Print #1, s
Reset
MsgBox Timer - t
End Sub
说明:
1,表格里每个表名必须是1,2,3,4,5,6,7,8,9,10,11,12间的任意一数字,代表月份。
2,程序运行时候会首先弹出inputbox窗口,提示用户输入年份,默认是2010。
3,确认后程序开始提取表格里的值到txt文本文件里,列之间以~~隔开。
[ 本帖最后由 lgcmeli 于 2010-3-3 17:16 编辑 ] |
|