将自定义操作制作成Excel菜单

Excel是我们日常工作和学习必不缺少的软件之一，由于每天跟它打交道，很多朋友就想自己写一些VBA程序来简单开发excel，如何将自己写的VBA发给别人用，或者将程序嵌入excel的菜单中，无论打开哪个excel都可以用呢？

一、将Excel所有列转存到txt文件

Sub test()
    Dim p$, f$, A, i, j, s$
    p = ThisWorkbook.Path & "\"
    f = Format(Now, "yyyymmdd-hhmmss") & ".txt"
    A = Sheets(1).UsedRange

    Open p & f For Output As #1
    For i = 2 To UBound(A)
        s = ""
        For j = 1 To UBound(A, 2)
            s = s & "," & """" & A(i, j) & """"
        Next j
        s = Mid(s, 2)
        Print #1, s
    Next i
    Close #1

    Shell "explorer " & p & f, vbNormalFocus
End Sub

二、将宏做成Excel菜单运用到所有Excel

参照https://jingyan.baidu.com/article/f79b7cb31b6d029144023ed6.html

Sub 抽出列()
'
' 抽出
'

    Dim p$, f$, A, i, j, s$
    p = ThisWorkbook.Path & "\"
    f = "policyno" & ".txt"
    s = Split(ActiveCell.Address(1, 0), "$")(0) & "65536"
    A = ActiveCell.Column
    Open p & f For Output As #1
    j = Range(s).End(xlUp).Row
    For i = 1 To j
        
        Print #1, i & "|" & ActiveSheet.Range(Split(ActiveCell.Address(1, 0), "$")(0) & i) & "|"
    Next i
    Close #1
 
    Shell "explorer " & p & f, vbNormalFocus

   
End Sub

一键分列

Sub formula()
'
' 分列
'

'
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
        2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12 _
        , 1)), TrailingMinusNumbers:=True
End Sub