Access交流中心

北京 | 上海 | 天津 | 重庆 | 广州 | 深圳 | 珠海 | 汕头 | 佛山 | 中山 | 东莞 | 南京 | 苏州 | 无锡 | 常州 | 南通 | 扬州 | 徐州 | 杭州 | 温州 | 宁波 | 台州 | 福州 | 厦门 | 泉州 | 龙岩 | 合肥 | 芜湖 | 成都 | 遂宁 | 长沙 | 株洲 | 湘潭 | 武汉 | 南昌 | 济南 | 青岛 | 烟台 | 潍坊 | 淄博 | 济宁 | 太原 | 郑州 | 石家庄 | 保定 | 唐山 | 西安 | 大连 | 沈阳 | 长春 | 昆明 | 兰州 | 哈尔滨 | 佳木斯 | 南宁 | 桂林 | 海口 | 贵阳 | 西宁 | 乌鲁木齐 | 包头 |

怎样在导出表的这段程序中增加一个选择新文件夹的语句?谢谢!

李建平  发表于:2013-08-02 09:41:56  
复制

随着导出的表格增加,一个文件夹存放溢出,造成程序中断;能不能在文件夹存不下的情况下,提示选择新文件夹,确定后继续运行,直致所有的表导出完成,谢谢!下附程序
Private Sub 全部导出_Click()
Dim myFSO As New FileSystemObject
Dim myFolder As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset
Dim sql1 As String, sql2 As String
Dim i As Long, j As Long
Dim myfile As String
myFolder = GetFolder

sql1 = "SELECT 业务员 FROM 结算 GROUP BY 业务员;"
rs1.Open sql1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


For i = 1 To rs1.RecordCount
    myfile = rs1("业务员") & ".xls"
    If myFSO.FileExists(myFolder & "\" & myfile) = False Then
        Call CreatE(myfile, myFolder)
    End If
        
    sql2 = "SELECT * FROM 结算 where 业务员='" & rs1("业务员") & "'"
    rs2.Open sql2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Application.Visible = True
    Set xlBook = xlApp.Workbooks.Open(myFolder & "\" & myfile)
    '导出主表
    xlBook.Application.Sheets(1).Select
    xlBook.Application.Range("A1").Value = "姓    名:"
    xlBook.Application.Range("B1").Value = rs1("业务员")
   
   
    xlBook.Application.Range("A2").Value = "回款日期"
    xlBook.Application.Range("b2").Value = "厂名"
    xlBook.Application.Range("c2").Value = "回款类别"
    xlBook.Application.Range("d2").Value = "地区"
    xlBook.Application.Range("e2").Value = "产品"
    xlBook.Application.Range("f2").Value = "分配金额"
    xlBook.Application.Range("g2").Value = "单价"
    xlBook.Application.Range("h2").Value = "分配数量"
    xlBook.Application.Range("i2").Value = "考核率"
    xlBook.Application.Range("j2").Value = "增长率"
   
    '导出子表
    For j = 1 To rs2.RecordCount
        xlBook.Application.Cells(j + 2, 1).Value = rs2("回款日期")
        xlBook.Application.Cells(j + 2, 1).NumberFormatLocal = "yyyy/m/d;@"
        xlBook.Application.Cells(j + 2, 2).Value = rs2("厂名")
        xlBook.Application.Cells(j + 2, 3).Value = rs2("回款类别")
        xlBook.Application.Cells(j + 2, 4).Value = rs2("地区")
        xlBook.Application.Cells(j + 2, 5).Value = rs2("产品")
        xlBook.Application.Cells(j + 2, 6).Value = rs2("分配金额")
        xlBook.Application.Cells(j + 2, 7).Value = rs2("单价")
        xlBook.Application.Cells(j + 2, 8).Value = rs2("分配数量")
        xlBook.Application.Cells(j + 2, 9).Value = rs2("考核率")
        xlBook.Application.Cells(j + 2, 10).Value = rs2("增长率")
        
        rs2.MoveNext
    Next
    xlBook.Save
    xlApp.Quit
    rs1.MoveNext
    rs2.Close
Next
rs1.Close

End Sub

 

Top
hongyin 发表于:2013-08-02 10:57:46
Private Sub 全部导出_Click()
    Dim myFSO As New FileSystemObject
    Dim myFolder As String
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset
    Dim sql1 As String, sql2 As String
    Dim i As Long, j As Long
    Dim myfile As String
    myFolder = GetFolder

    sql1 = "SELECT 业务员 FROM 结算 GROUP BY 业务员;"
    rs1.Open sql1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


    For i = 1 To rs1.RecordCount
        myfile = rs1("业务员") & ".xls"

        '每100个另选文件夹
        If i Mod 100 = 0 Then
            myFolder = GetFolder
        End If

        '        '其实如文件很多,不如设一个编号的文件夹,比较方便,不用另选。如:
        '        If i Mod 100 = 0 Then
        '            myFolder = myFolder & i
        '        End If

        If myFSO.FileExists(myFolder & "\" & myfile) = False Then
            Call Create(myfile, myFolder)
        End If

        sql2 = "SELECT * FROM 结算 where 业务员='" & rs1("业务员") & "'"
        rs2.Open sql2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

        Set xlApp = CreateObject("Excel.Application")
        xlApp.Application.Visible = True
        Set xlBook = xlApp.Workbooks.Open(myFolder & "\" & myfile)
        '导出主表
        xlBook.Application.Sheets(1).Select
        xlBook.Application.Range("A1").Value = "姓    名:"
        xlBook.Application.Range("B1").Value = rs1("业务员")


        xlBook.Application.Range("A2").Value = "回款日期"
        xlBook.Application.Range("b2").Value = "厂名"
        xlBook.Application.Range("c2").Value = "回款类别"
        xlBook.Application.Range("d2").Value = "地区"
        xlBook.Application.Range("e2").Value = "产品"
        xlBook.Application.Range("f2").Value = "分配金额"
        xlBook.Application.Range("g2").Value = "单价"
        xlBook.Application.Range("h2").Value = "分配数量"
        xlBook.Application.Range("i2").Value = "考核率"
        xlBook.Application.Range("j2").Value = "增长率"

        '导出子表
        For j = 1 To rs2.RecordCount
            xlBook.Application.Cells(j + 2, 1).Value = rs2("回款日期")
            xlBook.Application.Cells(j + 2, 1).NumberFormatLocal = "yyyy/m/d;@"
            xlBook.Application.Cells(j + 2, 2).Value = rs2("厂名")
            xlBook.Application.Cells(j + 2, 3).Value = rs2("回款类别")
            xlBook.Application.Cells(j + 2, 4).Value = rs2("地区")
            xlBook.Application.Cells(j + 2, 5).Value = rs2("产品")
            xlBook.Application.Cells(j + 2, 6).Value = rs2("分配金额")
            xlBook.Application.Cells(j + 2, 7).Value = rs2("单价")
            xlBook.Application.Cells(j + 2, 8).Value = rs2("分配数量")
            xlBook.Application.Cells(j + 2, 9).Value = rs2("考核率")
            xlBook.Application.Cells(j + 2, 10).Value = rs2("增长率")

            rs2.MoveNext
        Next
        xlBook.Save
        xlApp.Quit
        rs1.MoveNext
        rs2.Close
    Next
    rs1.Close

End Sub


李建平 发表于:2013-08-02 11:47:54
首先先谢谢你的解答!我试试,经过运行新的程序,原来一个文件夹放不下,现在都放在一个文件夹了,没有提示换新文件夹

总记录:2篇  页次:1/1 9 1 :