Excel VBA Sample

ExcelVBASample1viewplaincopytoclipboardprint?01.PrivateSubWorkbook_open()02.'03.'---Definevariables.04.'05.DimstrFileGAsString06.DimstrFileIAsString07.DimstrFileLAsString08.DimExcelFileAsExcel.Workbook09.'10.'---HandleC:\TEMP\MIS\Report\TMP0046701.xls11.'12.OpenFileC:\TEMP\MIS\Report\TMP0046701.xls13.'14.'---HandleC:\TEMP\MIS\Report\P00467_Form.xls.15.'16.AddC:\TEMP\MIS\Program\P00467_Form.xls,C:\TEMP\MIS\Report\TMP0046701.xls17.'18.'---SaveC:\TEMP\MIS\Report\P00467_Form.xls.19.'20.Application.ScreenUpdating=False21.SetExcelFile=Workbooks.Open(C:\TEMP\MIS\Program\P00467_Form.xls,UpdateLinks:=3)22.strFileG=C:\TEMP\MIS\Report\P00467_+Format(Date,yyyymmdd)+Format(Time,hhmmss)23.ExcelFile.SaveAs(strFileG)24.SetExcelFile=Nothing25.26.EndSub27.28.PrivateSubOpenFile(FileNAsString)29.30.DimxlsFileAsExcel.Workbook31.32.IfLen(Dir(FileN,vbDirectory))0Then33.Application.ScreenUpdating=False34.SetxlsFile=Workbooks.Open(FileName:=FileN)35.xlsFile.Save36.xlsFile.CloseSaveChanges:=True37.SetxlsFile=Nothing38.EndIf39.40.EndSub41.42.PrivateSubAdd(FileN1AsString,FileN2AsString)43.44.DimxlsFile1AsExcel.Workbook45.DimxlsFile2AsExcel.Workbook46.47.IfLen(Dir(FileN2,vbDirectory))0Then48.'49.'---Insertdata.50.'51.Application.ScreenUpdating=False52.SetxlsFile1=Workbooks.Open(FileName:=FileN1,UpdateLinks:=3)53.SetxlsFile2=Workbooks.Open(FileName:=FileN2)54.Application.GotoReference:=R2C155.Range(A2:H600).Select56.Selection.Copy57.xlsFile1.Sheets(Spreadsheet).PasteDestination:=xlsFile1.Sheets(Spreadsheet).Range(A4)58.Application.CutCopyMode=False59.'60.Range(K2:K2).Select61.Selection.Copy62.xlsFile1.Sheets(Spreadsheet).PasteDestination:=xlsFile1.Sheets(Spreadsheet).Range(D2)63.Application.CutCopyMode=False64.'65.Range(J2:J2).Select66.Selection.Copy67.xlsFile1.Sheets(Spreadsheet).PasteDestination:=xlsFile1.Sheets(Spreadsheet).Range(F2)68.Application.CutCopyMode=False69.'70.xlsFile2.CloseSaveChanges:=True71.SetxlsFile2=Nothing72.73.'74.'---Formatdata.75.'76.Application.ScreenUpdating=False77.xlsFile1.Worksheets(Spreadsheet).Select78.xlsFile1.Worksheets(Spreadsheet).Range(A1:H65536).Columns.AutoFit79.'80.Range(D2:D2).Select81.WithSelection.Interior82..ColorIndex=483..Pattern=xlSolid84.EndWith85.Range(F2:F2).Select86.WithSelection.Interior87..ColorIndex=488..Pattern=xlSolid89.EndWith90.'91.Range(A4:G600).Select92.WithSelection93..HorizontalAlignment=xlCenter94..VerticalAlignment=xlCenter95..WrapText=False96..Orientation=097..AddIndent=False98..ShrinkToFit=False99..MergeCells=False100.EndWith101.'102.'---Closefiles.103.'104.xlsFile1.CloseSaveChanges:=True105.SetxlsFile1=Nothing106.EndIf107.108.EndSubExcelVBASample2---P00523收藏viewplaincopytoclipboardprint?01.PrivateSubWorkbook_open()02.'03.'---Definevariables.04.'05.DimxlsbookAsExcel.Workbook06.DimxlsbookxAsExcel.Workbook07.DimfilenAsString08.DimstrSheetNameAsString09.DimstrTXUAsString10.DimstrTDSAsString11.DimstrLCCAsString12.DimstrLDSAsString13.DimstrRPTAsString14.DimstrTYPAsString15.DimstrOPTAsString16.DimstrPERAsString17.DimstrVATAsString18.DimstrCustAsString19.DimstrCustNewAsString20.DimstrCustNameNewAsString21.DimstrParentNewAsString22.DimstrCURAsString23.DimstrCURNewAsString24.DimiRow1AsInteger25.DimiRow2AsInteger26.DimiCountAsInteger27.DimiSheetAsInteger28.DimiNewSheetAsBoolean29.'30.'---HandleC:\TEMP\MIS\Report\TMP0052301.xls31.'32.Application.ScreenUpdating=False33.'34.Setxlsbook=Workbooks.Open(Filename:=C:\TEMP\MIS\Report\P00523\TMP0052301.xls)35.xlsbook.Save36.xlsbook.Close37.'38.'---HandleC:\TEMP\MIS\Report\TMP0052302.xls39.'40.Setxlsbook=Workbooks.Open(Filename:=C:\TEMP\MIS\Report\P00523\TMP0052302.xls)41.'42.'---Defaultvariable43.'44.strTXU=Trim(xlsbook.Sheets(1).Cells(2,1).Value)45.strTDS=Trim(xlsbook.Sheets(1).Cells(2,2).Value)46.strLCC=Trim(xlsbook.Sheets(1).Cells(2,3).Value)47.strLDS=Trim(xlsbook.Sheets(1).Cells(2,4).Value)48.strRPT=Trim(xlsbook.Sheets(1).Cells(2,5).Value)49.strTYP=Trim(xlsbook.Sheets(1).Cells(2,6).Value)50.strOPT=Trim(xlsbook.Sheets(1).Cells(2,7).Value)51.strPER=Trim(xlsbook.Sheets(1).Cells(2,8).Value)52.xlsbook.Save53.xlsbook.Close54.'55.'---HandleC:\TEMP\MIS\Report\P00523_SUMForm.xls.56.'57.Application.ScreenUpdating=False58.Setxlsbookx=Workbooks.Open(Filename:=C:\TEMP\MIS\Program\P00523_SUMForm.xls,UpdateLinks:=3)59.Setxlsbook=Workbooks.Open(Filename:=C:\TEMP\MIS\Report\P00523\TMP0052301.xls)60.'61.'****2009.05.1362.'63.iRow=264.iCount=865.iSheet=166.iNewSheet=False67.68.strCust=Null69.strCustNew=Trim(xlsbook.Sheets(1).Cells(iRow,8).Value)70.strCustNameNew=Trim(xlsbook.Sheets(1).Cells(iRow,9).Value)71.strParentNew=Trim(xlsbook.Sheets(1).Cells(iRow,10).Value)72.strCUR=Null73.strCURNew=Trim(xlsbook.Sheets(1).Cells(iRow,17).Value)74.strVAT=N75.76.DoWhilestrCustNew'---B177.78.iNewSheet=False79.'80.'---SamePage(File)81.'82.If(strCust=strCustNew)And(strCUR=strCURNew)Then'---B283.84.Else'---X285.86.If(strCustNull)Then'---B387.88.LastSheet=xlsbookx.Sheets.Count89.If(LastSheet1)And(iCount8)Then'---B490.'91.'---Total92.'93.xlsbookx.Sheets(LastSheet).Cel