Access导出excel修改标题无法修改

盗到稻

游客，如果您要查看本帖隐藏内容请回复

老是说91错误，好像偶有几次也能通过，百思不解，恳请帮助

盗到稻

原代码如下：
Dim i As Integer, lblName As String, j As Integer
Dim TP(15) As String

        Dim xlApp As Excel.Application '定义EXCEL类
    Dim xlBook As Excel.Workbook '定义工件簿类
    Dim xlSheet As Excel.Worksheet '定义工作表类
   

For i = 1 To 15
    lblName = "Label" & Format(i, "00")
    If Me.Child0.Form(lblName).Visible = True Then
        TP(i) = Me.Child0.Form(lblName).Caption
        j = i
    Else      
        Exit For
    End If
Next i
'导出到Excel
    Dim stDocName As String
    stDocName = ChrW(23548) & ChrW(20986) & ChrW(69) & ChrW(120) & ChrW(99) & ChrW(101) & ChrW(108)
    DoCmd.OutputTo acForm, "StarShow", "MicrosoftExcelBiff8(*.xls)", "", True, "", 0
'导出的数据标题不直观
'修改Excel中第一列的标题
    Set xlSheet = ActiveSheet
   
    xlSheet.Cells(1, 1) = "姓 名"        '这里就出错了
    xlSheet.Cells(1, 2) = "义工编号"
    xlSheet.Cells(1, 3) = "合 计"
   
    i = 1
    For i = 1 To j
   
        xlSheet.Cells(1, i + 3) = TP(i)
        
    Next i
   
xlBook.Save  '保存

Set xlSheet = Nothing
Set xlApp = Nothing
Set xlBook = Nothing

Erase TP  '回收数组

[ 本帖最后由 盗到稻 于 2007-11-28 19:57 编辑 ]

tmtony

中间代码修改如下:
    Dim xlApp As Excel.Application '定义EXCEL类
    Dim xlBook As Excel.Workbook '定义工件簿类
    Dim xlSheet As Excel.Worksheet '定义工作表类
   

'For i = 1 To 15
'    lblName = "Label" & Format(i, "00")
'    If Me.Child0.Form(lblName).Visible = True Then
'        TP(i) = Me.Child0.Form(lblName).Caption
'        j = i
'    Else
        
'        Exit For
'    End If
'Next i

    Dim stDocName As String

    stDocName = ChrW(23548) & ChrW(20986) & ChrW(69) & ChrW(120) & ChrW(99) & ChrW(101) & ChrW(108)
    DoCmd.OutputTo acForm, "StarShow", "MicrosoftExcelBiff8(*.xls)", "", True, "", 0
    'DoCmd.RunMacro stDocName
    'DoCmd.OutputTo acOutputForm, stDocName, acFormatXLS, , True
   
   
    'AppActivate "Microsoft Excel"
    Set xlApp = GetObject(, "Excel.Application")
    'Set xlBook = GetObject("StarShow.xls")
    'Debug.Print ActiveWindow.Caption
    Set xlBook = xlApp.ActiveWorkbook
    Set xlSheet = xlApp.ActiveSheet
    'xlSheet.Activate
    'xlApp.Visible = True
   
   
    xlSheet.Cells(1, 1) = "编 号"
    xlSheet.Cells(1, 2) = "姓 名"
    xlSheet.Cells(1, 3) = "手 机"
    xlSheet.Cells(1, 4) = "电 话"
就可以了,因为你对未获得Excel对象,所以出错

tmtony

另外,输入文件可能还未完成,所以在 Set xlApp = GetObject(, "Excel.Application")
之前要 用程序 sleep 几秒钟

盗到稻

感谢王站，

盗到稻

如果Excel打开了，就不会出错，如果Excel已经打开了会出现429的错误
所以加入了一个判断Excel是否打开的代码：

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As Long) As Long

    '判断Excel是已经打开
    Dim hWnd As Long
        hWnd = FindWindow("XLMAIN", 0)
        If hWnd = 0 Then
               Set xlApp = New Excel.Application
         End If

另外王站你说的Sleep函数是不是延时功能的？是不是类似下面的？
Private Declare Function timeGetTime Lib "winmm.dll" () As Long   '
Public Function atSleep(WaitDate As Integer)
Dim Savetime As Double
Savetime = timeGetTime '记下开始时的时间

While timeGetTime < Savetime + WaitDate
    DoEvents
Wend

End Function

VBA中没有找到Sleep函数，不过好像用不上。

kokolc

学习学习学习学习！！

yori2007

学习学习啊 ！！！！！！！！！！！！！

phoenix_xh

原代码是不是有问题

phoenix_xh

楼主能不能把修改过的代码再共享一下,特别是判断excel是不是打开的那一段代码,我放入程序后出错:
"编译错误
在end sub、end function 或end 属性后面只能出现注释
"
我想用这个代码,请楼主帮下忙.