各位高手,我在调用EXECL打印我控件MSHFlexGrid里的内容时碰到了两个问题,希望能帮小弟解答一下,谢谢了!
第一、调用EXECL打印完了之后,有一个另存为的对话框,如何除掉呢?
第二、调用EXECL打印时,如何在代码中改一下页边距,盼详解,谢谢!
代码如下:
Private Sub print_Click()
On Error Resume Next
If MSHFlexGrid1.TextMatrix(1, 2) = "" Then
MsgBox "没有数据打印", vbInformation, "提示"
Exit Sub
End If
Dim excelApp As Excel.Application
Set excelApp = New Excel.Application
Set excelApp = CreateObject("Excel.Application")
Dim exbook As Excel.Workbook
Dim exsheet As Excel.Worksheet
Set exbook = excelApp.Workbooks.Add '添加空白工作表
excelApp.SheetsInNewWorkbook = 1
excelApp.Visible = True '是否显示导出过程(true是)
excelApp.UserControl = True
Me.MousePointer = vbHourglass '控制鼠标为读取数据
'''''''''''''''''''''''''''''''''''''''''''表头设置'''''''''''''''''''''''''''''''''''''
With excelApp.ActiveSheet '表头合并
.Range("a1:H2").Merge '合并
.Range("a3:b3").Merge '合并
.Range("c3:d3").Merge '合并
.Range("g3:h3").Merge '合并
.Range("a4:b4").Merge '合并
.Range("c4:d4").Merge '合并
.Range("g4:h4").Merge '合并 .Range("a1:H2") = "入库单打印"
.Range("a3:b3") = "入库单号:"
.Range("c3:d3") = t
.Range("e3:e3") = "仓库:" & DataGrid2.Columns("仓库").CellValue(DataGrid2.Book)
.Range("f3:f3") = "入库日期:"
.Range("g3:h3") = DataGrid2.Columns("入库日期").CellValue(DataGrid2.Book)
.Range("a4:b4") = "供应商:"
.Range("c4:d4") = DataGrid2.Columns("供应商").CellValue(DataGrid2.Book)
.Range("e4:e4") = "入库类型:" & DataGrid2.Columns("入库类型").CellValue(DataGrid2.Book)
.Range("f4:f4") = "打印日期"
.Range("g4:h4") = Format$(Now, "yyyy-mm-dd")
.Rows.HorizontalAlignment = xlVAlignCenter '
End With
'With excelApp.ActiveSheet
'.Range("A1:j4").Borders.LineStyle = xlContinuous '表头边框线
' End With
With excelApp.ActiveSheet.Range("a1:H4").Borders(7)
.LineStyle = 1
.Weight = 2
End With
With excelApp.ActiveSheet.Range("a1:H4").Borders(8)
.LineStyle = 1
.Weight = 2
End With
With excelApp.ActiveSheet.Range("a1:H4").Borders(9)
.LineStyle = 1
.Weight = 2
End With
With excelApp.ActiveSheet.Range("a1:H4").Borders(10)
.LineStyle = 1
.Weight = 2
End With
With excelApp.ActiveSheet .Range("a5:a5") = "编号"
.Range("b5:b5") = "入库单号"
.Range("c5:c5") = "商品款号"
.Range("d5:d5") = "商品颜色"
.Range("e5:e5") = "商品尺码"
.Range("f5:f5") = "入库单价"
.Range("g5:g5") = "入库数量"
.Range("h5:h5") = "入库金额"
End With
With excelApp.ActiveSheet
.Cells(1).ColumnWidth = 5
.Cells(2).ColumnWidth = 11 '第一列
.Cells(3).ColumnWidth = 8 '第二列
.Cells(4).ColumnWidth = 8 '第三列
.Cells(5).ColumnWidth = 30 '第四列
.Cells(6).ColumnWidth = 8 '第五列
.Cells(7).ColumnWidth = 8 '第六列
.Cells(8).ColumnWidth = 8 '第七列End With
With excelApp.ActiveSheet
.Range("A5:H5").Borders.LineStyle = xlContinuous '表头边框线
End With
'''''''''''''''''''''''''''导出MSFLEXGRID内容'''''''''''''''''''''''''''''''''
With excelApp.ActiveSheet
For i = 1 To MSHFlexGrid1.Rows
For j = 5 To MSHFlexGrid1.Cols - 2
.Cells(i + 5, j - 1).Value = "" & Format$(MSHFlexGrid1.TextMatrix(i, j))
.Cells(i + 5, 1).Value = "'" & MSHFlexGrid1.TextMatrix(i, 1)
.Cells(i + 5, 2).Value = "'" & MSHFlexGrid1.TextMatrix(i, 2)
.Cells(i + 5, 3).Value = "'" & MSHFlexGrid1.TextMatrix(i, 3)
Next j
.Range("a" & 6 & ":" & "H" & MSHFlexGrid1.Rows + 4).Borders.LineStyle = xlContinuous '设置横线(边框)
Next i
End With
With excelApp.ActiveSheet '单据尾端合并
.Range("a" & MSHFlexGrid1.Rows + 10 & ":" & "C" & MSHFlexGrid1.Rows + 12).Merge '合并
.Rows.HorizontalAlignment = xlVAlignCenter '
End With
With excelApp.ActiveSheet .Range("a" & MSHFlexGrid1.Rows + 10 & ":" & "C" & MSHFlexGrid1.Rows + 12) = "复核"
End With
With excelApp.ActiveSheet '
.Range("D" & MSHFlexGrid1.Rows + 10 & ":" & "E" & MSHFlexGrid1.Rows + 12).Merge '合并
.Rows.HorizontalAlignment = xlVAlignCenter '
End With
With excelApp.ActiveSheet .Range("D" & MSHFlexGrid1.Rows + 10 & ":" & "E" & MSHFlexGrid1.Rows + 12) = "审核"
End With
With excelApp.ActiveSheet '
.Range("F" & MSHFlexGrid1.Rows + 10 & ":" & "G" & MSHFlexGrid1.Rows + 12).Merge '合并
.Rows.HorizontalAlignment = xlVAlignCenter '
End With
With excelApp.ActiveSheet .Range("F" & MSHFlexGrid1.Rows + 10 & ":" & "G" & MSHFlexGrid1.Rows + 12) = "开单"
End With
With excelApp.ActiveSheet.PrintPreviewEnd With
Me.MousePointer = 0 '释放鼠标为读取数据
exbook.Close (True) '关闭EXBOOK
excelApp.Quit '退出
Set exsheet = Nothing '释放EXCEL
Set exbook = Nothing '释放EXCEL
Set excelApp = Nothing '释放EXCEL
excelApp.DisplayAlerts = False
excelApp.ActiveWorkbook.SaveAs FileName:=FilePath
excelApp.DisplayAlerts = True'MsgBox "导出成功!", vbOKOnly + vbInformation, "消息提示"End Sub
第一、调用EXECL打印完了之后,有一个另存为的对话框,如何除掉呢?
第二、调用EXECL打印时,如何在代码中改一下页边距,盼详解,谢谢!
代码如下:
Private Sub print_Click()
On Error Resume Next
If MSHFlexGrid1.TextMatrix(1, 2) = "" Then
MsgBox "没有数据打印", vbInformation, "提示"
Exit Sub
End If
Dim excelApp As Excel.Application
Set excelApp = New Excel.Application
Set excelApp = CreateObject("Excel.Application")
Dim exbook As Excel.Workbook
Dim exsheet As Excel.Worksheet
Set exbook = excelApp.Workbooks.Add '添加空白工作表
excelApp.SheetsInNewWorkbook = 1
excelApp.Visible = True '是否显示导出过程(true是)
excelApp.UserControl = True
Me.MousePointer = vbHourglass '控制鼠标为读取数据
'''''''''''''''''''''''''''''''''''''''''''表头设置'''''''''''''''''''''''''''''''''''''
With excelApp.ActiveSheet '表头合并
.Range("a1:H2").Merge '合并
.Range("a3:b3").Merge '合并
.Range("c3:d3").Merge '合并
.Range("g3:h3").Merge '合并
.Range("a4:b4").Merge '合并
.Range("c4:d4").Merge '合并
.Range("g4:h4").Merge '合并 .Range("a1:H2") = "入库单打印"
.Range("a3:b3") = "入库单号:"
.Range("c3:d3") = t
.Range("e3:e3") = "仓库:" & DataGrid2.Columns("仓库").CellValue(DataGrid2.Book)
.Range("f3:f3") = "入库日期:"
.Range("g3:h3") = DataGrid2.Columns("入库日期").CellValue(DataGrid2.Book)
.Range("a4:b4") = "供应商:"
.Range("c4:d4") = DataGrid2.Columns("供应商").CellValue(DataGrid2.Book)
.Range("e4:e4") = "入库类型:" & DataGrid2.Columns("入库类型").CellValue(DataGrid2.Book)
.Range("f4:f4") = "打印日期"
.Range("g4:h4") = Format$(Now, "yyyy-mm-dd")
.Rows.HorizontalAlignment = xlVAlignCenter '
End With
'With excelApp.ActiveSheet
'.Range("A1:j4").Borders.LineStyle = xlContinuous '表头边框线
' End With
With excelApp.ActiveSheet.Range("a1:H4").Borders(7)
.LineStyle = 1
.Weight = 2
End With
With excelApp.ActiveSheet.Range("a1:H4").Borders(8)
.LineStyle = 1
.Weight = 2
End With
With excelApp.ActiveSheet.Range("a1:H4").Borders(9)
.LineStyle = 1
.Weight = 2
End With
With excelApp.ActiveSheet.Range("a1:H4").Borders(10)
.LineStyle = 1
.Weight = 2
End With
With excelApp.ActiveSheet .Range("a5:a5") = "编号"
.Range("b5:b5") = "入库单号"
.Range("c5:c5") = "商品款号"
.Range("d5:d5") = "商品颜色"
.Range("e5:e5") = "商品尺码"
.Range("f5:f5") = "入库单价"
.Range("g5:g5") = "入库数量"
.Range("h5:h5") = "入库金额"
End With
With excelApp.ActiveSheet
.Cells(1).ColumnWidth = 5
.Cells(2).ColumnWidth = 11 '第一列
.Cells(3).ColumnWidth = 8 '第二列
.Cells(4).ColumnWidth = 8 '第三列
.Cells(5).ColumnWidth = 30 '第四列
.Cells(6).ColumnWidth = 8 '第五列
.Cells(7).ColumnWidth = 8 '第六列
.Cells(8).ColumnWidth = 8 '第七列End With
With excelApp.ActiveSheet
.Range("A5:H5").Borders.LineStyle = xlContinuous '表头边框线
End With
'''''''''''''''''''''''''''导出MSFLEXGRID内容'''''''''''''''''''''''''''''''''
With excelApp.ActiveSheet
For i = 1 To MSHFlexGrid1.Rows
For j = 5 To MSHFlexGrid1.Cols - 2
.Cells(i + 5, j - 1).Value = "" & Format$(MSHFlexGrid1.TextMatrix(i, j))
.Cells(i + 5, 1).Value = "'" & MSHFlexGrid1.TextMatrix(i, 1)
.Cells(i + 5, 2).Value = "'" & MSHFlexGrid1.TextMatrix(i, 2)
.Cells(i + 5, 3).Value = "'" & MSHFlexGrid1.TextMatrix(i, 3)
Next j
.Range("a" & 6 & ":" & "H" & MSHFlexGrid1.Rows + 4).Borders.LineStyle = xlContinuous '设置横线(边框)
Next i
End With
With excelApp.ActiveSheet '单据尾端合并
.Range("a" & MSHFlexGrid1.Rows + 10 & ":" & "C" & MSHFlexGrid1.Rows + 12).Merge '合并
.Rows.HorizontalAlignment = xlVAlignCenter '
End With
With excelApp.ActiveSheet .Range("a" & MSHFlexGrid1.Rows + 10 & ":" & "C" & MSHFlexGrid1.Rows + 12) = "复核"
End With
With excelApp.ActiveSheet '
.Range("D" & MSHFlexGrid1.Rows + 10 & ":" & "E" & MSHFlexGrid1.Rows + 12).Merge '合并
.Rows.HorizontalAlignment = xlVAlignCenter '
End With
With excelApp.ActiveSheet .Range("D" & MSHFlexGrid1.Rows + 10 & ":" & "E" & MSHFlexGrid1.Rows + 12) = "审核"
End With
With excelApp.ActiveSheet '
.Range("F" & MSHFlexGrid1.Rows + 10 & ":" & "G" & MSHFlexGrid1.Rows + 12).Merge '合并
.Rows.HorizontalAlignment = xlVAlignCenter '
End With
With excelApp.ActiveSheet .Range("F" & MSHFlexGrid1.Rows + 10 & ":" & "G" & MSHFlexGrid1.Rows + 12) = "开单"
End With
With excelApp.ActiveSheet.PrintPreviewEnd With
Me.MousePointer = 0 '释放鼠标为读取数据
exbook.Close (True) '关闭EXBOOK
excelApp.Quit '退出
Set exsheet = Nothing '释放EXCEL
Set exbook = Nothing '释放EXCEL
Set excelApp = Nothing '释放EXCEL
excelApp.DisplayAlerts = False
excelApp.ActiveWorkbook.SaveAs FileName:=FilePath
excelApp.DisplayAlerts = True'MsgBox "导出成功!", vbOKOnly + vbInformation, "消息提示"End Sub
excelApp.ActiveWorkbook.Saved = True
excelApp.DisplayAlerts = Trueexbook.Close (True) '关闭EXBOOK
excelApp.Quit '退出
Set exsheet = Nothing '释放EXCEL
Set exbook = Nothing '释放EXCEL
Set excelApp = Nothing '释放EXCEL'MsgBox "导出成功!", vbOKOnly + vbInformation, "消息提示"
第二、调用EXECL打印时,如何在代码中改一下页边距,盼详解,谢谢!
18.设置顶边距为2厘米
eole.ActiveSheet.PageSetup.TopMargin=2/0.035
19.设置底边距为4厘米
eole.ActiveSheet.PageSetup.BottomMargin=4/0.035
20.设置左边距为2厘米
veole.ActiveSheet.PageSetup.LeftMargin=2/0.035
21.设置右边距为2厘米
第二个设置页边距,此代码应该放在什么位置吗?