1、Access的表可以读到纪录集中
DataGrid的数据源本身就是纪录集
2、使用纪录集的getstring方法可以返回字符串,记住列分割符用vbTab,行分割符用回车
3、在VB中打开一个Excel,定位到要复制区域的左上角
4、用clipboard.settext将纪录集转换成的字符串复制到剪贴板,用Sendkeys "^V"将剪贴板中的值粘贴到Excel
DataGrid的数据源本身就是纪录集
2、使用纪录集的getstring方法可以返回字符串,记住列分割符用vbTab,行分割符用回车
3、在VB中打开一个Excel,定位到要复制区域的左上角
4、用clipboard.settext将纪录集转换成的字符串复制到剪贴板,用Sendkeys "^V"将剪贴板中的值粘贴到Excel
Public Function Export(ByVal mFileName As String) As Boolean If mTemplateFile = "" Then Exit FunctionOn Error GoTo HandlerErr Dim sFileName As String
Dim i, iRow As Integer
Dim tmpRs As New ADODB.Recordset
Dim ExcelApp As New Excel.Application
Dim ExcelWk As Excel.Workbook
Dim ExcelSheet As Excel.WorkSheet
ExcelApp.Visible = False
Set ExcelWk = ExcelApp.Workbooks.Add
Set ExcelSheet = ExcelWk.Worksheets.Add
ExcelWk.Sheets(1)
Set ExcelSheet = ExcelWk.ActiveSheet
Set ExcelSheet = ExcelWk.Sheets(mWorkSheet)
ExcelSheet.Activate
tmpRs.Open mStrSQL, tmpCn, adOpenForwardOnly, adLockReadOnly
iRow = mStartRow
Do While Not tmpRs.EOF
For i = 0 To tmpRs.Fields.Count - 1
ExcelSheet.Cells(iRow, i + 1) = tmpRs(i).Value & ""
Next
iRow = iRow + 1
tmpRs.MoveNext
Loop
tmpRs.Close
ExcelWk.SaveAs mFileName
Set ExcelSheet = Nothing
ExcelWk.Close
Set ExcelWk = Nothing
Set ExcelApp = Nothing
Set tmpRs = Nothing
Export = True
Exit FunctionHandlerErr:
' Err.Raise Err.Number, "Export to Excel", Err.Description
ExcelWk.Close
Set ExcelSheet = Nothing
Set ExcelWk = Nothing
Set ExcelApp = Nothing
Set tmpRs = Nothing
Export = False
End Function
Dim nowdate As String
Dim wkbObj As Workbook
Dim wksObj As Worksheet
Dim i As Integer
Dim totalRe As Integer
MousePointer = 11
totalRe = 0
nowdate = Format(Date, "YYYYMMDD")
Workbooks.Add
Set wkbObj = Workbooks.Item(1)
Set wksObj = wkbObj.Worksheets(1)
wksObj.Range("A1", "H1").Merge
wksObj.Range("A1", "H1").HorizontalAlignment = 3
wksObj.Range("A1", "H1").value = "入荷予定組合員リスト"
wksObj.Range("F2", "H2").Merge
wksObj.Range("F2", "H2").Font.Size = 8
wksObj.Range("F2", "H2").HorizontalAlignment = 4
wksObj.Range("F2", "H2").value = "入荷予定日 " + left(nowdate, 4) + "年" + _
Mid(nowdate, 5, 2) + "月" + right(nowdate, 2) + "日" wksObj.Range("A3", "H3").RowHeight = wksObj.Range("A3", "H3").RowHeight * 2
Call wksObj.Range("A3", "A4").BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("A3", "A4").HorizontalAlignment = 3
wksObj.Range("A3").value = "生産者"
wksObj.Range("A4").value = "コード"
Call wksObj.Range("B3", "B4").BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("B3", "B4").HorizontalAlignment = 3
wksObj.Range("B3").value = "組合員氏名"
Call wksObj.Range("C3", "C4").BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("C3", "C4").HorizontalAlignment = 3
wksObj.Range("C3").value = "入荷予定"
wksObj.Range("C4").value = "数量"
Call wksObj.Range("D3", "D4").BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("D3", "D4").HorizontalAlignment = 3
wksObj.Range("D3").value = "検査日"
Call wksObj.Range("E3", "E4").BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("E3", "E4").HorizontalAlignment = 3
wksObj.Range("E3").value = "住所"
Call wksObj.Range("F3", "F4").BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("F3", "F4").HorizontalAlignment = 3
wksObj.Range("F3").value = "電話番号"
Call wksObj.Range("G3", "G4").BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("G3", "G4").HorizontalAlignment = 3
wksObj.Range("G3").value = "圃場"
wksObj.Range("G4").value = "NO"
Call wksObj.Range("H3", "H4").BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("H3", "H4").HorizontalAlignment = 3
wksObj.Range("H3").value = "筆コード" For i = 1 To sprList.maxrows
sprList.Row = i
sprList.Col = 1
Call wksObj.Range("A" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("A" + Format(i + 4)).NumberFormat = "000"
wksObj.Range("A" + Format(i + 4)).value = sprList.Text sprList.Col = 2
Call wksObj.Range("B" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("B" + Format(i + 4)).value = sprList.Text
sprList.Col = 3
Call wksObj.Range("C" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("C" + Format(i + 4)).value = sprList.Text
sprList.Col = 4
Call wksObj.Range("D" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("D" + Format(i + 4)).value = sprList.Text
sprList.Col = 5
Call wksObj.Range("E" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("E" + Format(i + 4)).value = sprList.Text
sprList.Col = 6
Call wksObj.Range("F" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("F" + Format(i + 4)).value = sprList.Text
sprList.Col = 7
Call wksObj.Range("G" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("G" + Format(i + 4)).NumberFormat = "00"
wksObj.Range("G" + Format(i + 4)).value = sprList.Text
sprList.Col = 8
Call wksObj.Range("H" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("H" + Format(i + 4)).NumberFormat = "00"
wksObj.Range("H" + Format(i + 4)).value = sprList.Text
totalRe = totalRe + 1
Next
'If (sprList.maxrows < 40 And wksObj.HPageBreaks.Count = 0) Then
i = sprList.maxrows + 1
Do While i <= (wksObj.StandardHeight * 40 - wksObj.Range("A5", "A" + Format(sprList.maxrows + 4)).Height) / wksObj.StandardHeight
Call wksObj.Range("A" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("A" + Format(i + 4)).value = " "
Call wksObj.Range("B" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("B" + Format(i + 4)).value = " "
Call wksObj.Range("C" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("C" + Format(i + 4)).value = " "
Call wksObj.Range("D" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("D" + Format(i + 4)).value = " "
Call wksObj.Range("E" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("E" + Format(i + 4)).value = " "
Call wksObj.Range("F" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("F" + Format(i + 4)).value = " "
Call wksObj.Range("G" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("G" + Format(i + 4)).value = " "
Call wksObj.Range("H" + Format(i + 4)).BorderAround(1, xlThin, xlColorIndexAutomatic, RGB(255, 0, 0))
wksObj.Range("H" + Format(i + 4)).value = " "
totalRe = totalRe + 1
i = i + 1
Loop
'End If
wksObj.Range("A3", "A" + Format(totalRe + 3)).ColumnWidth = wksObj.Range("A3", "A" + Format(totalRe + 3)).ColumnWidth * 4 / 5
wksObj.Range("B3", "B" + Format(totalRe + 3)).ColumnWidth = wksObj.Range("B3", "B" + Format(totalRe + 3)).ColumnWidth * 5 / 4
wksObj.Range("C3", "C" + Format(totalRe + 3)).ColumnWidth = wksObj.Range("C3", "C" + Format(totalRe + 3)).ColumnWidth * 9 / 10
wksObj.Range("E3", "E" + Format(totalRe + 3)).ColumnWidth = wksObj.Range("E3", "E" + Format(totalRe + 3)).ColumnWidth * 8 / 3
wksObj.Range("E3", "E" + Format(totalRe + 3)).WrapText = True
wksObj.Range("F3", "F" + Format(totalRe + 3)).ColumnWidth = wksObj.Range("F3", "F" + Format(totalRe + 3)).ColumnWidth * 6 / 5
wksObj.Range("G3", "G" + Format(totalRe + 3)).ColumnWidth = wksObj.Range("G3", "G" + Format(totalRe + 3)).ColumnWidth * 4 / 5
wksObj.Range("H3", "H" + Format(totalRe + 3)).ColumnWidth = wksObj.Range("H3", "H" + Format(totalRe + 3)).ColumnWidth * 4 / 5
' Do While wksObj.HPageBreaks.Count > 0 And sprList.maxrows < 40 And i > sprList.maxrows
' wksObj.Range("A" + Format(i + 4), "H" + Format(i + 4)).Delete
' i = i - 1
' Loop
wksObj.PrintOut
wkbObj.Close SaveChanges:=False
MousePointer = 0
End Sub
这是一个利用excel来打印的程序段。