vb中控制Excel问题

[sangye]

各位大虾：请问怎样在vb中控制Excel中的显示格式：比如格线，对齐方式，合并单元各，
字体等。或者，能否在vb向Excel写数据的时候调用事先保存的excel模板？

[ykytw]

用EXCEL录制宏不就都知道了。
当然可以。

[chenyu5188]

我也要一份呀…！！！
[e-mail]

QQ:5367778

[jiami]

Set xlapp = Nothing
Set x_Cls = New ClsPrint
x_Cls.OpenFile = App.Path + "\report\print.xls"
x_Cls.OpenSheet = "account"
If x_Cls.OpenXlsObj Then
'Set xlsheet = New Excel.Worksheet
Set xlsheet = x_Cls.GetSheet

xlsheet.Columns("a:h").AutoFit
x_Cls.SetVisible = True
xlsheet.PrintPreview
Set xlsheet = Nothing
Set x_Cls = Nothing

[jiami]

用EXCLE的宏录，用VBA看就知道了，代码可以用。

[liad]

1.Project->References->Microsoft Excel 9.0 Object Library
加入EXCEL 2000库支持

2.
Private Sub Command1_Click()
On Error Resume Next

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim i, j As Integer
Dim iXlRow As Integer
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then
'***************************************************************
' Excel is NOT running, so create a new instance
'***************************************************************
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
End If

If Err.Number <> 429 And Err.Number <> 0 Then
MsgBox "打开 Excel 时发生错误!请检查是否正确安装了 Excel 2000 或更高版本!", vbExclamation, App.Title
End If
Err.Clear
Set xlSheet = xlApp.Worksheets.Add
If Err.Number Then
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlApp.Worksheets.Add
End If
On Error GoTo ErrH
'*******************************************************************
' Make sure Excel is visible
'*******************************************************************

xlApp.Visible = True
xlSheet.Cells.Font.Size = 10
xlSheet.Cells.HorizontalAlignment = xlCenter

With xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, 3))
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
xlSheet.Cells(1, 1) = "WELCOME EXCEL2000"
xlSheet.Cells(1, 1).Font.Size = 12
xlSheet.Cells(1, 1).Font.Bold = True

xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(4, 3)).BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(4, 3)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(4, 3)).Borders(xlInsideHorizontal).Weight = xlThin
xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(4, 3)).Borders(xlInsideVertical).LineStyle = xlContinuous
xlSheet.Range(xlSheet.Cells(3, 1), xlSheet.Cells(4, 3)).Borders(xlInsideVertical).Weight = xlThin
'####到最适合列宽
xlSheet.Columns.AutoFit

With xlSheet.PageSetup
.TopMargin = 20
.LeftMargin = 20
.BottomMargin = 75
.RightMargin = 20
.Orientation = xlLandscape
.CenterHorizontally = True
.RightFooter = "第&P页,共&N页"
End With
xlSheet.PrintPreview


Set xlSheet = Nothing
Set xlApp = Nothing
Exit Sub
ErrH:
MsgBox "打开 Excel 时发生错误!请检查是否正确安装了 Excel 2000 或更高版本!如Excel 正在运行，请先关闭！" & Err.Description, vbExclamation, App.Title
End Sub

[sangye]

laid:谢谢了。还想问一下，我的表格很宽，通常打印的时候需要在“分页预览”视图中拖动分页符的位置让横向在一张纸的宽度范围内，能否在程序里控制让横向
的分页符位置自动将所有内容设在一页范围内。我不想通过设置每列的具体宽度来
实现这一点，也就是分页符的位置能否控制？－－也就是能否控制“缩放比例”？
谢谢

[cgh1970]

给E-MAIL，我发给你源代码！

[sangye]

[e-mail],谢谢

[cgh1970]

我发过了

[sangye]

没用过excel宏，能够说得详细点吗，谢了！

[zhang_hua268]

关注

[wujs]

up
别忘了给我一份！

[sandyen]

被忘了给我一份，我的名字跟你差不多亚，哈哈

[liad]

@MSDN
This example causes Microsoft Excel to print Sheet1 exactly one page wide and tall.

With Worksheets("Sheet1").PageSetup
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
End With

[sangye]

问题已经解决，感谢laid,cgh1970,jiami几位朋友。