救助表头的问题 - 编程论坛

#2

约定的童话2022-10-03 19:41

这个加个工作表循环，判断表名是否与文本框当前循环的ABC一致...

#3

kissis2022-10-03 20:18

在写入之前就有判断表名是否与文本框相符了
If Text2.Text > "" Then
    MSHFlexGrid1.Redraw = False
    Set xlApp = CreateObject("Excel.Application")
   'Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(App.Path & "\DATA\DATA.xls")
    xlApp.Visible = True '设置EXCEL对象可见
    For i = 1 To xlBook.Sheets.Count
    Named = xlBook.Worksheets(i).Name
     Next i
   Named = Trim(Text2.Text)
   Set xlsheet = xlBook.Worksheets(Named) '设置活动工作表
   With MSHFlexGrid1
     For s = 0 To MSHFlexGrid1.Rows - 1 '行循环
            For j = 0 To MSHFlexGrid1.Cols - 1 '列循环
                 'MSHFlexGrid1.Row = i
                ' MSHFlexGrid1.Col = j
xlBook.Worksheets(Named).Cells(s + 3, j + 1).Value = Trim(MSHFlexGrid1.TextMatrix(s, j)) '从第三行写入数据保存到EXCEL
xlsheet.Cells(3, 1).Value = "序号"
xlsheet.Cells(s + 3, 1).Value = s
Next j
Next s
End With
With xlApp
.Cells.Select '选择整个工作表
.Columns.AutoFit '自动调整列宽以适应文字
.Range(xlApp.Cells(1, 1), xlApp.Cells(1, j)).Merge ' 合并第一行
.Cells(1, 1).Value = "报表样式"
.Cells(1, 1).Font.Size = 20
.Rows(1).Font.Bold = True '第三行粗体
.Selection.HorizontalAlignment = xlCenter  '水平居中
.Range(xlApp.Cells(2, 1), xlApp.Cells(2, j)).Merge
.Cells(2, 1).Font.Size = 12
  .Cells(2, 1).Value = "制表单位: " + Trim(Text2.Text) + " " + Space(10) + "  制表日期：" + Trim(Text1.Text) + ""
End With
MSHFlexGrid1.Redraw = True
Next i
Next i
End If
是不是要在With xlApp之前再加个判断 Named = Trim(Text2.Text)

#4

风吹过b2022-10-03 22:54

设置了活动工作表，按理来都是对这个活动工作表进行操作，
Set xlsheet = xlBook.Worksheets(Named) '设置活动工作表

With xlApp
你这里为啥又要对整个 exlce 对象进行操作呢？？
不是继续对活动工作表进行操作吗？

#5

kissis2022-10-04 00:25

回复 4楼风吹过b

谢谢提醒改成With xlsheet后达到了效果，但文字居中的代码该如何写
xlsheet.Range（S+1,j+1).HorizontalAlignment = xlCenter提示出错
原来只要调整列文字居中就可以了
xlsheet.Cells.HorizontalAlignment = xlCenter

[此贴子已经被作者于2022-10-4 07:43编辑过]

#6

约定的童话2022-10-04 10:35

回复 5楼 kissis

xlsheet.Range（S+1,j+1).HorizontalAlignment = xlCenter改成xlsheet.cells(S+1,j+1).HorizontalAlignment = xlCenter