您当前的位置：首页 > 零壹零壹 > 技巧网站首页技巧

excel使用技巧（自动生成表单文件，内容条目随机）

发布时间：2015-11-28编辑：一水阅读（）

一个自动随机生成项目内容并保存的excel宏（vb也挺好用的，半个小时临时学习搞定）

(不过感觉非常冗余，仅仅能用就是了～)
Sub 宏1()
'
' 宏1 宏
'
    Dim name$, temp$, bjn$, bsn$, bjp$, bsp$, tmn$, hnn$, wzn$, pkn$, kqsn$, dyn$
    temp = Rnd
    bjn = Int((25 * Rnd) + 3)
    bsn = Int((9 * Rnd) + 2)
    If temp > 0.8 Then
        bjp = 238
        bsp = 580
        ElseIf temp > 0.6 Then
        bjp = 268
        bsp = 880
        ElseIf temp > 0.4 Then
        bjp = 328
        bsp = 1080
        Else
        bjp = 398
        bsp = 1280
    End If
    tmn = Int((12 * Rnd) + 3)
    hnn = tmn - 2
    wzn = Int((5 * Rnd) + 1)
    pkn = 2 * Int((4 * Rnd) + 1)
    kqsn = Int((10 * Rnd) + 5)
    dyn = Int((100 * Rnd) + 5)
    Sheets(1).Activate
    Sheets(1).Range("b6:d18").ClearContents
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "标准间"
    Range("C6").Select
    ActiveCell.FormulaR1C1 = bjn
    Range("D6").Select
    ActiveCell.FormulaR1C1 = bjp
    If Rnd > 0.3 Then
        Range("B7").Select
        ActiveCell.FormulaR1C1 = "别墅"
        Range("C7").Select
        ActiveCell.FormulaR1C1 = bsn
        Range("D7").Select
        ActiveCell.FormulaR1C1 = bsp
        If Rnd > 0.3 Then
            Range("B8").Select
            ActiveCell.FormulaR1C1 = "桶面"
            Range("C8").Select
            ActiveCell.FormulaR1C1 = tmn
            Range("D8").Select
            ActiveCell.FormulaR1C1 = "5"
            If Rnd > 0.2 Then
                Range("B9").Select
                ActiveCell.FormulaR1C1 = "矿泉水"
                Range("C9").Select
                ActiveCell.FormulaR1C1 = kqsn
                Range("D9").Select
                ActiveCell.FormulaR1C1 = "3"
                If Rnd > 0.2 Then
                    Range("B10").Select
                    ActiveCell.FormulaR1C1 = "旺仔"
                    Range("C10").Select
                    ActiveCell.FormulaR1C1 = wzn
                    Range("D10").Select
                    ActiveCell.FormulaR1C1 = "10"
                    If Rnd > 0.2 Then
                        Range("B11").Select
                        ActiveCell.FormulaR1C1 = "扑克"
                        Range("C11").Select
                        ActiveCell.FormulaR1C1 = pkn
                        Range("D11").Select
                        ActiveCell.FormulaR1C1 = "5"
                        If Rnd > 0.5 Then
                            Range("B12").Select
                            ActiveCell.FormulaR1C1 = "红牛"
                            Range("C12").Select
                            ActiveCell.FormulaR1C1 = hnn
                            Range("D12").Select
                            ActiveCell.FormulaR1C1 = "10"
                        End If
                    End If
                End If
            End If
        End If
    End If
    For i = 7 To 13
    If Cells(i, "b") = "" Then
            If Rnd > 0.4 Then
                Cells(i, "b").Select
                ActiveCell.FormulaR1C1 = "烧烤"
                Cells(i, "c").Select
                ActiveCell.FormulaR1C1 = "1"
                Cells(i, "d").Select
                ActiveCell.FormulaR1C1 = "200"
            End If
        Exit For
        End If
        Next i
    For k = 7 To 13
    If Cells(k, "b") = "" Then
            If Rnd > 0.6 Then
                Cells(k, "b").Select
                ActiveCell.FormulaR1C1 = "垂钓"
                Cells(k, "c").Select
                ActiveCell.FormulaR1C1 = dyn
                Cells(k, "d").Select
                ActiveCell.FormulaR1C1 = "55"
            End If
        Exit For
        End If
        Next k
    name = [e19]
    ActiveWorkbook.SaveCopyAs "e:\oo\" & name & ".xls"

End Sub

 

生成后，用dir命令将所有统计数据导出到xls中，方便计算总量。
比如在命令行 (批处理中%要写成%%):
cd/d "要导出的文件所在路径如c:\aa"
for /f "delims=" %i in ('dir/b')do echo %~ni>>A.xls