VBA 控制 Excel 制作表格的例子

Private Sub Workbook_open() ' '--- Define variables. ' Dim xlsbook As Excel.Workbook Dim xlsbookx As Excel.Workbook Dim filen As String Dim strSheetName As String Dim strTXU, strLDS As String Dim strCust, strCustNew, strCustNameNew, strParentNew, strCUR, strCURNew, strMON, strMONNew As String Dim iRow, iCount, iSheet As Integer Dim iNewSheet As Boolean ' '--- Handle C:/TEMP/MIS/Report/TMP0052304.xls ' Application.ScreenUpdating = False ' Set xlsbook = Workbooks.Open(Filename:="C:/TEMP/MIS/Report/P00523/TMP0052301.xls") xlsbook.Save xlsbook.Close Set xlsbook = Workbooks.Open(Filename:="C:/TEMP/MIS/Report/P00523/TMP0052302.xls") strTXU = Trim(xlsbook.Sheets(1).Cells(2, 1).Value) strLDS = Trim(xlsbook.Sheets(1).Cells(2, 3).Value) xlsbook.Save xlsbook.Close ' '--- Handle C:/TEMP/MIS/Report/P00523_Form.xls. ' Application.ScreenUpdating = False Set xlsbookx = Workbooks.Open(Filename:="C:/TEMP/MIS/Program/P00523_SUMForm.xls", UpdateLinks:="3") Set xlsbook = Workbooks.Open(Filename:="C:/TEMP/MIS/Report/P00523/TMP0052301.xls") ' '**** 2009.05.13 ' iRow = 2 iCount = 8 iSheet = 1 iNewSheet = False strCust = "Null" strCustNew = Trim(xlsbook.Sheets(1).Cells(iRow, 8).Value) strCustNameNew = Trim(xlsbook.Sheets(1).Cells(iRow, 9).Value) strParentNew = Trim(xlsbook.Sheets(1).Cells(iRow, 10).Value) strCUR = "Null" strCURNew = Trim(xlsbook.Sheets(1).Cells(iRow, 17).Value) strMON = "190101" strMONNew = Mid(Trim(xlsbook.Sheets(1).Cells(iRow, 3).Value), 1, 6) Do While strCustNew <> "" '---B1 iNewSheet = False If (strCust = strCustNew) And (strMON = strMONNew) And (strCUR = strCURNew) Then '---B2 Else '---X2 If (strCust <> "Null") Then '---B3 LastSheet = xlsbookx.Sheets.Count If (LastSheet > 1) And (iCount > 8) Then '---B4 xlsbookx.Sheets(LastSheet).Range("A8:Q" & iCount - 1).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