'ADO连接 public gsCn As string gsCn=”Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=;Initial Catalog= Investment;Data Source= ServerName” '生成 TXT文件 Public sub Qtxt(Question SQL statement as string ) Dim i As Integer Dim j As Integer Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Dim fs, f, ts, s Dim rs As New ADODB.Recordset rs.open Question one SQL statement,gsCn,1,1 ''创建一个文件 Set fs = CreateObject("Scripting.FileSystemObject") fs.CreateTextFile ("c:\" & rs.recordcount &".txt") Set f = fs.GetFile("c:\" & rs.recordcount &".txt") Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault) Do while not rs.eof ts.write " " & rs.fields(“index”).value rs.movenext loop rs.close ts.Close'关闭该文件 End sub'生成Excel文件 Public sub Qexcel(Question SQL statement as string) ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '生成Excel所需的引用 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim iEx As New Excel.Application Dim iExcel As Object iEx.Workbooks.Add Set iExcel = iEx.Worksheets("sheet1") iEx.Sheets.Select 1 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim rs As New ADODB.Recordset rs.open Question SQL statement as string,gsCn,1,1 Do while not rs.eof I=1 iEx.Range("A" & i & ":G" & i).Merge ‘合并单元格 iEx.Range("A" & i).Value = rs.fields(“index”).value Rs.movenext Loop Rs.close iExcel.SaveAs "C:\" & FileName End sub '生成Word文件 Public sub Qword(Question SQL statement as string) Dim wd As Object Set wd = CreateObject ("Word.Basic") wd.FileNewDefault wd.FontSize 20 Dim rs As New ADODB.Recordset Rs.open Question SQL statement,gsCn,1,1 Do while not rs.eof wd.Insert rs(“index”).value Rs.movenext Loop Rs.close wd.FileSaveAs "C:\*.Doc" wd.FileClose Set wd = Nothing End sub
Private Sub slExportInvoice(ByVal sFileN As String) 'Start by jiang 2002/10/11 On Error GoTo ErrHandler Dim lsCaseno As String Dim lsInvoice As String Dim lsContainer As String Dim myworkspace As Workspace Dim mydatabase As DataBase Dim mytable As Recordset Dim iloop As Integer Dim sFileName As String Dim sPathN As String Dim exlApp As Excel.Application Dim lRow As Long Dim lCol As Long Dim lInvRow1 As Long Dim lConRow1 As Long Dim lCasRow1 As Long
public gsCn As string
gsCn=”Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=;Initial Catalog= Investment;Data Source= ServerName”
'生成 TXT文件
Public sub Qtxt(Question SQL statement as string )
Dim i As Integer
Dim j As Integer
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts, s
Dim rs As New ADODB.Recordset
rs.open Question one SQL statement,gsCn,1,1
''创建一个文件
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile ("c:\" & rs.recordcount &".txt")
Set f = fs.GetFile("c:\" & rs.recordcount &".txt")
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)
Do while not rs.eof
ts.write " " & rs.fields(“index”).value
rs.movenext
loop
rs.close
ts.Close'关闭该文件
End sub'生成Excel文件
Public sub Qexcel(Question SQL statement as string)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'生成Excel所需的引用
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim iEx As New Excel.Application
Dim iExcel As Object
iEx.Workbooks.Add
Set iExcel = iEx.Worksheets("sheet1")
iEx.Sheets.Select 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim rs As New ADODB.Recordset
rs.open Question SQL statement as string,gsCn,1,1
Do while not rs.eof
I=1
iEx.Range("A" & i & ":G" & i).Merge ‘合并单元格
iEx.Range("A" & i).Value = rs.fields(“index”).value
Rs.movenext
Loop
Rs.close
iExcel.SaveAs "C:\" & FileName
End sub
'生成Word文件
Public sub Qword(Question SQL statement as string)
Dim wd As Object
Set wd = CreateObject ("Word.Basic")
wd.FileNewDefault
wd.FontSize 20
Dim rs As New ADODB.Recordset
Rs.open Question SQL statement,gsCn,1,1
Do while not rs.eof
wd.Insert rs(“index”).value
Rs.movenext
Loop
Rs.close
wd.FileSaveAs "C:\*.Doc"
wd.FileClose
Set wd = Nothing
End sub
'Start by jiang 2002/10/11
On Error GoTo ErrHandler
Dim lsCaseno As String
Dim lsInvoice As String
Dim lsContainer As String
Dim myworkspace As Workspace
Dim mydatabase As DataBase
Dim mytable As Recordset
Dim iloop As Integer
Dim sFileName As String
Dim sPathN As String
Dim exlApp As Excel.Application
Dim lRow As Long
Dim lCol As Long
Dim lInvRow1 As Long
Dim lConRow1 As Long
Dim lCasRow1 As Long
Me.Enabled = False
Me.MousePointer = vbHourglass
lblts.Visible = True
lblts.Caption = "ハセンラノヨミ..."
DoEvents
Set myworkspace = Workspaces(0)
Set mydatabase = myworkspace.OpenDataBase(G_PATH_WMDB & "\" & GS_MDB_INVOICE_PRT)
Set mytable = mydatabase.OpenRecordset("select * from [invoice attached] order by invoice,conterner,[LOT CASE/MODULE NO],[PART NO]", dbOpenDynaset, dbReadOnly)' If Dir(G_PATH_EXPORT, vbDirectory) = "" Then 'シ・鯡ヌキ贇レオシウソツシ
' MkDir G_PATH_EXPORT
' End If
'
' sPathN = G_PATH_EXPORT & "\" & "Invoice "
sPathN = sFileN
Set exlApp = CreateObject("excel.application")
With exlApp
.ScreenUpdating = False
.DisplayAlerts = False
lbl_NewInvoice:
.Workbooks.Add (1)
'ノ靹テチミソ・
.Columns(1).ColumnWidth = 12
.Columns(2).ColumnWidth = 14
.Columns(3).ColumnWidth = 12
.Columns(4).ColumnWidth = 14
.Columns(5).ColumnWidth = 40
.Columns(6).ColumnWidth = 8
.Columns(7).ColumnWidth = 8
.Columns(8).ColumnWidth = 10
''Header
.Rows(2).HorizontalAlignment = xlCenter
'Bold
.Rows(2).Font.Size = 14
.Rows(2).Font.Bold = True
.Cells(2, 4) = "INVOICE ATTACHED LIST(PARTS)"
.Rows(4).Font.Size = 10
.Rows(4).Font.Bold = True
.Rows(4).HorizontalAlignment = xlCenter
.Cells(4, 1) = "INVOICE NO. :"
.Cells(4, 2) = "CONTAINER NO."
.Cells(4, 3) = "CASE NO."
.Cells(4, 4) = "PART NO."
.Cells(4, 5) = "DESCRIPTION OF GOODS"
.Cells(4, 6) = "Q'TY(PCS)"
.Cells(4, 7) = "UNIT PRICE"
.Cells(4, 8) = "FOB AMOUNT"
'add by tl
' .Cells(4, 1) = "INVOICE NO. :"
' .Cells(5, 1) = "CONTAINER NO."
' .Cells(6, 1) = "CASE NO."
' .Cells(6, 2) = "PART NO."
' .Cells(6, 3) = "DESCRIPTION OF GOODS"
' .Cells(6, 4) = "Q'TY(PCS)"
' .Cells(6, 5) = "UNIT PRICE"
' .Cells(6, 6) = "FOB AMOUNT"
'end
'ノ靹テア゚マ゚
.Cells.Select
.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With .Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Rows("1:3").Select
.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
.Selection.Borders(xlEdgeLeft).LineStyle = xlNone
.Selection.Borders(xlEdgeTop).LineStyle = xlNone
.Selection.Borders(xlEdgeRight).LineStyle = xlNone
.Selection.Borders(xlInsideVertical).LineStyle = xlNone
.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
.Range("B9").Select
'Title
lRow = 5
lsCaseno = ""
lsContainer = ""
lsInvoice = ""
lInvRow1 = 5
lConRow1 = 5
lCasRow1 = 5
While mytable.EOF = False
.Rows(lRow).Font.Size = 10
'Invoiceア莉ッ
If lsInvoice <> mytable.Fields("invoice") Then
If lsInvoice <> "" Then
.Range("A" & lInvRow1, "A" & lRow - 1).Select
.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
--------------------------------
PageSetup 对象代表页面设置说明。PageSetup 对象包含所有页面设置的属性(左边距、底部边距、纸张大小等)。PageSetup 对象用法
可用 PageSetup 属性返回一个 PageSetup 对象。下例将打印方向设置为横向,并打印工作表。With Worksheets("Sheet1")
.PageSetup.Orientation = xlLandscape
.PrintOut
End With
With 语句使同时设置若干属性变得简单而迅速。下例设置第一张工作表的所有页边距。With Worksheets(1).PageSetup
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1.5)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
End With
--------------------
具体内容查看Visual Basic 帮助。