ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 661|回复: 11

[求助] 如何让一个excel文件每一行生成一个新的Excel文件,并命名,加密

[复制链接]

TA的精华主题

TA的得分主题

发表于 2019-9-7 07:53 | 显示全部楼层 |阅读模式
一、源文件:       
序号        姓名        税后工资        邮箱地址        密码
1        张三        6000        zhangsan@126.com        1010
2        李四        7000        lisi@126.com        1212
3        王五        8000        wangwu@126.com        1515
……        ……        ……        ……        ……
100        XX        XX        XX        XXXX

二、要求:       
自动让工资条分别形成一个单独的excel文件,Excel文件用姓名命名,且excel文件加密,密码见密码一栏       

示例:       
生成excel文件1:       
文件名:        张三        密码:1010       
Excel文件内内容:       
序号        姓名        税后工资        邮箱地址        密码
1        张三        6000        zhangsan@126.com        1010

生成excel文件2:       
文件名:        李四        密码:1212       
Excel文件内内容:       
序号        姓名        税后工资        邮箱地址        密码
2        李四        7000        lisi@126.com        1212

生成excel文件3:       
文件名:        王五        密码:1515       
Excel文件内内容:       
序号        姓名        税后工资        邮箱地址        密码
3        王五        8000        wangwu@126.com        1515


谢谢!

工资条总表及生成独立文件要求.zip

7.96 KB, 下载次数: 13

TA的精华主题

TA的得分主题

发表于 2019-9-7 10:31 | 显示全部楼层
源文件要保持这样的格式:   
从A列开始,标题在第2行,数据从第3行开始
  

040.png

代码截图如下:

041.png


TA的精华主题

TA的得分主题

发表于 2019-9-7 10:32 | 显示全部楼层
代码将生成的文件保存在同一目录下,
如需更改目录,请在代码中自行更改,已标识出位置。
  1. Public Sub 生成文件()
  2.     Application.ScreenUpdating = False
  3.     Dim arr, wb As Workbook, i As Long, mFullpath$, FolderPath$
  4.     arr = Sheet1.UsedRange.Value
  5.     FolderPath = ThisWorkbook.Path & "\"
  6.     For i = 3 To UBound(arr)   ' 数据行,第3行开始,如果不是,这里修改****
  7.         If arr(i, 2) <> "" Then
  8.             Set wb = Application.Workbooks.Add
  9.             With wb
  10.                 With .Sheets(1)
  11.                     For j = 1 To UBound(arr, 2)
  12.                         .Cells(1, j) = arr(2, j)         ' 取表头
  13.                         .Cells(2, j).Value = arr(i, j)  ' 取内容
  14.                     Next
  15.                 End With    '
  16.                 mFullpath = FolderPath & arr(i, 2) & ".xlsx"
  17.                 .SaveAs Filename:=mFullpath, FileFormat:=xlOpenXMLWorkbook, Password:=arr(i, 5), CreateBackup:=False
  18.                 .Close
  19.             End With
  20.         End If
  21.     Next
  22.     Set wb = Nothing
  23.     Application.ScreenUpdating = True
  24. End Sub
复制代码


评分

参与人数 1鲜花 +2 收起 理由
gjytm + 2 感谢帮助

查看全部评分

TA的精华主题

TA的得分主题

发表于 2019-9-7 10:49 | 显示全部楼层
再给你美化一下,生成的目标文件自动设置格式,   
效果如下:

039.png




TA的精华主题

TA的得分主题

发表于 2019-9-7 10:50 | 显示全部楼层
美化后的代码如下:
  1. Public Sub 生成文件()
  2.     Application.ScreenUpdating = False
  3.     Dim arr, brr, wb As Workbook, i As Long, mFullpath$, FolderPath$, m As Integer
  4.     arr = Sheet1.UsedRange.Value
  5.     FolderPath = ThisWorkbook.Path & ""
  6.     For i = 3 To UBound(arr)   ' 数据行,第3行开始,如果不是,这里修改****
  7.         If arr(i, 2) <> "" Then
  8.             Set wb = Application.Workbooks.Add
  9.             With wb
  10.                 With .Sheets(1)
  11.                     For j = 1 To UBound(arr, 2)
  12.                         .Cells(1, j) = arr(2, j)         ' 取表头
  13.                         .Cells(2, j).Value = arr(i, j)  ' 取内容
  14.                     Next
  15.                     With .Range("A1:E2")
  16.                         With .Font
  17.                             .Name = "宋体": .Size = 12
  18.                         End With
  19.                         With .Borders
  20.                             .ColorIndex = 12: .LineStyle = 1
  21.                         End With
  22.                     End With
  23.                     .Columns("A:A").ColumnWidth = 5.38
  24.                     .Columns("C:C").HorizontalAlignment = xlRight
  25.                     .Columns("D:D").HorizontalAlignment = xlLeft
  26.                     .Range("A:B,E:E,C1").HorizontalAlignment = xlCenter
  27.                     .Rows("1:2").RowHeight = 20
  28.                     brr = Array(7, 10, 12, 20, 10, 3)
  29.                     For m = 0 To UBound(brr)
  30.                         .Columns(m + 1).ColumnWidth = brr(m)  ' 列宽设置
  31.                     Next
  32.                     With .Range("A1:E1")
  33.                         .Font.Bold = True
  34.                         .Font.ColorIndex = 5
  35.                         .Interior.ColorIndex = 19
  36.                     End With
  37.                     ActiveWindow.DisplayGridlines = False
  38.                     .Name = arr(i, 2)   ' 工作表名,改为:姓名
  39.                 End With    '
  40.                 mFullpath = FolderPath & arr(i, 2) & ".xlsx"
  41.                 If Dir(mFullpath) <> "" Then Kill mFullpath  ' 如果目录文件存在,则删除
  42.                 .SaveAs Filename:=mFullpath, FileFormat:=xlOpenXMLWorkbook, Password:=arr(i, 5), CreateBackup:=False
  43.                 .Close
  44.             End With
  45.         End If
  46.     Next
  47.     Set wb = Nothing
  48.     Application.ScreenUpdating = True
  49. End Sub
复制代码


评分

参与人数 2鲜花 +3 收起 理由
gjytm + 2 感谢帮助
摸不到的颜色 + 1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-9-8 12:26 | 显示全部楼层
opel-wong 发表于 2019-9-7 10:32
代码将生成的文件保存在同一目录下,
如需更改目录,请在代码中自行更改,已标识出位置。

帮了大忙,非常感谢。
请问,如何用整行选取的方式,来调整单元格格式呢?

TA的精华主题

TA的得分主题

发表于 2019-9-8 17:24 | 显示全部楼层
gjytm 发表于 2019-9-8 12:26
帮了大忙,非常感谢。
请问,如何用整行选取的方式,来调整单元格格式呢?

还需要如何调整?

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-9-8 17:47 | 显示全部楼层
opel-wong 发表于 2019-9-8 17:24
还需要如何调整?

您好,太感谢了~
为了灵活的修改格式,我用宏,逐渐写成了下面的代码。经过测试,在单一文件里面是可以的。
但是,我不知道,怎么整合到您写的第一个代码中。

一、这个是我为了修改格式写的代码
Sub 独立格式调整专用()
'独立运作成功,但是整合到源代码中失败,未能全自动
    Columns("A:E").Select  '选择列,范围需要根据实际情况调整
    Selection.ColumnWidth = 11.63  '所有选择的列调整宽度,数值自行调整,请提前测试合适的宽度'
    Rows("1:2").Select  '选择前两行
    With Selection  '
        .HorizontalAlignment = xlCenter  '居中
        .VerticalAlignment = xlCenter  '居中
        .WrapText = True    '自动换行
    End With  '
    Range("A1:E2").Select  '选择区域,手动根据实际情况自行调整
    Selection.Borders.LineStyle = 1  '所选区域边框线实线
End Sub

二、这个是您写的第一个代码,对我很管用。我稍作修改并加了我按自己理解的备注
Public Sub 生成文件()
    Application.ScreenUpdating = False 'Excel的工作表里面数据发生变化后False禁止实时刷新
    Dim arr, wb As Workbook, i As Long, mfullpath$, folderpath$  '定义了变量,long是个变量类型。mfullpath$, folderpath$ ,最后的符号$不知道咋回事,不知道我写没写错。$似乎代表了字符串,文本类型
    arr = Sheet1.UsedRange.Value   '用所有的可用数据给arr赋值,数组?
    folderpath = ThisWorkbook.Path & "\"  '这里是生成的文件储存位置的字符串
    For i = 2 To UBound(arr) '数据(不是表头)行从第几行开始就数字就改成几。  'UBound的是数组的上界(搜索得知)
        If arr(i, 2) <> "" Then
          Set wb = Application.Workbooks.Add  '和新建工作簿有关,或者新建打开工作簿?
            With wb   'with语句起到的是简化代码的作用
              With .Sheets(1)  '表示活动工作簿的第一个工作表。所以需要关掉其他文件。
                For j = 1 To UBound(arr, 2)
                    .Cells(1, j) = arr(1, j)   '取表头
                    .Cells(2, j).Value = arr(i, j) '取内容(生成文件的第二行)
                Next
              End With
              mfullpath = folderpath & arr(i, 6) & ".xlsx"  '这里显示的是生成后文件名的构成,姓名在第几列,就把这行的数字改成几
              .SaveAs Filename:=mfullpath, FileFormat:=xlOpenXMLWorkbook, Password:=arr(i, 4), CreateBackup:=False
              '分别代表:保存文件名为XXX(其本质是一个文件夹+文件的地址信息),保存文件格式为.xlsx,密码在第几列就把数字改成几,清空后台数据。
              .Close
            End With
          End If
    Next
    Set wb = Nothing   '释放内存
    Application.ScreenUpdating = True   '可以刷新看结果
End Sub
三、我自己尝试的整合,各种出错,请指点,非常感谢
Public Sub 生成文件()
    Application.ScreenUpdating = False 'Excel的工作表里面数据发生变化后False禁止实时刷新
    Dim arr, wb As Workbook, i As Long, mfullpath$, folderpath$  '定义了变量,long是个变量类型。mfullpath$, folderpath$ ,最后的符号$不知道咋回事,不知道我写没写错。$似乎代表了字符串,文本类型
    arr = Sheet1.UsedRange.Value   '用所有的可用数据给arr赋值,数组?
    folderpath = ThisWorkbook.Path & "\"  '这里是生成的文件储存位置的字符串
    For i = 2 To UBound(arr) '数据(不是表头)行从第几行开始就数字就改成几。  'UBound的是数组的上界(搜索得知)
        If arr(i, 2) <> "" Then
          Set wb = Application.Workbooks.Add  '和新建工作簿有关,或者新建打开工作簿?
            With wb   'with语句起到的是简化代码的作用
              With .Sheets(1)  '表示活动工作簿的第一个工作表。所以需要关掉其他文件。
                For j = 1 To UBound(arr, 2)
                    .Cells(1, j) = arr(1, j)   '取表头
                    .Cells(2, j).Value = arr(i, j) '取内容(生成文件的第二行)
                Next
                    Columns("A:E").Select  '选择列,范围需要根据实际情况调整
                    Selection.ColumnWidth = 11.63  '所有选择的列调整宽度,数值自行调整,请提前测试合适的宽度'
                    Rows("1:2").Select  '选择前两行
                With Selection  '
                     .HorizontalAlignment = xlCenter  '居中
                     .VerticalAlignment = xlCenter  '居中
                     .WrapText = True    '自动换行
                End With  '
                Range("A1:E2").Select  '选择区域,手动根据实际情况自行调整
                Selection.Borders.LineStyle = 1  '所选区域边框线实线
              End With
              mfullpath = folderpath & arr(i, 6) & ".xlsx"  '这里显示的是生成后文件名的构成,姓名在第几列,就把这行的数字改成几
              .SaveAs Filename:=mfullpath, FileFormat:=xlOpenXMLWorkbook, Password:=arr(i, 4), CreateBackup:=False
              '分别代表:保存文件名为XXX(其本质是一个文件夹+文件的地址信息),保存文件格式为.xlsx,密码在第几列就把数字改成几,清空后台数据。
              .Close
            End With
          End If
    Next
    Set wb = Nothing   '释放内存
    Application.ScreenUpdating = True   '可以刷新看结果
End Sub




TA的精华主题

TA的得分主题

 楼主| 发表于 2019-9-8 19:49 | 显示全部楼层
opel-wong 发表于 2019-9-7 10:50
美化后的代码如下:

太感谢了,研究之后我解决了我基本问题,其他还在研究。谢谢了

TA的精华主题

TA的得分主题

发表于 2019-9-9 08:11 | 显示全部楼层
gjytm 发表于 2019-9-8 19:51
太感谢了,研究之后我解决了我基本问题,其他还在研究。谢谢了

按照“三”的意思,给你改了一下,
  1. Public Sub 生成文件()
  2.     Application.ScreenUpdating = False    'Excel的工作表里面数据发生变化后False禁止实时刷新
  3.     Dim arr, wb As Workbook, i As Long, mFullPath$, FolderPath$
  4.     arr = Sheet1.UsedRange.Value   '用所有的可用数据给arr赋值,数组?
  5.     FolderPath = ThisWorkbook.Path & ""  '这里是生成的文件储存位置的字符串
  6.     For i = 2 To UBound(arr)    '数据(不是表头)行从第几行开始就数字就改成几。  'UBound的是数组的上界(搜索得知)
  7.         If arr(i, 2) <> "" Then
  8.             Set wb = Application.Workbooks.Add  '和新建工作簿有关,或者新建打开工作簿?
  9.             With wb   'with语句起到的是简化代码的作用
  10.                 With .Sheets(1)  '表示活动工作簿的第一个工作表。所以需要关掉其他文件。
  11.                     For j = 1 To UBound(arr, 2)
  12.                         .Cells(1, j) = arr(1, j)   '取表头
  13.                         .Cells(2, j).Value = arr(i, j)    '取内容(生成文件的第二行)
  14.                     Next
  15.                     .Columns("A:E").ColumnWidth = 11.63  '所有选择的列调整宽度,数值自行调整,请提前测试合适的宽度'
  16.                     With .Rows("1:2")  '选择前两行
  17.                         .HorizontalAlignment = xlCenter  '居中
  18.                         .VerticalAlignment = xlCenter  '居中
  19.                         .WrapText = True    '自动换行
  20.                     End With  '
  21.                     .Range("A1:E2").Borders.LineStyle = 1  '所选区域边框线实线
  22.                 End With
  23.                 mFullPath = FolderPath & arr(i, 6) & ".xlsx"  '这里显示的是生成后文件名的构成,姓名在第几列,就把这行的数字改成几
  24.                 .SaveAs Filename:=mFullPath, FileFormat:=xlOpenXMLWorkbook, Password:=arr(i, 4), CreateBackup:=False
  25.                 '分别代表:保存文件名为XXX(其本质是一个文件夹+文件的地址信息),保存文件格式为.xlsx,密码在第几列就把数字改成几,清空后台数据。
  26.                 .Close
  27.             End With
  28.         End If
  29.     Next
  30.     Set wb = Nothing   '释放内存
  31.     Application.ScreenUpdating = True   '可以刷新看结果
  32. End Sub
复制代码


您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-11-15 18:40 , Processed in 0.078679 second(s), 14 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表