表格模板白程序 VB源码

合集下载

excel vba常用代码

excel vba常用代码Excel VBA是一种用于编写宏的编程语言，它可以帮助我们在Excel 中进行自动化操作。

在VBA中，有一些常用的代码，可以帮助我们快速完成一些常见的任务。

本文将介绍一些常用的Excel VBA代码，帮助读者更好地理解并运用它们。

一、数据处理1.1 数据筛选在Excel中，我们经常需要根据某些条件筛选数据。

使用VBA可以实现自动筛选，代码如下：```ActiveSheet.Range("A1:D10").AutoFilter Field:=1, Criteria1:=">10"```以上代码将自动筛选出范围为A1:D10的数据，其中第一列大于10的数据。

1.2 数据排序有时候，我们需要对数据进行排序。

使用VBA可以实现自动排序，代码如下：```ActiveSheet.Range("A1:D10").Sort Key1:=Range("A1"), Order1:=xlAscending```以上代码将自动对范围为A1:D10的数据根据第一列进行升序排序。

1.3 数据透视表数据透视表可以帮助我们对数据进行汇总和分析。

使用VBA可以自动生成数据透视表，代码如下：```ActiveSheet.PivotTableWizard```以上代码将自动生成一个数据透视表。

二、单元格操作2.1 单元格赋值在VBA中，我们可以使用代码将某个值赋给指定的单元格，代码如下：```Range("A1").Value = "Hello World"```以上代码将把"Hello World"赋值给A1单元格。

2.2 单元格格式设置使用VBA可以设置单元格的格式，例如设置字体、颜色、边框等，代码如下：```Range("A1").Font.Bold = TrueRange("A1").Interior.Color = RGB(255, 0, 0)Range("A1").Borders.LineStyle = xlContinuous```以上代码将设置A1单元格的字体为粗体、背景色为红色、边框为实线。

vba精典源程序源代码(工作表操作)

表格 12 1 数据有效性类型
12-2 判断单元格是否存在数据有效性
在VBA中没有专门的属性判断单元格是否存在数据有效性设置，可以使用Validation对象的有效性类型和错误陷阱来判断，如下面的代码所示。
#001 Sub Validation()
#002 On Error GoTo Line
#005 AlertStyle:=xlValidAlertStop, _
#006 Operator:=xlBetween, _
#007 Formula1:="1,2,3,4,5,6,7,8"
#008 End With
#016 Select Case Target
#017 Case "主机"
#018 .Add Type:=xlValidateList, _
#019 AlertStyle:=xlValidAlertStop, _
参数expression是可选的，该表达式返回一个Application对象。
参数Keys是必需的，要发送的键或者组合键，以文本方式表示。
Keys参数可以指定任何单个键或与Alt、Ctrl 或Shift的组合键（或者这些键的组合）。每个键可用一个或多个字符表示。例如，"a" 表示字符 a，或者 "{ENTER}" 表示 Enter。
若要指定在按相应键时不会显示的字符（例如，Enter 或 Tab），请使用如表格 12 2所列的代码来表示相应的键，表中的每个代码表示键盘上的一个键。
键代码
Backspace {BACKSPACE} 或 {BS}
#027 End Select
#028 End With

VB操作Excel报表(实例、图例、源码、注释)

VB操作Excel报表（实例、图例、源码、注释）用Excel报表非常方便，而用VB操作Excel更能延长VB的功能范围。

对于重复生成的表格更是非常方便。

字体，行高，列宽，合并单元格，排版等功能都有。

供大家学习研究。

VB中使用Excel控件要先引用哦。

要不你错都不知道哪错了。

郁闷吧！一、报表预览如下1、无内容报表如下：2、填表后预览如下：二、界面预览如下：打印记录按钮为Command3三、源码如下：Private Sub Command3_Click()声明一个新的Excel对象Set winkexcel = New Excel.Application该对象可见winkexcel.Visible = True选择第一张表为操作表winkexcel.SheetsInNewWorkbook = 1表添加内容Set winkworkbook = winkexcel.Workbooks.Add 设置指定单元格行高列宽Set xlsheet = winkworkbook.Worksheets(1) xlsheet.Rows(1).RowHeight = 45xlsheet.Rows(2).RowHeight = 33xlsheet.Rows(3).RowHeight = 33xlsheet.Rows(4).RowHeight = 33xlsheet.Rows(5).RowHeight = 33xlsheet.Rows(6).RowHeight = 300 xlsheet.Rows(7).RowHeight = 100 xlsheet.Rows(8).RowHeight = 45xlsheet.Rows(9).RowHeight = 45xlsheet.Rows(10).RowHeight = 26 xlsheet.Columns(1).ColumnWidth = 14 xlsheet.Columns(2).ColumnWidth = 24 xlsheet.Columns(3).ColumnWidth = 14 xlsheet.Columns(4).ColumnWidth = 24设置表格内容的对齐方式winkexcel.ActiveSheet.Rows.HorizontalAlignment = xlVAlignCenter'水平居中winkexcel.ActiveSheet.Rows.VerticalAlignment = xlVAlignCenter'垂直居中With winkexcel.ActiveSheet.Range("A1:D1")'合并单元格.MergeEnd WithWith winkexcel.ActiveSheet.Range("B6:D6")'合并单元格.MergeEnd WithWith winkexcel.ActiveSheet.Range("B7:D7")'合并单元格.MergeEnd With画边框线，细线，黑。

使用VB做的报表源码

Public cn As New ADODB.ConnectionPublic rs2 As New ADODB.RecordsetPublic sore As BooleanDim xls As Excel.ApplicationDim xlsbook As Excel.WorkbookDim xlssheet As Excel.WorksheetDim xlsrows As Integer 'excel行数Dim rs1 As New ADODB.RecordsetPrivate Sub Combo1_DropDown()MonthView1.Visible = Truesore = 1End SubPrivate Sub Command1_Click()Dim strsql As StringIf rs2.State = 1 Thenrs2.CloseEnd Ifstrsql = "SELECT * FROM biao where aaaa = #" & Combo1.Text & "#"DataGrid1.ClearFieldsrs2.CursorLocation = adUseClientrs2.Open strsql, cn, adOpenDynamic, adLockBatchOptimisticSet DataGrid1.DataSource = rs2' DataGrid1.RefreshEnd SubPrivate Sub Command2_Click()Dim strdelect As Stringstrdelete = "delete from biao where aaaa = #" & Combo1.Text & "# and bbbb=#2:00:00#" 'strdelete = "delete from biao where ID=5"MsgBox strdeleteIf rs1.State = 1 Thenrs1.CloseEnd Ifrs1.CursorLocation = adUseClientrs1.Open strdelete, cn, adOpenDynamic, adLockBatchOptimisticEnd SubPrivate Sub DataGrid1_Click()End SubPrivate Sub Expbutton_Click() '把数据集导出到execl模板里另存为日期文件Set xls = CreateObject("Excel.Application") '创建execl对象Set xlsbook = xls.Workbooks.Open(App.Path & "\REPORT.xls") '打开已经存在的EXCEL工件簿文件Set xlssheet = xlsbook.Worksheets(2) '设置活动工作表xls.Visible = Falsexlssheet.ActivateCall Command1_ClickCall insetdateEnd SubPrivate Sub insetdate()Dim i As IntegerDim strsql As StringFor i = 0 To 23If rs1.State = 1 Thenrs1.CloseEnd Ifrs1.Open strsql, cn, adOpenDynamic, adLockBatchOptimisticIf rs1.RecordCount > 0 Thenxlssheet.Cells(5 + i, 1) = rs1("aaaa") & " " & rs1("bbbb")xlssheet.Cells(5 + i, 2) = rs1("ID")xlssheet.Cells(5 + i, 3) = rs1("TIC01")xlssheet.Cells(5 + i, 4) = rs1("TIC02")xlssheet.Cells(5 + i, 5) = rs1("TIC03")xlssheet.Cells(5 + i, 6) = rs1("TIC04")'MsgBox "ok"Elsexlssheet.Cells(5 + i, 1) = Combo1.Text & i & ":00"xlssheet.Cells(5 + i, 2) = "0"xlssheet.Cells(5 + i, 3) = "0"xlssheet.Cells(5 + i, 4) = "0"xlssheet.Cells(5 + i, 5) = "0"xlssheet.Cells(5 + i, 6) = "0"End IfNextxlssheet.SaveAs App.Path & "\report\" & Format(Date, "YYYYMMDD") & ".xls"'MsgBox App.Path & "\report\" & Format(Date, "YYYYMMDD") & ".xls"xlsbook.Closexls.QuitSet xlsbook = NothingSet xls = NothingEnd SubPrivate Sub Form_Load()cn.ConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & App.Path & "\REPORT.mdb;Persist Security Info=False;" '连接数据库cn.OpenMsgBox "连接成功"Combo1.Text = DateCombo3.Text = DateMonthView1.Visible = FalseEnd SubPrivate Sub Form_Unload(Cancel As Integer)cn.Close '关闭连接End SubPrivate Sub MonthView1_DateClick(ByVal DateClicked As Date)Dim a As StringMsgBox sorea = MonthView1.ValueMsgBox a'MsgBox MonthView1.SelStart'MsgBox MonthView1.SelEndIf sore = True ThenCombo1.Text = MonthView1.ValueMsgBox Combo1.TextEnd IfIf sore = False ThenCombo3.Text = MonthView1.ValueMsgBox Combo3.TextEnd IfMonthView1.Visible = FalseEnd Sub。

vb简单的计算机源代码

vb简单的计算机源代码.txt如果xx的时光在闲散中度过，那么回忆岁月将是一场凄凉的悲剧。

杂草多的地方庄稼少，空话多的地方xx少。

即使路上没有花朵，我仍可以欣赏荒芜。

Private Sub Command1_Click()Form1.Caption = "欢迎使用智能计算器"'载入默认正常显示If Check1.Value = "0" Then'1类分歧ElseIf Text1.Text = "" Or Text2.Text = "" Then'2类分歧Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then'2类分歧Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"Else'2类分歧Dim a, b, c As Doublea = Val(Text1.Text)b = Val(Text2.Text)c = a + bText3.Text = cText1.Text = ""Text2.Text = ""End IfIf Check1.Value = "1" Then'1类分歧ElseIf Text1.Text = "" Or Text2.Text = "" Then'2类分歧Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then'2类分歧Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"Else'2类分歧Dim d, e, f As Doubled = Val(Text1.Text)e = Val(Text2.Text)f = d + eText3.Text = fEnd IfEnd SubPrivate Sub Command2_Click()Form1.Caption = "欢迎使用智能计算器"If Check1.Value = "1" ThenElseIf Text1.Text = "" Or Text2.Text = "" ThenForm1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" ThenForm1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseDim d, e, f As Doubled = Val(Text1.Text)e = Val(Text2.Text)f = d - eText3.Text = fEnd IfIf Check1.Value = "0" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseDim a, b, c As Doublea = Val(Text1.Text)b = Val(Text2.Text)c = a - bText3.Text = cText1.Text = ""Text2.Text = ""End IfEnd SubPrivate Sub Command3_Click()Form1.Caption = "欢迎使用智能计算器"If Check1.Value = "1" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseDim d, e, f As Doubled = Val(Text1.Text)e = Val(Text2.Text)f = d * eText3.Text = fEnd IfIf Check1.Value = "0" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseDim a, b, c As Doublea = Val(Text1.Text)b = Val(Text2.Text)c = a * bText3.Text = cText1.Text = ""Text2.Text = ""End IfEnd SubPrivate Sub Command4_Click()Form1.Caption = "欢迎使用智能计算器"If Check1.Value = "0" ThenElseIf Text1.Text = "" Or Text2.Text = "" ThenForm1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Val(Text2.Text) = "0" ThenForm1.Caption = "xataliq kuruldi"Text3.Text = "分数的分子不能为零"ElseDim a, b, c As Doublea = Val(Text1.Text)b = Val(Text2.Text)c = a / bText3.Text = cText1.Text = ""Text2.Text = ""End IfIf Check1.Value = "1" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Val(Text2.Text) = "0" ThenForm1.Caption = "xataliq kuruldi"Text3.Text = "分数的分子不能为零"ElseDim d, e, f As Doubled = Val(Text1.Text)e = Val(Text2.Text)f = d / eText3.Text = fEnd IfEnd SubPrivate Sub Command5_Click()Form1.Caption = "欢迎使用智能计算器" Text1.Text = ""Text2.Text = ""Text3.Text = ""End SubPrivate Sub Command6_Click()Form1.HideForm3.ShowEnd SubPrivate Sub Command7_Click()EndEnd SubPrivate Sub Command8_Click()Form1.Caption = "欢迎使用智能计算器" If Text3.Text <> "" ThenText1.Text = Text3.TextText2.Text = ""Text3.Text = ""ElseForm1.Caption = "xataliq kuruldi"Text3.Text = "没有结果无法继续"End IfEnd SubPrivate Sub Text2_Change()End SubPrivate Sub 乘_Click()If Check1.Value = "1" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseDim d, e, f As Doubled = Val(Text1.Text)e = Val(Text2.Text)f = d * eText3.Text = fEnd IfIf Check1.Value = "0" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseDim a, b, c As Doublea = Val(Text1.Text)b = Val(Text2.Text)c = a * bText3.Text = cText1.Text = ""Text2.Text = ""End IfEnd SubPrivate Sub 除_Click()Form1.Caption = "欢迎使用智能计算器"If Check1.Value = "0" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Val(Text2.Text) = "0" ThenForm1.Caption = "xataliq kuruldi"Text3.Text = "分数的分子不能为零"ElseDim a, b, c As Doublea = Val(Text1.Text)b = Val(Text2.Text)c = a / bText3.Text = cText1.Text = ""Text2.Text = ""End IfIf Check1.Value = "1" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "错误"Text3.Text = "运算数值不能为空"ElseIf Val(Text2.Text) = "0" ThenForm1.Caption = "错误"Text3.Text = "分数的分子不能为零"ElseDim d, e, f As Doubled = Val(Text1.Text)e = Val(Text2.Text)f = d / eText3.Text = fEnd IfEnd SubPrivate Sub munasiwetlik_Click()Form1.Caption = "欢迎使用智能计算器" Form1.HideForm3.ShowEnd SubPrivate Sub 继续_Click()Form1.Caption = "欢迎使用智能计算器" If Text3.Text <> "" ThenText1.Text = Text3.TextText2.Text = ""Text3.Text = ""ElseForm1.Caption = "xataliq"Text3.Text = "没有结果无法继续"End IfEnd SubPrivate Sub 加_Click()Form1.Caption = "欢迎使用智能计算器"If Check1.Value = "0" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "错误"Text3.Text = "运算数值不能为空"ElseDim a, b, c As Doublea = Val(Text1.Text)b = Val(Text2.Text)c = a + bText3.Text = cText1.Text = ""Text2.Text = ""End IfIf Check1.Value = "1" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseDim d, e, f As Doubled = Val(Text1.Text)e = Val(Text2.Text)f = d + eText3.Text = fEnd IfEnd SubPrivate Sub 减_Click()Form1.Caption = "欢迎使用智能计算器"If Check1.Value = "1" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "xataliq kuruldi"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "错误"Text3.Text = "运算数值不能为空"ElseDim d, e, f As Doubled = Val(Text1.Text)e = Val(Text2.Text)f = d - eText3.Text = fEnd IfIf Check1.Value = "0" ThenElseIf Text1.Text = "" Or Text2.Text = "" Then Form1.Caption = "错误"Text3.Text = "运算数值不能为空"ElseIf Text1.Text = "" And Text2.Text = "" Then Form1.Caption = "错误"Text3.Text = "运算数值不能为空"ElseDim a, b, c As Doublea = Val(Text1.Text)b = Val(Text2.Text)Text3.Text = cText1.Text = ""Text2.Text = ""End IfEnd SubPrivate Sub 清空_Click()Form1.Caption = "欢迎使用智能计算器" Text1.Text = ""Text2.Text = ""Text3.Text = ""End Sub----------------------------------------------form2Private Sub Command1_Click()Form3.HideForm1.ShowForm1.Text3.Text = ""End SubPrivate Sub Command2_Click()End。

vba编程代码大全

vba编程代码大全VBA编程代码大全。

VBA（Visual Basic for Applications）是一种用于应用程序开发的编程语言，它可以帮助用户在Microsoft Office软件中自动化任务，提高工作效率。

在本文中，我们将为您介绍一些常用的VBA编程代码，帮助您更好地利用VBA来处理各种任务。

首先，让我们来看一些常用的VBA基础操作代码。

在VBA中，您可以使用MsgBox函数来显示消息框，例如：```vba。

MsgBox "Hello, World!"```。

这段代码将会在屏幕上显示一个包含"Hello, World!"的消息框。

除了MsgBox 函数，VBA还提供了InputBox函数来获取用户输入的数值或文本：```vba。

Dim userInput As String。

userInput = InputBox("Please enter your name:")。

```。

这段代码将会弹出一个输入框，等待用户输入姓名，并将用户输入的内容存储在userInput变量中。

接下来，让我们来看一些与Excel相关的VBA代码。

在Excel中，VBA可以帮助您自动化各种数据处理任务。

例如，您可以使用VBA来创建新的工作表，并向其中填充数据：```vba。

Dim ws As Worksheet。

Set ws = ThisWorkbook.Sheets.Add。

= "NewSheet"ws.Range("A1").Value = "Hello"```。

这段代码将会在当前工作簿中创建一个名为"NewSheet"的新工作表，并在A1单元格中填入"Hello"。

除了操作工作表，VBA还可以帮助您处理Excel中的图表。

例如，您可以使用VBA来创建新的图表，并向其中添加数据：```vba。

vb excel编程实例

vb excel编程实例
在VBExcel编程中创建一个计算器是一个非常简单的任务，下面将介绍一个基本的示例，帮助你了解如何实现这个任务。

步骤1：创建一个新的工作簿
在VB Excel中创建一个新的工作簿，然后在工作表中选择单元格A1，输入“数字1”，在单元格B1中输入“数字2”，在单元格C1中输入“结果”。

步骤2：创建表单按钮
在工具箱中选择表单按钮，然后在工作表中绘制一个按钮。

在按钮上单击鼠标右键，选择“编辑文本”，在文本框中输入“加法”。

然后单击“确定”按钮。

步骤3：编写代码
在VB编辑器中打开工作簿，然后在模块中添加以下代码：
Sub Addition()
Dim num1 As Integer
Dim num2 As Integer
Dim result As Integer
num1 = Range('A1').Value
num2 = Range('B1').Value
result = num1 + num2
Range('C1').Value = result
End Sub
步骤4：将代码与按钮关联
将按钮与代码关联，使得当用户单击按钮时，代码会自动运行。

在VB编辑器中，双击按钮，将其与代码相关联。

步骤5：测试功能
在单元格A1中输入数字1，在单元格B1中输入数字2，然后单击“加法”按钮。

结果应显示在单元格C1中。

总结
这个简单的VB Excel编程实例说明了如何创建一个简单的计算器。

通过编写代码和将按钮与代码关联，你可以轻松地创建你自己的Excel计算器。

Excel VBA代码亲测可用

1、打开显示登录窗体代码打开隐藏表格，显示登录窗体private Sub Workbook_open()Application.Visible = falseUserForm1.Showend Sub2、固定账号、密码登录窗体设置（1）制作窗体（2）登录验证Private Sub CommandButton1_Click() If TextBox1 = "admin" ThenIf TextBox2 <> 123 ThenMsgBox "密码错误"Exit SubElseMsgBox "登录成功”"Unload MeApplication.Visible = TrueSheet1.ActivateEnd IfElseMsgBox "账号不存在"End IfEnd Sub（3）退出按钮Private Sub CommandButton2_Click() Unload MeThisWorkbook.CloseEnd Sub(4)打开注册窗体Private Sub CommandButton3_Click() UserForm2.ShowEnd Sub（5）唯一关闭代码Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode <> 1 Then Cancel = TrueEnd Sub3、注册账号（1）制作注册账号窗体（2）注册代码Private Sub CommandButton1_Click()Dim zh As Range, zt As RangeIf TextBox1 = "" Then MsgBox "未填入账户": Exit SubIf TextBox2 <> TextBox3 Then MsgBox "密码不一致": Exit SubSet zh = Sheets("注册").Range("a:a").Find(TextBox1.Text, , , 1)If zh Is Nothing ThenSet zt = Sheets("注册").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) zt = TextBox1.Textzt.Offset(0, 1) = TextBox2.Textzt.Offset(0, 2) = NowMsgBox "注册成功"Unload MeElseMsgBox "账号已经存在，请更换其他账号"End IfEnd Sub4、查找筛选代码Private Sub TextBox1_Change()If Len(TextBox1.Value) = 0 ThenSheet1.AutoFilterMode = FalseElseIf Sheet1.AutoFilterMode = True ThenSheet1.AutoFilterMode = FalseEnd IfSheet1.Range("B7:P" & Rows.Count).AutoFilter _field:=4, Criteria1:="*" & TextBox1.Value & "*"End IfEnd Sub5、多账号密码验证代码Private Sub CommandButton1_Click()If Len(TextBox1) = 0 Then MsgBox "未输入账号": Exit SubDim zh As RangeSet zh = Sheets("注册").Range("a:a").Find(TextBox1.Text, , , 1) If Not zh Is Nothing ThenIf TextBox2.Text <> zh.Offset(0, 1) ThenMsgBox "密码错误"Exit SubElseMsgBox "登录成功"Unload MeApplication.Visible = TrueSheet1.ActivateEnd IfElseMsgBox "账号不存在"End IfEnd Sub6、默认打开第一个工作表Private Sub Workbook_Open()Sheet1.ActivateEnd Sub7、退出保存工作表Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.SaveEnd Sub。

VBnet操做Excel的代码

Private Sub writeToExcel(strTmp1() As String, colTmp1 As Collection)'' Dim tmp1Dim i1 As Integer, intCol As Integer, intRow As IntegerDim xlApp As New Excel.ApplicationDim xlBook As New Excel.WorkbookDim xlSheet As New Excel.WorksheetDim strName As String, strArray1() As StringDim strS1 As StringDim strD1 As StringstrS1 = CurrentProject.Path + "\template.xls"strD1 = CurrentProject.Path + "\" + CStr(Format(Now, "YYYYMMDDHHMMSS")) + "aaa1.xls"' For i1 = 0 To UBound(strTmp1) - 1' Debug.Print strTmp1(i1) + " " + CStr(i1)' Next i1' strName = CurrentProject.Path + "\aaa1.xls"FileCopy strS1, strD1Set xlApp = CreateObject("Excel.Application")xlApp.Visible = False' Set xlBook = xlApp.Workbooks.Open(strName)Set xlBook = xlApp.Workbooks.Open(strD1)Set xlSheet = xlBook.Worksheets(1)With xlSheet.Range("F6").Value = strTmp1(1).Range("H6").Value = strTmp1(2).Range("F7").Value = CStr(Date).Range("E10").Value = strTmp1(9).Range("A15").Value = "To: " + strTmp1(8).Range("B26").Value = strTmp1(4) + "PACKAGES".Range("B27").Value = strTmp1(5) + "KGS".Range("B28").Value = strTmp1(6) + "KGS".Range("B29").Value = strTmp1(7) + "M3"End WithintCol = 1intRow = 21For i1 = 1 To colTmp1.CountstrArray1 = colTmp1.Item(i1)With xlSheet.Cells(intRow, 1).Value = strArray1(2).Cells(intRow, 2).Value = strArray1(5).Cells(intRow, 4).Value = strArray1(6).Cells(intRow, 5).Value = strArray1(1).Cells(intRow, 6).Value = strArray1(3).Cells(intRow, 7).Value = strArray1(4).Cells(intRow, 8).Value = strArray1(7).Cells(intRow, 9).Value = strArray1(9)intRow = intRow + 1xlApp.ActiveSheet.Rows(intRow).Insert.Cells(intRow, 1).Value = strArray1(8)intRow = intRow + 1xlApp.ActiveSheet.Rows(intRow).InsertEnd WithintRow = intRow + 1xlApp.ActiveSheet.Rows(intRow).InsertNext i1xlApp.Visible = TruexlBook.Save' xlBook.CloseSet xlSheet = NothingSet xlBook = Nothing' xlApp.Quit' tmp1 = Shell(strName, 1)' hWndDesk = GetDesktopWindow()' r = ShellExecute(hWndDesk, "Open", strName, vbNullString, 0&, 1) End SubDim xlApp As New Excel.ApplicationDim xlBook As New Excel.WorkbookDim xlSheet As New Excel.WorksheetPublic Sub exportExcel()'Dim strA1() As String, strA2() As String, strTmp1 As String, strDATE As String, strName As String, strValue As StringDim intFieldLength As Integer, i1 As Integer, i2 As Integer, lngCount As LongDim rs1 As DAO.RecordsetstrTmp1 = "A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1,P1,Q1,R1,S1,T1,U1,V1,W1,X1,Y1,Z1,AA1,AB1,AC1,AD1,AE1,AF1,A G1,AH1,AI1,AJ1,AK1,AL1,AM1,AN1,AO1,AP1,AQ1,AR1,AS1,AT1,AU1,AV1,AW1,AX1,AY1,AZ1,BA1,BB1,BC1,BD1,BE1 ,BF1,BG1,BH1,BI1,BJ1,BK1,BL1,BM1,BN1,BO1,BP1,BQ1,BR1,BS1,BT1,BU1,BV1,BW1,BX1,BY1,BZ1,CA1,CB1,CC1,CD1 ,CE1,CF1,CG1,CH1,CI1,CJ1,CK1,CL1,CM1,CN1,CO1,CP1,CQ1,CR1,CS1,CT1,CU1,CV1,CW1,CX1,CY1,CZ1"strA1 = Split(strTmp1, ",")Set xlApp = CreateObject("Excel.Application")xlApp.Visible = FalseSet xlBook = xlApp.Workbooks.AddstrDATE = CStr(Format(Date, "YYYY-MM-DD"))monDialog1.DefaultExt = "xls"monDialog1.Filename = "帐单输出" + strDATE + ".xls"monDialog1.Filter = "EXCEL FILE(*.xls)|*.xls"monDialog1.ShowSavestrName = monDialog1.FilenamexlBook.SaveAs strNameSet xlBook = xlApp.Workbooks.Open(strName)Set xlSheet = xlBook.Worksheets(1)strSQL = "SELECT * FROM HEADCOST1; "Set rs1 = CurrentDb.OpenRecordset(strSQL)rs1.MoveLastDebug.Print rs1.RecordCountlngCount = rs1.RecordCountintFieldLength = rs1.Fields.Count' Debug.Print intFieldLengthDebug.Print intFieldLengthstrA2() = Split(splitTable("HEADCOST1"), ",")Debug.Print UBound(strA2)With xlSheetFor i1 = 0 To intFieldLength - 1Debug.Print i1Debug.Print strA1(i1).Range(strA1(i1)).Value = getZValue(strA2(i1)) Next i1End WithIf rs1.RecordCount <> 0 Thenrs1.MoveFirstFor i1 = 1 To lngCountFor i2 = 1 To rs1.Fields.CountIf IsNull(rs1(i2 - 1)) ThenstrValue = " "ElsestrValue = rs1(i2 - 1).ValueEnd IfxlSheet.Cells(i1 + 1, i2) = strValueNext i2rs1.MoveNextNext i1rs1.MoveFirstElseMsgBox "未读取到数据", vbCritical, "错误"End IfxlBook.SavexlBook.CloseSet xlSheet = NothingSet xlBook = NothingxlApp.QuitSet xlApp = Nothingrs1.CloseSet rs1 = NothingEnd SubPrivate Sub Command1_Click()Set xlApp = CreateObject("Excel.Application")xlApp.Visible = FalseSet xlBook = xlApp.Workbooks.AddDim strDate As String, strName As String, strValue As String strDate = CStr(Format(Date, "yyyy-mm-dd"))monDialog1.DefaultExt = "xls"monDialog1.FileName = "SEND3B2" + strDate + ".xls"monDialog1.Filter = "EXCEL FILE(*.xls)|*.xls"monDialog1.ShowSavestrName = monDialog1.FileNameDebug.Print strNamexlBook.SaveAs strNameSet xlBook = xlApp.Workbooks.Open(strName)Set xlSheet = xlBook.Worksheets(1)' For i1 = 0 To Me.DataGrid1.Columns.Count - 1' xlSheet.Cells(1, i1 + 1) = Me.DataGrid1.Columns.Item(j).Caption ' Next i1With xlSheet.Range("A1").Value = "ORDERKEY".Range("B1").Value = "EXTERNORDERKEY".Range("C1").Value = "MM".Range("D1").Value = "QTY".Range("E1").Value = "PRODUCTDESP".Range("F1").Value = "DIVISION".Range("G1").Value = "MOQ".Range("H1").Value = "OVERPACKQTY".Range("I1").Value = "OVERPACK ?".Range("J1").Value = "CTNQTY".Range("K1").Value = "OPCTNQTY".Range("L1").Value = "CTN_PALLET".Range("M1").Value = "PALLETNO".Range("N1").Value = "PALLETWEIGHT".Range("O1").Value = "PALLETVOLUME".Range("P1").Value = "PALLETLENGTH".Range("Q1").Value = "PALLETWIDTH".Range("R1").Value = "PALLETHIGH".Range("S1").Value = "DELIVERYDATE".Range("T1").Value = "CONSIGNEEKEY".Range("U1").Value = "C_COUNTRY".Range("V1").Value = "BILLTOKEY".Range("W1").Value = "INCOTERM".Range("X1").Value = "STATUS".Range("Y1").Value = "INTERMODALVEHICLE".Range("Z1").Value = "ORDERGROUP".Range("AA1").Value = "HAWB".Range("AB1").Value = "REQSHIPDATE".Range("AC1").Value = "RELEASEDDATE".Range("AD1").Value = "C_COMPANY"End WithIf Me.Adodc1.Recordset.RecordCount <> 0 ThenMe.Adodc1.Recordset.MoveFirstFor i1 = 1 To Me.Adodc1.Recordset.RecordCountFor i2 = 1 To Me.Adodc1.Recordset.Fields.CountIf IsNull(Me.Adodc1.Recordset.Fields(i2 - 1)) Then strValue = " "ElsestrValue = Me.Adodc1.Recordset.Fields(i2 - 1).Value ': Debug.Print strValue End IfxlSheet.Cells(i1 + 1, i2) = strValueNext i2Me.Adodc1.Recordset.MoveNextNext i1Me.Adodc1.Recordset.MoveFirstElseMsgBox "请先查询数据", vbCritical, "错误"End IfxlBook.SavexlBook.CloseSet xlSheet = NothingSet xlBook = Nothing' xlApp.Visible = TruexlApp.QuitSet xlApp = NothingEnd Sub用VB操作Excel(VB6.0)(整理)首先创建Excel对象，使用ComObj:Dim ExcelID as Excel.ApplicationSet ExcelID as new Excel.Application1)显示当前窗口：ExcelID.Visible:=True;2)更改Excel标题栏：ExcelID.Caption:='应用程序调用MicrosoftExcel';3)添加新工作簿：ExcelID.WorkBooks.Add;4)打开已存在的工作簿：ExcelID.WorkBooks.Open('C:\Excel\Demo.xls');5)设置第2个工作表为活动工作表：ExcelID.WorkSheets[2].Activate;或ExcelID.WorkSheets['Sheet2'].Activate;6)给单元格赋值：ExcelID.Cells[1,4].Value:='第一行第四列';7)设置指定列的宽度（单位：字符个数），以第一列为例：ExcelID.ActiveSheet.Columns[1].ColumnsWidth:=5;8)设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelID.ActiveSheet.Rows[2].RowHeight:=1/0.035;//1厘米9)在第8行之前插入分页符：ExcelID.WorkSheets[1].Rows[8].PageBreak:=1;10)在第8列之前删除分页符：ExcelID.ActiveSheet.Columns[4].PageBreak:=0;11)指定边框线宽度：ExcelID.ActiveSheet.Range['B3:D4'].Borders[2].Weight:=3;1-左2-右3-顶4-底5-斜(\) 6-斜(/)12)清除第一行第四列单元格公式：ExcelID.ActiveSheet.Cells[1,4].ClearContents;13)设置第一行字体属性：ExcelID.ActiveSheet.Rows[1]:='隶书';ExcelID.ActiveSheet.Rows[1].Font.Color :=clBlue;ExcelID.ActiveSheet.Rows[1].Font.Bold :=True;ExcelID.ActiveSheet.Rows[1].Font.UnderLine:=True;14)进行页面设置：a.页眉：ExcelID.ActiveSheet.PageSetup.CenterHeader:='报表演示';b.页脚：ExcelID.ActiveSheet.PageSetup.CenterFooter:='第&P页';c.页眉到顶端边距2cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin:=2/0.035;d.页脚到底端边距3cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin:=3/0.035; e.顶边距2cm：ExcelID.ActiveSheet.PageSetup.TopMargin:=2/0.035;f.底边距2cm：ExcelID.ActiveSheet.PageSetup.BottomMargin:=2/0.035; g.左边距2cm：ExcelID.ActiveSheet.PageSetup.LeftMargin:=2/0.035;h.右边距2cm：ExcelID.ActiveSheet.PageSetup.RightMargin:=2/0.035;i.页面水平居中：ExcelID.ActiveSheet.PageSetup.CenterHorizontally:=2/0.035; j.页面垂直居中：ExcelID.ActiveSheet.PageSetup.CenterVertically:=2/0.035; k.打印单元格网线：ExcelID.ActiveSheet.PageSetup.PrintGridLines:=True;15)拷贝操作：a.拷贝整个工作表：ed.Range.Copy;b.拷贝指定区域：ExcelID.ActiveSheet.Range['A1:E2'].Copy;c.从A1位置开始粘贴：ExcelID.ActiveSheet.Range.['A1'].PasteSpecial;d.从文件尾部开始粘贴：ExcelID.ActiveSheet.Range.PasteSpecial;16)插入一行或一列：a.ExcelID.ActiveSheet.Rows[2].Insert;b.ExcelID.ActiveSheet.Columns[1].Insert;17)删除一行或一列：a.ExcelID.ActiveSheet.Rows[2].Delete;b.ExcelID.ActiveSheet.Columns[1].Delete;18)打印预览工作表：ExcelID.ActiveSheet.PrintPreview;19)打印输出工作表：ExcelID.ActiveSheet.PrintOut;20)工作表保存：IfnotExcelID.ActiveWorkBook.SavedthenExcelID.ActiveSheet.PrintPreviewEndif21)工作表另存为：ExcelID.SaveAs('C:\Excel\Demo1.xls');22)放弃存盘：ExcelID.ActiveWorkBook.Saved:=True;23)关闭工作簿：ExcelID.WorkBooks.Close;24)退出Excel：ExcelID.Quit;25)设置工作表密码:ExcelID.ActiveSheet.Protect"123",DrawingObjects:=True,Contents:=True,Scenarios:=True 26)EXCEL的显示方式为最大化ExcelID.Application.WindowState=xlMaximized27)工作薄显示方式为最大化ExcelID.ActiveWindow.WindowState=xlMaximized28)设置打开默认工作薄数量ExcelID.SheetsInNewWorkbook=329)'关闭时是否提示保存(true保存；false不保存)ExcelID.DisplayAlerts=False30)设置拆分窗口,及固定行位置ExcelID.ActiveWindow.SplitRow=1ExcelID.ActiveWindow.FreezePanes=True31)设置打印时固定打印内容ExcelID.ActiveSheet.PageSetup.PrintTitleRows="$1:$1"32)设置打印标题ExcelID.ActiveSheet.PageSetup.PrintTitleColumns=""33)设置显示方式(分页方式显示)ExcelID.ActiveWindow.View=xlPageBreakPreview34)设置显示比例ExcelID.ActiveWindow.Zoom=10035)让Excel响应DDE请求Ex.Application.IgnoreRemoteRequests=False用VB操作EXCEL示例代码Private Sub Command3_Click()On Error GoTo err1Dim i As LongDim j As LongDim objExl As Excel.Application '声明对象变量Me.MousePointer=11 '改变鼠标样式Set objExl=New Excel.Application'初始化对象变量objExl.SheetsInNewWorkbook=1 '将新建的工作薄数量设为1objExl.Workbooks.Add'增加一个工作薄objExl.Sheets(objExl.Sheets.Count).Name="book1" '修改工作薄名称objExl.Sheets.Add,objExl.Sheets("book1")‘增加第二个工作薄在第一个之后objExl.Sheets(objExl.Sheets.Count).Name="book2"objExl.Sheets.Add,objExl.Sheets("book2")‘增加第三个工作薄在第二个之后objExl.Sheets(objExl.Sheets.Count).Name="book3"objExl.Sheets("book1").Select '选中工作薄<book1>For i=1 To 50'循环写入数据For j=1 To 5If i=1 ThenobjExl.Selection.NumberFormatLocal="@" '设置格式为文本objExl.Cells(i,j)="E"&i&jElseobjExl.Cells(i,j)=i&jEndIfNextNextobjExl.Rows("1:1").Select '选中第一行objExl.Selection.Font.Bold=True '设为粗体objExl.Selection.Font.Size=24 '设置字体大小objExl.Cells.EntireColumn.AutoFit '自动调整列宽objExl.ActiveWindow.SplitRow=1 '拆分第一行objExl.ActiveWindow.SplitColumn=0 '拆分列objExl.ActiveWindow.FreezePanes=True '固定拆分objExl.ActiveSheet.PageSetup.PrintTitleRows="$1:$1" '设置打印固定行objExl.ActiveSheet.PageSetup.PrintTitleColumns=""'打印标题objExl.ActiveSheet.PageSetup.RightFooter="打印时间:"&_Format(Now,"yyyy年mm月dd日hh:MM:ss")objExl.ActiveWindow.View=xlPageBreakPreview'设置显示方式objExl.ActiveWindow.Zoom=100 '设置显示大小'给工作表加密码objExl.ActiveSheet.Protect"123",DrawingObjects:=True, _Contents:=True,Scenarios:=TrueobjExl.Application.IgnoreRemoteRequests=FalseobjExl.Visible=True '使EXCEL可见objExl.Application.WindowState=xlMaximized'EXCEL的显示方式为最大化objExl.ActiveWindow.WindowState=xlMaximized'工作薄显示方式为最大化objExl.SheetsInNewWorkbook=3 '将默认新工作薄数量改回3个Set objExl=Nothing'清除对象Me.MousePointer=0 '修改鼠标ExitSuberr1:objExl.SheetsInNewWorkbook=3objExl.DisplayAlerts=False '关闭时不提示保存objExl.Quit'关闭EXCELobjExl.DisplayAlerts=True '关闭时提示保存Set objExl=NothingMe.MousePointer=0End SubDim excelfile As Excel.Application, excelwbook As Excel.Workbook, excelsheet As Excel.WorksheetPrivate Sub ImportExcelData()'On Error GoTo Err_ImportExcelDataDim strFile As StringDim strB1() As String, intTmp1 As IntegerDoCmd.RunSQL "DELETE * FROM APTmp "monDialog8.ShowOpenstrFile = monDialog8.FilenameDebug.Print strFileIf strFile = "" ThenMsgBox "没有选择文件", vbCritical, "错误"Exit SubEnd IfSet excelfile = New Excel.ApplicationSet excelwbook = excelfile.Workbooks.Open(strFile)Set excelsheet = excelwbook.Sheets(1)lastCol = edRange.Columns.CountlastRow = edRange.Rows.CountDebug.Print lastColDebug.Print lastRowDebug.Print excelsheet.Cells(1, 1)strB1 = Split(strFile, "\")intTmp1 = UBound(strB1)strFile = strB1(intTmp1)Debug.Print strFile' If checkFileName(strFile) = True Then' MsgBox "此文件名已经导入过,不可再导入", vbCritical, "错误"' Exit Sub' End IfIf intChange = 2 ThenCall ImportAPData2(strFile)ElseCall ImportAPData(strFile)End Ifexcelwbook.Closeexcelfile.QuitSet excelfile = NothingSet excelwbook = NothingMsgBox "EXCEL数据导入完成", , "提示"Exit_ImportExcelData:Exit SubErr_ImportExcelData:MsgBox Err.DescriptionResume Exit_ImportExcelDataEndPrivate Sub ImportAPData(strTmp1 As String)'Dim i2 As Long, strTmp2 As String, boolTmp1 As BooleanFor i2 = 2 To lastRowDebug.Print excelsheet.Cells(i2, 7)If checkDN(Trim(CStr(excelsheet.Cells(i2, 7))), "APT") = True ThenIf checkRoute(Trim(CStr(excelsheet.Cells(i2, 5)))) = False ThenstrTmp2 = Trim(CStr(excelsheet.Cells(i2, 1)))boolTmp1 = TrueElsestrTmp2 = "WBLP"GoTo LOOP1End IfIf checkR8(Trim(CStr(excelsheet.Cells(i2, 8)))) = 1 Then GoTo LOOP1' 1 2 3 4 5 6 7 8 9strSQL = "INSERT INTO APTmp ( OrderType, CreateDate, GIdate, ShipTo, Route, OriginDoc, DeliveryNum, LOCATION, HAWB ) "' strSQL = strSQL + "VALUES('" + Trim(CStr(excelsheet.Cells(i2, 1))) + "',"strSQL = strSQL + "VALUES('" + strTmp2 + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 2))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 3))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 4))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 5))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 6))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 7))) + "', "' If checkRoute(Trim(CStr(excelsheet.Cells(i2, 5)))) = False Then' strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 8))) + "') "' Else' strSQL = strSQL + "'" + addR8TSHAWB + "')"'' End If' strSQL = strSQL + "'" + strTmp1 + "'" + ") "If Trim(CStr(excelsheet.Cells(i2, 9))) = "" ThenstrSQL = strSQL + "'" + "R811" + "', "ElsestrSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 9))) + "', "End IfIf boolTmp1 = True ThenstrSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 8))) + "') "boolTmp1 = FalseElsestrSQL = strSQL + "'" + addR8TSHAWB + "')"boolTmp1 = FalseGoTo LOOP1End IfDebug.Print strSQLDoCmd.RunSQL strSQLLOOP1:strTmp2 = ""boolTmp1 = FalseEnd IfNext i2Call ImportTAPDataEnd Sub'INSERT INTO APTmp ( OrderType, CreateDate, GIdate, ShipTo, Route, OriginDoc, DeliveryNum, HAWB ) 'VALUES('1','1','1','1','1','1','1','1')Private Sub ImportAPData2(strTmp1 As String)'Dim i2 As Long, strTmp2 As String, boolTmp1 As BooleanFor i2 = 2 To lastRowDebug.Print excelsheet.Cells(i2, 10): Debug.Print excelsheet.Cells(i2, 7)If checkDN(Trim(CStr(excelsheet.Cells(i2, 10))), "APT") = True ThenIf checkRoute(Trim(CStr(excelsheet.Cells(i2, 5)))) = False ThenstrTmp2 = Trim(CStr(excelsheet.Cells(i2, 1)))boolTmp1 = TrueElsestrTmp2 = "WBLP"GoTo LOOP1End IfIf checkR8(Trim(CStr(excelsheet.Cells(i2, 12)))) = 1 Then GoTo LOOP1' 1 2 3 4 5 6 7 8 9strSQL = "INSERT INTO APTmp ( OrderType, CreateDate, GIdate, ShipTo, Route, OriginDoc, DeliveryNum, LOCATION, HAWB ) "' strSQL = strSQL + "VALUES('" + Trim(CStr(excelsheet.Cells(i2, 1))) + "'," 2012-9-7 修改添加WBLP条款strSQL = strSQL + "VALUES('" + strTmp2 + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 3))) + "',"' strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 5))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 4))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 6))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 7))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 8))) + "',"strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 10))) + "', "' If checkRoute(Trim(CStr(excelsheet.Cells(i2, 5)))) = False Then' strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 8))) + "') "' Else' strSQL = strSQL + "'" + addR8TSHAWB + "')"'' End If' strSQL = strSQL + "'" + strTmp1 + "'" + ") "' If Trim(CStr(excelsheet.Cells(i2, 9))) = "" ThenstrSQL = strSQL + "'" + "R811" + "', "' Else' strSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 9))) + "', "' End IfIf boolTmp1 = True ThenstrSQL = strSQL + "'" + Trim(CStr(excelsheet.Cells(i2, 12))) + "') "boolTmp1 = FalseElsestrSQL = strSQL + "'" + addR8TSHAWB + "')"boolTmp1 = FalseGoTo LOOP1End IfDebug.Print strSQLDoCmd.RunSQL strSQLLOOP1:strTmp2 = ""boolTmp1 = FalseEnd IfNext i2Call ImportTAPDataEnd SubPrivate Sub Command3_Click()On Error GoTo err1Dim i As LongDim j As LongDim objExl As Excel.Application '声明对象变量Me.MousePointer = 11 '改变鼠标样式Set objExl = New Excel.Application '初始化对象变量objExl.SheetsInNewWorkbook = 1 '将新建的工作薄数量设为1objExl.Workbooks.Add '增加一个工作薄objExl.Sheets(objExl.Sheets.Count).Name = "book1" '修改工作薄名称objExl.Sheets.Add , objExl.Sheets("book1") '增加第二个工作薄在第一个之后objExl.Sheets(objExl.Sheets.Count).Name = "book2"objExl.Sheets.Add , objExl.Sheets("book2") '增加第三个工作薄在第二个之后objExl.Sheets(objExl.Sheets.Count).Name = "book3"objExl.Sheets("book1").Select '选中工作薄<book1>For i = 1 To 50 '循环写入数据For j = 1 To 5If i = 1 ThenobjExl.Selection.NumberFormatLocal = "@" '设置格式为文本objExl.Cells(i, j) = " E " & i & jElseobjExl.Cells(i, j) = i & jEnd IfNextNextobjExl.Rows("1:1").Select '选中第一行objExl.Selection.Font.Bold = True '设为粗体objExl.Selection.Font.Size = 24 '设置字体大小objExl.Cells.EntireColumn.AutoFit '自动调整列宽objExl.ActiveWindow.SplitRow = 1 '拆分第一行objExl.ActiveWindow.SplitColumn = 0 '拆分列objExl.ActiveWindow.FreezePanes = True '固定拆分objExl.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" '设置打印固定行objExl.ActiveSheet.PageSetup.PrintTitleColumns = "" '打印标题objExl.ActiveSheet.PageSetup.RightFooter = "打印时间: " & _Format(Now, "yyyy年mm月dd日hh:MM:ss") objExl.ActiveWindow.View = xlPageBreakPreview '设置显示方式objExl.ActiveWindow.Zoom = 100 '设置显示大小'给工作表加密码objExl.ActiveSheet.Protect "123", DrawingObjects:=True, _Contents:=True, Scenarios:=TrueobjExl.Application.IgnoreRemoteRequests = FalseobjExl.Visible = True '使EXCEL可见objExl.Application.WindowState = xlMaximized 'EXCEL的显示方式为最大化objExl.ActiveWindow.WindowState = xlMaximized '工作薄显示方式为最大化objExl.SheetsInNewWorkbook = 3 '将默认新工作薄数量改回3个Set objExl = Nothing '清除对象Me.MousePointer = 0 '修改鼠标Exit Suberr1:objExl.SheetsInNewWorkbook = 3objExl.DisplayAlerts = False '关闭时不提示保存objExl.Quit '关闭EXCELobjExl.DisplayAlerts = True '关闭时提示保存Set objExl = NothingMe.MousePointer = 0End Sub＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝全面控制Excel首先创建Excel 对象，使用ComObj:Dim ExcelID as Excel.ApplicationSet ExcelID as new Excel.Application1) 显示当前窗口：ExcelID.Visible := True;2) 更改Excel 标题栏：ExcelID.Caption := '应用程序调用Microsoft Excel';3) 添加新工作簿：ExcelID.WorkBooks.Add;4) 打开已存在的工作簿：ExcelID.WorkBooks.Open( 'C:\Excel\Demo.xls' );5) 设置第2个工作表为活动工作表：ExcelID.WorkSheets[2].Activate;或ExcelID.WorkSheets[ 'Sheet2' ].Activate;6) 给单元格赋值：ExcelID.Cells[1,4].Value := '第一行第四列';7) 设置指定列的宽度（单位：字符个数），以第一列为例：ExcelID.ActiveSheet.Columns[1].ColumnsWidth := 5;8) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelID.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米9) 在第8行之前插入分页符：ExcelID.WorkSheets[1].Rows[8].PageBreak := 1;10) 在第8列之前删除分页符：ExcelID.ActiveSheet.Columns[4].PageBreak := 0;11) 指定边框线宽度：ExcelID.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;1-左2-右3-顶4-底5-斜( \ ) 6-斜( / )12) 清除第一行第四列单元格公式：ExcelID.ActiveSheet.Cells[1,4].ClearContents;13) 设置第一行字体属性：ExcelID.ActiveSheet.Rows[1] := '隶书';ExcelID.ActiveSheet.Rows[1].Font.Color := clBlue;ExcelID.ActiveSheet.Rows[1].Font.Bold := True;ExcelID.ActiveSheet.Rows[1].Font.UnderLine := True;14) 进行页面设置：a.页眉：ExcelID.ActiveSheet.PageSetup.CenterHeader := '报表演示';b.页脚：ExcelID.ActiveSheet.PageSetup.CenterFooter := '第&P页';c.页眉到顶端边距2cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;d.页脚到底端边距3cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;e.顶边距2cm：ExcelID.ActiveSheet.PageSetup.TopMargin := 2/0.035;f.底边距2cm：ExcelID.ActiveSheet.PageSetup.BottomMargin := 2/0.035;g.左边距2cm：ExcelID.ActiveSheet.PageSetup.LeftMargin := 2/0.035;h.右边距2cm：ExcelID.ActiveSheet.PageSetup.RightMargin := 2/0.035;i.页面水平居中：ExcelID.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;j.页面垂直居中：ExcelID.ActiveSheet.PageSetup.CenterVertically := 2/0.035;k.打印单元格网线：ExcelID.ActiveSheet.PageSetup.PrintGridLines := True;15) 拷贝操作：a.拷贝整个工作表：ed.Range.Copy;b.拷贝指定区域：ExcelID.ActiveSheet.Range[ 'A1:E2' ].Copy;c.从A1位置开始粘贴：ExcelID.ActiveSheet.Range.[ 'A1' ].PasteSpecial;d.从文件尾部开始粘贴：ExcelID.ActiveSheet.Range.PasteSpecial;16) 插入一行或一列：a. ExcelID.ActiveSheet.Rows[2].Insert;b. ExcelID.ActiveSheet.Columns[1].Insert;17) 删除一行或一列：a. ExcelID.ActiveSheet.Rows[2].Delete;b. ExcelID.ActiveSheet.Columns[1].Delete;18) 打印预览工作表：ExcelID.ActiveSheet.PrintPreview;19) 打印输出工作表：ExcelID.ActiveSheet.PrintOut;20) 工作表保存：If not ExcelID.ActiveWorkBook.Saved thenExcelID.ActiveSheet.PrintPreviewEnd if21) 工作表另存为：ExcelID.SaveAs( 'C:\Excel\Demo1.xls' );22) 放弃存盘：ExcelID.ActiveWorkBook.Saved := True;23) 关闭工作簿：ExcelID.WorkBooks.Close;24) 退出Excel：ExcelID.Quit;25) 设置工作表密码:ExcelID.ActiveSheet.Protect "123", DrawingObjects:=True, Contents:=True, Scenarios:=True26) EXCEL的显示方式为最大化ExcelID.Application.WindowState = xlMaximized27) 工作薄显示方式为最大化ExcelID.ActiveWindow.WindowState = xlMaximized28) 设置打开默认工作薄数量ExcelID.SheetsInNewWorkbook = 329) '关闭时是否提示保存(true 保存；false 不保存)ExcelID.DisplayAlerts = False30) 设置拆分窗口,及固定行位置ExcelID.ActiveWindow.SplitRow = 1ExcelID.ActiveWindow.FreezePanes = True31) 设置打印时固定打印内容ExcelID.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"32) 设置打印标题ExcelID.ActiveSheet.PageSetup.PrintTitleColumns = ""33) 设置显示方式(分页方式显示)ExcelID.ActiveWindow.View = xlPageBreakPreview34) 设置显示比例ExcelID.ActiveWindow.Zoom = 10035) 让Excel 响应DDE 请求Ex.Application.IgnoreRemoteRequests = False用VB操作Excel(VB6.0)(整理)2008-09-23 22:16:30| 分类：文章转载| 标签：excel office |字号订阅用VB操作Excel(VB6.0)(整理)全面控制Excel:首先创建Excel对象，使用ComObj:Dim ExcelID as Excel.ApplicationSet ExcelID as new Excel.Application1)显示当前窗口：ExcelID.Visible:=True;2)更改Excel标题栏：ExcelID.Caption:='应用程序调用MicrosoftExcel';3)添加新工作簿：ExcelID.WorkBooks.Add;4)打开已存在的工作簿：ExcelID.WorkBooks.Open('C:\Excel\Demo.xls');5)设置第2个工作表为活动工作表：ExcelID.WorkSheets[2].Activate;或ExcelID.WorkSheets['Sheet2'].Activate;6)给单元格赋值：ExcelID.Cells[1,4].Value:='第一行第四列';7)设置指定列的宽度（单位：字符个数），以第一列为例：ExcelID.ActiveSheet.Columns[1].ColumnsWidth:=5;8)设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelID.ActiveSheet.Rows[2].RowHeight:=1/0.035;//1厘米9)在第8行之前插入分页符：ExcelID.WorkSheets[1].Rows[8].PageBreak:=1;10)在第8列之前删除分页符：ExcelID.ActiveSheet.Columns[4].PageBreak:=0;11)指定边框线宽度：ExcelID.ActiveSheet.Range['B3:D4'].Borders[2].Weight:=3;1-左2-右3-顶4-底5-斜(\) 6-斜(/)12)清除第一行第四列单元格公式：ExcelID.ActiveSheet.Cells[1,4].ClearContents;13)设置第一行字体属性：ExcelID.ActiveSheet.Rows[1]:='隶书';ExcelID.ActiveSheet.Rows[1].Font.Color :=clBlue;ExcelID.ActiveSheet.Rows[1].Font.Bold :=True;ExcelID.ActiveSheet.Rows[1].Font.UnderLine:=True;14)进行页面设置：a.页眉：ExcelID.ActiveSheet.PageSetup.CenterHeader:='报表演示';b.页脚：ExcelID.ActiveSheet.PageSetup.CenterFooter:='第&P页';c.页眉到顶端边距2cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin:=2/0.035;d.页脚到底端边距3cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin:=3/0.035;e.顶边距2cm：ExcelID.ActiveSheet.PageSetup.TopMargin:=2/0.035;f.底边距2cm：ExcelID.ActiveSheet.PageSetup.BottomMargin:=2/0.035;g.左边距2cm：ExcelID.ActiveSheet.PageSetup.LeftMargin:=2/0.035;h.右边距2cm：ExcelID.ActiveSheet.PageSetup.RightMargin:=2/0.035;i.页面水平居中：ExcelID.ActiveSheet.PageSetup.CenterHorizontally:=2/0.035;j.页面垂直居中：。

用VB操作Excel（VB6.0）（整理）

用VB操作Excel（VB6.0）（整理）用VB操作Excel(VB6.0)(整理)全面控制Excel:首先创建Excel对象，使用ComObj:Dim ExcelID as Excel.ApplicationSet ExcelID as new Excel.Application1)显示当前窗口：ExcelID.Visible:=True;2)更改Excel标题栏：ExcelID.Caption:='应用程序调用MicrosoftExcel';3)添加新工作簿：ExcelID.WorkBooks.Add;4)打开已存在的工作簿：ExcelID.WorkBooks.Open('C:\Excel\Demo.xls');5)设置第2个工作表为活动工作表：ExcelID.WorkSheets[2].Activate;或ExcelID.WorkSheets['Sheet2'].Activate;6)给单元格赋值：ExcelID.Cells[1,4].Value:='第一行第四列';7)设置指定列的宽度（单位：字符个数），以第一列为例：ExcelID.ActiveSheet.Columns[1].ColumnsWidth:=5;8)设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：ExcelID.ActiveSheet.Rows[2].RowHeight:=1/0.035;//1厘米9)在第8行之前插入分页符：ExcelID.WorkSheets[1].Rows[8].PageBreak:=1;10)在第8列之前删除分页符：ExcelID.ActiveSheet.Columns[4].PageBreak:=0;11)指定边框线宽度：ExcelID.ActiveSheet.Range['B3:D4'].Borders[2].Weight:=3;1-左 2-右 3-顶4-底 5-斜(\) 6-斜(/)12)清除第一行第四列单元格公式：ExcelID.ActiveSheet.Cells[1,4].ClearContents;13)设置第一行字体属性：ExcelID.ActiveSheet.Rows[1]:='隶书'; ExcelID.ActiveSheet.Rows[1].Font.Color :=clBlue; ExcelID.ActiveSheet.Rows[1].Font.Bold :=True;ExcelID.ActiveSheet.Rows[1].Font.UnderLine:=True;14)进行页面设置：a.页眉：ExcelID.ActiveSheet.PageSetup.CenterHeader:='报表演示'; b.页脚：ExcelID.ActiveSheet.PageSetup.CenterFooter:='第&P页'; c.页眉到顶端边距2cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin:=2/0.035; d.页脚到底端边距3cm：ExcelID.ActiveSheet.PageSetup.HeaderMargin:=3/0.035; e.顶边距2cm：ExcelID.ActiveSheet.PageSetup.T opMargin:=2/0.035;f.底边距2cm：ExcelID.ActiveSheet.PageSetup.BottomMargin:=2/0.035; g.左边距2cm：ExcelID.ActiveSheet.PageSetup.LeftMargin:=2/0.035;h.右边距2cm：ExcelID.ActiveSheet.PageSetup.RightMargin:=2/0.035;i.页面水平居中：ExcelID.ActiveSheet.PageSetup.CenterHorizontally:=2/0.035; j.页面垂直居中：ExcelID.ActiveSheet.PageSetup.CenterVertically:=2/0.035; k.打印单元格网线：ExcelID.ActiveSheet.PageSetup.PrintGridLines:=True;15)拷贝操作：a.拷贝整个工作表：ed.Range.Copy;b.拷贝指定区域：ExcelID.ActiveSheet.Range['A1:E2'].Copy;c.从A1位置开始粘贴：ExcelID.ActiveSheet.Range.['A1'].PasteSpecial;d.从文件尾部开始粘贴：ExcelID.ActiveSheet.Range.PasteSpecial;16)插入一行或一列：a.ExcelID.ActiveSheet.Rows[2].Insert;b.ExcelID.ActiveSheet.Columns[1].Insert;17)删除一行或一列：a.ExcelID.ActiveSheet.Rows[2].Delete;b.ExcelID.ActiveSheet.Columns[1].Delete;18)打印预览工作表：ExcelID.ActiveSheet.PrintPreview;19)打印输出工作表：ExcelID.ActiveSheet.PrintOut;20)工作表保存：IfnotExcelID.ActiveWorkBook.SavedthenExcelID.ActiveSheet.PrintPreviewEndif21)工作表另存为：ExcelID.SaveAs('C:\Excel\Demo1.xls');22)放弃存盘：ExcelID.ActiveWorkBook.Saved:=True;23)关闭工作簿：ExcelID.WorkBooks.Close;24)退出Excel：ExcelID.Quit;25)设置工作表密码:ExcelID.ActiveSheet.Protect"123",DrawingObjects:=True,Con tents:=True,Scenarios:=True26)EXCEL的显示方式为最大化ExcelID.Application.WindowState=xlMaximized27)工作薄显示方式为最大化ExcelID.ActiveWindow.WindowState=xlMaximized28)设置打开默认工作薄数量ExcelID.SheetsInNewWorkbook=329)'关闭时是否提示保存(true保存；false不保存)ExcelID.DisplayAlerts=False30)设置拆分窗口,及固定行位置ExcelID.ActiveWindow.SplitRow=1ExcelID.ActiveWindow.FreezePanes=True31)设置打印时固定打印内容ExcelID.ActiveSheet.PageSetup.PrintTitleRows="$1:$1"32)设置打印标题ExcelID.ActiveSheet.PageSetup.PrintTitleColumns=""33)设置显示方式(分页方式显示)ExcelID.ActiveWindow.View=xlPageBreakPreview34)设置显示比例ExcelID.ActiveWindow.Zoom=10035)让Excel响应DDE请求Ex.Application.IgnoreRemoteRequests=False用VB操作EXCEL示例代码Private Sub Command3_Click()On Error GoTo err1Dim i As LongDim j As LongDim objExl As Excel.Application '声明对象变量Me.MousePointer=11 '改变鼠标样式Set objExl=New Excel.Application'初始化对象变量objExl.SheetsInNewWorkbook=1 '将新建的工作薄数量设为1 objExl.Workbooks.Add'增加一个工作薄objExl.Sheets(objExl.Sheets.Count).Name="book1" '修改工作薄名称objExl.Sheets.Add,objExl.Sheets("book1")‘增加第二个工作薄在第一个之后objExl.Sheets(objExl.Sheets.Count).Name="book2"objExl.Sheets.Add,objExl.Sheets("book2")‘增加第三个工作薄在第二个之后objExl.Sheets(objExl.Sheets.Count).Name="book3"objExl.Sheets("book1").Select '选中工作薄<book1>For i=1 To 50'循环写入数据For j=1 To 5If i=1 ThenobjExl.Selection.NumberFormatLocal="@" '设置格式为文本objExl.Cells(i,j)="E"&i&jElseobjExl.Cells(i,j)=i&jEndIfNextNextobjExl.Rows("1:1").Select '选中第一行objExl.Selection.Font.Bold=True '设为粗体objExl.Selection.Font.Size=24 '设置字体大小objExl.Cells.EntireColumn.AutoFit '自动调整列宽objExl.ActiveWindow.SplitRow=1 '拆分第一行objExl.ActiveWindow.SplitColumn=0 '拆分列objExl.ActiveWindow.FreezePanes=True '固定拆分objExl.ActiveSheet.PageSetup.PrintTitleRows="$1:$1" '设置打印固定行objExl.ActiveSheet.PageSetup.PrintTitleColumns=""'打印标题objExl.ActiveSheet.PageSetup.RightFooter="打印时间:"&_ Format(Now,"yyyy年mm月dd日hh:MM:ss")objExl.ActiveWindow.View=xlPageBreakPreview'设置显示方式objExl.ActiveWindow.Zoom=100 '设置显示大小'给工作表加密码objExl.ActiveSheet.Protect"123",DrawingObjects:=True, _Contents:=True,Scenarios:=TrueobjExl.Application.IgnoreRemoteRequests=FalseobjExl.Visible=True '使EXCEL可见objExl.Application.WindowState=xlMaximized'EXCEL的显示方式为最大化objExl.ActiveWindow.WindowState=xlMaximized'工作薄显示方式为最大化objExl.SheetsInNewWorkbook=3 '将默认新工作薄数量改回3个Set objExl=Nothing'清除对象Me.MousePointer=0 '修改鼠标ExitSuberr1:objExl.SheetsInNewWorkbook=3objExl.DisplayAlerts=False '关闭时不提示保存objExl.Quit'关闭EXCELobjExl.DisplayAlerts=True '关闭时提示保存Set objExl=NothingMe.MousePointer=0End Sub。

vba精典源程序源代码(工作表操作)

Worksheets("sheet1").Visible = xlSheetVisible
End Sub
示例说明：本示例演示了隐藏和显示工作表的各种情形。其中，使用xlSheetVeryHidden常量来隐藏工作表，将不能通过选择工作表菜单栏中的“格式”——“工作表”——“取消隐藏”命令来取消隐藏。
End Sub
示例说明：使用Name属性可以获取指定工作表的名称，也可以设置工作表的名称。
--------------------------------------------------------------------------------
[示例04-06-02]重命名工作表
Sub ReNameSheet()
End Sub
示例说明：Activate方法只能激活一个工作表，而Select方法可以同时选择多个工作表。
--------------------------------------------------------------------------------
[示例04-07-02]
Else
MsgBox "已到第一个工作表"
End If
End Sub
示例说明：如果当前工作表是第一个工作表，则使用Previous属性会出错。
--------------------------------------------------------------------------------
[示例04-08]获取当前工作表的索引号(Index属性)
Sub GetSheetIndex()
Dim i As Long

VB对Excel文件的操作代码

VB对Excel文件的操作代码Dim A(65536) As StringDim i As IntegerDim xlsApp As ObjectDim xlsWorkbook As ObjectSet xlsApp = CreateObject("Excel.Application")Set xlsWorkbook = xlsApp.Workbooks.Open("D:\文件名.xls") xlsApp.Visible = FalsexlsApp.Range("A1").Selecti = -1Doi = i + 1A(i) = xlsApp.ActiveCell.Offset(0, i).ValueIf A(i) <> "" ThenPrint i + 1;Print " ";Print A(i)End IfLoop Until A(i) = ""If Dir("D:\文件名2.xls") <> "" Then Kill ("D:\文件名2.xls")xlsWorkbook.saveas ("D:\文件名2.xls")xlsApp.Workbooks.ClosexlsApp.QuitSet xlsSheet = NothingSet xlsWorkbook = NothingSet xlsApp = Nothing'以下为Excel操作的其他一些功能'Set xlsSheet = xlsWorkbook.Worksheets("表名") '设置活动工作表'xlsSheet.Cells(行,列) = "值" '赋值'xlsApp.WorkBooks.Add '添加工作薄'xlsApp.WorkSheets.Add '添加工作表'xlsWorkbook.saveas ("文件名") '保存文件'xlsApp.ActiveSheet.Columns(1).ColumnWidth = 20 '设置列宽'xlsApp.ActiveSheet.Rows(1).RowHeight = 30 '设置行高'xlsApp.ActiveSheet.rows(8).pagebreak = 1 '插入分页符（值为0时删除分页符）'xlsApp.ActiveSheet.PageSetup.CenterHeader = "标题" '页眉设置。

VBA代码全集模板

目录一、引用 (3)二、Worksheet_Change事件： (3)三、相乘 (5)四、相减 (6)五、高级筛选 (6)六、双击事件 (8)七．单位汇总（sumif），单条件汇总 (10)八、多条件汇总（连接、sumif） (13)九、多条件汇总、ado (15)十、对账 (16)十一、sql筛选 (20)十二、sql连接、交叉汇总 (21)十三、select语句总结 (23)十四、报表（有层次） (24)一、引用相对引用B4绝对引用$B$4混合引用$B4、B$4F4进行引用切换，$在字母前面则锁定列，在数字前面则锁定行。

二、Worksheet_Change 事件：1.在单元格中C4=VLOOKUP(B4,简码表!$B$4:$C$1000,2,FALSE)2. Worksheet_Change事件代码：Private Sub Worksheet_Change(ByVal Target As Range)On error resume nextIf Target.Row > 3 And Target.Column = 2 Theni = Target.RowCells(i, 3) = Application.WorksheetFunction.VLookup(Cells(i, 2), Sheets("简码表").Range("b4:c100"), 2, False)End IfEnd Sub备查代码：Private Sub Worksheet_Change(ByVal Target As Range)On Error Resume NextIf Target.Row > 3 And Target.Column = 5 Theni = Target.RowCells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 5), Sheets("类款项").Range("b2:e2000"), 2, False)Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 5), Sheets("类款项").Range("b2:e2000"), 3,False)Cells(i, 8) = Application.WorksheetFunction.VLookup(Cells(i, 5), Sheets("类款项").Range("b2:e2000"), 4, False)End IfEnd Sub三、相乘Sub 计算金额()Application.ScreenUpdating = FalseDim i As LongDim irow As Longirow = Range("a3").End(xldown).RowFor i = 4 To irowCells(i, 3) = Cells(i, 1) * Cells(i, 2)Next iApplication.ScreenUpdating = TrueEnd Sub四、相减Sub 相减()Application.ScreenUpdating = FalseRange("c3:c10000").ClearContentsDim i As LongDim irow As Longirow = Range("a5000").End(xlUp).RowFor i = 3 To irowCells(i, 3) = VBA.Round((Cells(i, 1) - Cells(i, 2)), 2)Next iApplication.ScreenUpdating = TrueEnd Sub五、高级筛选（工具-宏-录制新宏，宏名改成高级筛选）Sub 高级筛选()Sheets("业务").Range("A3:I10000").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ActiveCell.Range("A1:B1"), Unique:=TrueEnd Sub六、双击事件1.插入-名称-定义（修改名称和引用位置）2．查看代码-插入-用户窗体工具箱-多页、列表框-右键属性点击page1修改caption为资产类-点击空白列表框修改rowsource 为box1依次类推3. 业务表-查看代码 Worksheet beforedoubleclickPrivate Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Row > 3 And Target.Column = 6 ThenUserForm1.ShowSheets("初始化").Range("m3") = ActiveCellElseIf Target.Row > 3 And Target.Column = 7 ThenUserForm2.ShowEnd IfEnd Sub备查代码：Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Row > 3 And Target.Column = 6 ThenUserForm1.ShowSheets("初始化").Range("c2") = ActiveCellElseIf Target.Row > 3 And Target.Column = 7 ThenUserForm2.ShowSheets("初始化").Range("f2") = ActiveCellElseIf Target.Row > 3 And Target.Column = 8 ThenUserForm3.ShowEnd IfEnd Sub4．右键点击Userform1查看代码 Listbox1 dbclickPrivate Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)ActiveSheet.Cells(ActiveCell.Row, 6) = ListBox1.List(ListBox1.ListIndex, 0)Unload MeEnd SubPrivate Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)ActiveSheet.Cells(ActiveCell.Row, 6) = ListBox1.List(ListBox2.ListIndex, 0)Unload MeEnd SubPrivate Sub ListBox3_DblClick(ByVal Cancel As MSForms.ReturnBoolean)ActiveSheet.Cells(ActiveCell.Row, 6) = ListBox1.List(ListBox3.ListIndex, 0)Unload MeEnd SubPrivate Sub ListBox4_DblClick(ByVal Cancel As MSForms.ReturnBoolean)ActiveSheet.Cells(ActiveCell.Row, 6) = ListBox1.List(ListBox4.ListIndex, 0)Unload MeEnd SubPrivate Sub ListBox5_DblClick(ByVal Cancel As MSForms.ReturnBoolean)ActiveSheet.Cells(ActiveCell.Row, 6) = ListBox1.List(ListBox5.ListIndex, 0)Unload MeEnd Sub见上图5.插入用户窗体右键点击userform2 worksheet dblclickPrivate Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)ActiveSheet.Cells(ActiveCell.Row, 7) = ListBox1.List(ListBox1.ListIndex, 0)Unload MeEnd SubUserform initializePrivate Sub UserForm_Initialize()Application.ScreenUpdating = FalseWith Sheets("初始化")Sheets("科目表").Range("h2:i10000").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=.Range("m2:m3"), CopyToRange:=.Range("n2"), Unique:=TrueEnd WithApplication.ScreenUpdating = TrueEnd Sub七．单位汇总（sumif），单条件汇总=SUMIF(业务!$D$4:$D$1000,单位汇总!$A15,业务!I$4:I$10000)Sub 单位汇总1()Application.ScreenUpdating = Falserange("a1:i10000").ClearCells(3, 2) = "指标数"Cells(3, 3) = "拨款数"Cells(3, 4) = "余额"Cells(1, 7) = "单位"Cells(3, 7) = "单位"Cells(3, 8) = "指标数"Cells(3, 9) = "拨款数"Sheets("业务").Range("D3:D10000").AdvancedFilter Action:=xlFilterCopy, _CopyToRange:=Range("A3"), Unique:=TrueSheets("业务").Range("A3:J10000").AdvancedFilter Action:=xlFilterCopy, _CriteriaRange:=Range("G1:G2"), CopyToRange:=Range("G3:I3"), Unique:=FalseDim i As LongDim irow As Longirow = Range("a3").End(xlDown).RowFor i = 4 To irowCells(i, 2) = Application.WorksheetFunction.SumIf(Range("g4:g10000"), Cells(i, 1), Range("h4:h10000"))Cells(i, 3) = Application.WorksheetFunction.SumIf(Range("g4:g10000"), Cells(i, 1), Range("i4:i10000"))Cells(i, 4) = VBA.Round(Cells(i, 2) - Cells(i, 3), 2)Next iRange("g1:i10000").ClearApplication.ScreenUpdating = TrueEnd Sub八、多条件汇总（连接、sumif）连接=k4&l4&m4&n4Vba：Sub 多条件汇总()Application.ScreenUpdating = FalseRange("a1:p10000").ClearSheets("业务").Range("D3:G10000").AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Range("B3:E3"), Unique:=TrueSheets("业务").Range("D3:I10000").AdvancedFilter Action:=xlFilterCopy, _CopyToRange:=Range("K3:P3"), Unique:=FalseDim j As LongDim jrow As Longjrow = Range("k3").End(xlDown).RowFor j = 4 To jrowCells(j, 10) = Cells(j, 11) & Cells(j, 12) & Cells(j, 13) & Cells(j, 14)Next jDim i As LongDim irow As Longirow = Range("b3").End(xlDown).RowFor i = 4 To irowCells(3, 6) = "指标数"Cells(3, 7) = "拨款数"Cells(3, 8) = "余额"Cells(i, 1) = Cells(i, 2) & Cells(i, 3) & Cells(i, 4) & Cells(i, 5)Cells(i, 6) = Application.WorksheetFunction.SumIf(Range("j4:j10000"), Cells(i, 1), Range("o4:o10000"))Cells(i, 7) = Application.WorksheetFunction.SumIf(Range("j4:j10000"), Cells(i, 1), Range("p4:p10000"))Cells(i, 8) = VBA.Round(Cells(i, 6) - Cells(i, 7), 2)Next iRange("i3:p10000").ClearRange("a1:a10000").DeleteApplication.ScreenUpdating = TrueEnd Sub九、多条件汇总、adoSub 多条件汇总()Application.ScreenUpdating = FalseDim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT 单位,类,款,项, sum(指标数) as 预算股指标,sum(拨款数) as 预算股拨款 from[业务$a3:J10000] where 归口='" & Range("h2").Value & "'and 月<=" & Range("i2").Value & " GROUP BY 单位,类,款,项"rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("多条件汇总").Cells(3, i) = rst.Fields(i - 1).Name Next iSheets("多条件汇总").Range("a4").CopyFromRecordset rst rst.Closecnn.CloseSet rst = NothingSet cnn = NothingApplication.ScreenUpdating = TrueEnd Sub十、对账Sub 预算股()Application.ScreenUpdating = FalseDim i As IntegerDim strsql1 As StringDim cnn1 As New ADODB.ConnectionDim rst1 As New ADODB.Recordsetcnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql1 = " SELECT 单位,类,款,项, sum(指标数) as 预算股指标 from[预算股$a3:m50000] where 归口='" & Range("h2").Value & "'and 月<=" & Range("i2").Value & " GROUP BY 单位,类,款,项" rst1.Open strsql1, cnn1For i = 1 To rst1.Fields.CountSheets("对帐").Cells(3, i + 10) = rst1.Fields(i - 1).NameNext iSheets("对帐").Range("k4").CopyFromRecordset rst1rst1.Closecnn1.CloseSet rst1 = NothingSet cnn1 = NothingDim strsql2 As StringDim cnn2 As New ADODB.ConnectionDim rst2 As New ADODB.Recordsetcnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql2 = " SELECT 单位,类,款,项, sum(指标数) as 专业股指标 from[专业股$a3:j50000] where 归口='" & Range("h2").Value & "'and 月<=" & Range("i2").Value & " GROUP BY 单位,类,款,项" rst2.Open strsql2, cnn2For i = 1 To rst2.Fields.CountSheets("对帐").Cells(3, i + 19) = rst2.Fields(i - 1).NameNext iSheets("对帐").Range("t4").CopyFromRecordset rst2rst2.Closecnn2.CloseSet rst2 = NothingSet cnn2 = Nothings = Application.WorksheetFunction.CountA(Range("k4:k10000")) + 4Range("T4:W10000").SelectSelection.CopyRange("K" & s).SelectActiveSheet.PasteRange("X4:X10000").SelectSelection.CopyRange("P" & s).SelectActiveSheet.PasteRange("X3").SelectSelection.CopyRange("P3").SelectActiveSheet.PasteDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT 单位,类,款,项, sum(预算股指标) as 预算股指标 ,sum(专业股指标) as 专业股指标 from[对帐$k3:p50000] GROUP BY 单位,类,款,项"rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("对帐").Cells(3, i) = rst.Fields(i - 1).NameNext iSheets("对帐").Range("a4").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingApplication.ScreenUpdating = TrueEnd Sub十一、sql筛选Sub 筛选()Application.ScreenUpdating = FalseDim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT distinct 单位,类,款,项 from[专业$a3:h10000]"rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("筛选").Cells(3, i) = rst.Fields(i - 1).NameNext iSheets("筛选").Range("a4").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingApplication.ScreenUpdating = True End Sub十二、sql连接、交叉汇总Sub 连接()Application.ScreenUpdating = FalseDim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT 股,月,归口,单位,类,款,项,指标数 from [专业$a3:h10000] union ALL SELECT 股,月,归口,单位,类,款,项,指标数 from [预算$a3:l10000] order by 股 desc"rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("连接").Cells(1, i + 19) = rst.Fields(i - 1).NameNext iSheets("连接").Range("t2").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingApplication.ScreenUpdating = TrueEnd SubSub 汇总()Application.ScreenUpdating = FalseCall 连接Dim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " transform sum(指标数) SELECT 单位,类,款,项 from [连接$t1:aa10000] where 归口= '" & Range("h2").Value & "' and 月=" & Range("i2").Value & " group by 单位,类,款,项 pivot 股" rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("连接").Cells(3, i) = rst.Fields(i - 1).NameNext iSheets("连接").Range("a4").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingRange("t1:aa10000").ClearContentsApplication.ScreenUpdating = TrueEnd Sub十三、select语句总结1、筛选（false ---筛选全部）Select 列表名称1,列表名称2,…….列表名称n from [表$区域]或者Select * from [表$区域]2、筛选唯一的数据Select distinct 列表名称1,列表名称2,…….列表名称n from [表$区域]3、分类汇总Select 列表名称1,列表名称2,…….列表名称n，sum(a) as a from [表$区域]Group by列表名称1,列表名称2,…….列表名称n4、条件分类汇总Select 列表名称1,列表名称2,…….列表名称n，sum(a) as a from [表$区域]Where 归口=’”& range(“”).value &”’ and 月=”& range(“”).value &” Group by列表名称1,列表名称2,…….列表名称n5、交叉汇总Transform sum() select 列名称1,……列名称n from[表$区域] group by 列名称1，…..列名称n pivot 交叉事项6、连接Select 列名称1，…列名称n from[表$区域] union all Select 列名称1，…列名称n from[表$区域] order by 列名称 desc十四、报表（有层次）连接Transform sum（指标数），pivot 股按单位、类、款进行汇总按单位、类进行汇总按单位进行汇总连接以上四个表的内容，并按单位、类、款、项进行排序，其中单位按降序排序1、整体写代码Sub 报表()Application.ScreenUpdating = FalseDim i As IntegerDim strsql1 As StringDim cnn1 As New ADODB.ConnectionDim rst1 As New ADODB.Recordsetcnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql1 = " SELECT 股,月,归口,单位,类,款,项,sum(指标数) as 指标数 from[专业$a3:h10000] group by 股,月,归口,单位,类,款,项 union all SELECT 股,月,归口,单位,类,款,项,sum(指标数) as 指标数 from[预算$a3:l10000] group by 股,月,归口,单位,类,款,项 order by 股 desc"rst1.Open strsql1, cnn1For i = 1 To rst1.Fields.CountSheets("报表").Cells(3, i + 9) = rst1.Fields(i - 1).NameNext iSheets("报表").Range("j4").CopyFromRecordset rst1rst1.Closecnn1.CloseSet rst1 = NothingSet cnn1 = NothingDim strsql2 As StringDim cnn2 As New ADODB.ConnectionDim rst2 As New ADODB.Recordsetcnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql2 = "transform sum(指标数) SELECT 单位,类,款,项 from[报表$j3:q10000] where 归口='" & Range("g2") _.Value & "' and 月<=" & Range("h2").Value & " group by 单位,类,款,项 order by 单位 desc pivot 股 "rst2.Open strsql2, cnn2For i = 1 To rst2.Fields.CountSheets("报表").Cells(3, i + 19) = rst2.Fields(i - 1).NameNext iSheets("报表").Range("t4").CopyFromRecordset rst2rst2.Closecnn2.CloseSet rst2 = NothingSet cnn2 = NothingDim strsql3 As StringDim cnn3 As New ADODB.ConnectionDim rst3 As New ADODB.Recordsetcnn3.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql3 = "SELECT 单位,类,款,sum(专业股) as 专业股,sum(预算股) as 预算股 from[报表$t3:y10000] group by 单位,类,款 order by 单位 desc"rst3.Open strsql3, cnn3For i = 1 To rst3.Fields.CountSheets("报表").Cells(3, i + 26) = rst3.Fields(i - 1).NameNext iSheets("报表").Range("aa4").CopyFromRecordset rst3rst3.Closecnn3.CloseSet rst3 = NothingSet cnn3 = NothingDim strsql4 As StringDim cnn4 As New ADODB.ConnectionDim rst4 As New ADODB.Recordsetcnn4.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql4 = "SELECT 单位,类,sum(专业股) as 专业股,sum(预算股) as 预算股 from[报表$t3:y10000] group by 单位,类 order by 单位 desc"rst4.Open strsql4, cnn4For i = 1 To rst4.Fields.CountSheets("报表").Cells(3, i + 32) = rst4.Fields(i - 1).NameNext iSheets("报表").Range("ag4").CopyFromRecordset rst4rst4.Closecnn4.CloseSet rst4 = NothingSet cnn4 = NothingDim strsql5 As StringDim cnn5 As New ADODB.ConnectionDim rst5 As New ADODB.Recordsetcnn5.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql5 = "SELECT 单位,sum(专业股) as 专业股,sum(预算股) as 预算股 from[报表$t3:y10000] group by 单位 order by 单位 desc"rst5.Open strsql5, cnn5For i = 1 To rst5.Fields.CountSheets("报表").Cells(3, i + 37) = rst5.Fields(i - 1).NameNext iSheets("报表").Range("al4").CopyFromRecordset rst5rst5.Closecnn5.CloseSet rst5 = NothingSet cnn5 = NothingColumns("AD:AD").SelectSelection.Insert Shift:=xlToRightRange("ad3") = "项"Columns("Aj:Ak").SelectSelection.Insert Shift:=xlToRightRange("aj3") = "款"Range("ak3") = "项"Columns("Ap:Ar").SelectSelection.Insert Shift:=xlToRightRange("ap3") = "类"Range("aq3") = "款"Range("ar3") = "项"Dim strsql6 As StringDim cnn6 As New ADODB.ConnectionDim rst6 As New ADODB.Recordsetcnn6.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql6 = " SELECT 单位,类,款,项,专业股,预算股 from [报表$t3:y10000] union all SELECT 单位,类,款,项,专业股,预算股 from [报表$aa3:af10000] union all SELECT 单位,类,款,项,专业股,预算股 from [报表$ah3:am10000] union all SELECT 单位,类,款,项,专业股,预算股 from [报表$ao3:at10000] order by 单位 desc,类,款,项 "rst6.Open strsql6, cnn6For i = 1 To rst6.Fields.CountSheets("报表").Cells(3, i) = rst6.Fields(i - 1).NameNext iSheets("报表").Range("a4").CopyFromRecordset rst6rst6.Closecnn6.CloseSet rst6 = NothingSet cnn6 = NothingRange("j1:au10000").ClearContentsDim p As LongDim prow As Longprow = Range("a3").End(xlDown).RowFor p = 4 To prowRange("g3") = "金额"Cells(p, 7) = VBA.Round(Cells(p, 6) - Cells(p, 5), 2)Next pApplication.ScreenUpdating = TrueEnd Sub2、分开写代码：Sub 连接()Application.ScreenUpdating = FalseDim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT 月,归口,股,单位,类,款,项,sum(指标数) as 指标数 from[专业$a3:h10000] group by 月,归口,股,单位,类,款,项 union all SELECT 月, 归口,股,单位,类,款,项,sum(指标数) as 指标数 from[预算$a3:l10000] group by 月, 归口,股,单位,类,款,项 order by 股 desc"rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("报表").Cells(3, i + 9) = rst.Fields(i - 1).NameNext iSheets("报表").Range("j4").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingApplication.ScreenUpdating = TrueEnd SubSub 项()Application.ScreenUpdating = FalseCall 连接Dim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';DataSource=" & ThisWorkbook.FullNamestrsql = "transform sum(指标数) SELECT 单位,类,款,项 from [报表$j3:q10000] where 归口= '" & Range("g2").Value & "' and 月<=" & Range("h2").Value & " group by 单位,类,款,项 pivot 股"rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("报表").Cells(3, i + 19) = rst.Fields(i - 1).NameNext iSheets("报表").Range("t4").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingApplication.ScreenUpdating = TrueEnd SubSub 款()Application.ScreenUpdating = FalseCall 项Dim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT 单位,类,款, sum(专业股) as 专业股, sum(预算股) as 预算股 from [报表$t3:y10000] group by 单位,类,款 "rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("报表").Cells(3, i + 26) = rst.Fields(i - 1).NameNext iSheets("报表").Range("aa4").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingColumns("AD:AD").SelectSelection.Insert Shift:=xlToRightCells(3, 30) = "项"Application.ScreenUpdating = TrueEnd SubSub 类()Application.ScreenUpdating = FalseCall 款Dim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT 单位,类, sum(专业股) as 专业股, sum(预算股) as 预算股 from [报表$aa3:af10000] group by 单位,类 "rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("报表").Cells(3, i + 33) = rst.Fields(i - 1).NameNext iSheets("报表").Range("ah4").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingColumns("AJ:AJ").SelectSelection.Insert Shift:=xlToRightColumns("AK:AK").SelectSelection.Insert Shift:=xlToRightRange("AJ3").SelectActiveCell.FormulaR1C1 = "款"Range("AK3").SelectActiveCell.FormulaR1C1 = "项"Application.ScreenUpdating = TrueEnd SubSub 单位()Application.ScreenUpdating = FalseCall 类Dim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT 单位, sum(专业股) as 专业股, sum(预算股) as 预算股 from [报表$ah3:am10000] group by 单位 "rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("报表").Cells(3, i + 40) = rst.Fields(i - 1).NameNext iSheets("报表").Range("ao4").CopyFromRecordset rstrst.CloseSet rst = NothingSet cnn = NothingApplication.ScreenUpdating = True Columns("AP:AP").SelectSelection.Insert Shift:=xlToRight Columns("AQ:AQ").SelectSelection.Insert Shift:=xlToRight Columns("AR:AR").SelectSelection.Insert Shift:=xlToRight Range("AP3").SelectActiveCell.FormulaR1C1 = "类"Range("AQ3").SelectActiveCell.FormulaR1C1 = "款"Range("AR3").SelectActiveCell.FormulaR1C1 = "项" End SubSub 报表()If Range("i2") = "类" ThenCall 类ElseIf Range("i2") = "款" ThenCall 款ElseCall 项End IfEnd SubSub 总报表()Application.ScreenUpdating = FalseDim i As IntegerDim strsql As StringDim cnn As New ADODB.ConnectionDim rst As New ADODB.Recordsetcnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;Hdr=Yes';Data Source=" & ThisWorkbook.FullNamestrsql = " SELECT 单位,类,款,项,专业股,预算股 from [报表$t3:y10000] union all SELECT 单位,类,款,项,专业股,预算股 from [报表$aa3:af10000] union all SELECT 单位,类,款,项,专业股,预算股 from [报表$ah3:am10000] union all SELECT 单位,类,款,项,专业股,预算股 from [报表$ao3:at10000] order by 单位 desc,类,款,项 "rst.Open strsql, cnnFor i = 1 To rst.Fields.CountSheets("报表").Cells(3, i) = rst.Fields(i - 1).NameNext iSheets("报表").Range("a4").CopyFromRecordset rstrst.Closecnn.CloseSet rst = NothingSet cnn = NothingRange("j1:br10000").ClearApplication.ScreenUpdating = TrueEnd Sub插入图片Sub 按钮48_单击() 宏按钮名，编码时自动生成On Error Resume NextDim MR As RangeFor Each MR In SelectionIf Not IsEmpty(MR) ThenMR.SelectML = MR.LeftMT = MR.TopMW = MR.WidthMH = MR.HeightActiveSheet.Shapes.AddShape(msoShapeRectangle, ML, MT, MW, MH).SelecterPicture _End IfNextEnd Sub与EXCEL表在同一个文件夹里，。

怎样在EXCEL中创建自定义菜单 vb源代码

怎样在EXCEL中创建自定义菜单vb源代码Sub addToolBar()Dim foundflag As Boolean'=====增加工具栏========foundflag = FalseFor Each ct In CommandBars("standard").Controls'Debug.Print ct.CaptionIf ct.Caption <> "myMenu：我的自定义菜单" ThenElsefoundflag = TrueEnd IfNextIf foundflag = False ThenSet newitem = CommandBars("standard").Controls.Add(Type:=msoControlButton, ID:=1, Before:=19)'--------------------------工具栏名称--------------类型----按钮--------ID为1自定义------位置--------With newitem'.Style = msoButtonIconAndCaption '同时显示图标和说明.Style = msoButtonIcon '仅显示按键图标.Caption = "myMenu：我的自定义菜单" '为按键写文字说明.OnAction = "showAbout" '指定工作的宏.FaceId = 459End WithEnd IfEnd SubSub addMenu()Dim foundflag As Boolean'=====增加菜单栏========foundflag = FalseFor Each ct In CommandBars("Worksheet Menu Bar").Controls'Debug.Print ct.CaptionIf ct.Caption <> "我的自定义菜单(&A)" ThenElsefoundflag = TrueEnd IfNextIf foundflag = False ThenSet newMenu = CommandBars("Worksheet MenuBar").Controls.Add(Type:=msoControlPopup, ID:=1, Before:=8)'--------------------------工具栏名称--------------类型----按钮--------ID为1自定义------位置--------With newMenu.Caption = "我的自定义菜单(&A)" '为按键写文字说明'.Controls.Add Type:=msoControlPopup, ID:=1 '再加入一层表单'========加这个按键就不判断了，直接加，今后有扩展再另做方法Set AboutMenu = .Controls.Add(Type:=msoControlButton, ID:=1) '下拉菜单也是按钮With AboutMenu.Caption = "关于我的菜单(&A)".Style = msoControlIconAndCaption.OnAction = "showAbout".FaceId = 459.BeginGroup = True '画上一线条End WithSet nuinstallMenu = .Controls.Add(Type:=msoControlButton, ID:=1)With nuinstallMenu '复原.Caption = "卸载自定义菜单(&U)".Style = msoControlIconAndCaption.OnAction = "uninstall".FaceId = 330End WithEnd WithEnd IfEnd SubSub AddrightMenu()'==========增加右键菜单========Dim foundflag As Booleanfoundflag = FalseFor Each ct In CommandBars("cell").Controls'Debug.Print ct.CaptionIf ct.Caption <> "我的自定义菜单(&A)" ThenElsefoundflag = TrueEnd IfNextIf foundflag = False ThenSet newMenu = CommandBars("cell").Controls.Add(Type:=msoControlPopup, ID:=1)'--------------------------工具栏名称--------------类型----按钮--------ID为1自定义------位置--------With newMenu.Caption = "我的自定义菜单(&A)" '为按键写文字说明' .FaceId = 577 因为这是个多重菜单，所以没有图标，如果要图标，就只能一层，用按键.BeginGroup = True '画上一线条Set nextMenu = .Controls.Add(Type:=msoControlButton, ID:=1)With nextMenu.Caption = "关于我的菜单(&A)".Style = msoControlIconAndCaption.OnAction = "showAbout".FaceId = 459End WithEnd WithEnd If'mandBars("PivotTable Context Menu").ResetEnd SubSub showAbout()f_About.Show vbModalEnd SubSub rightMenuReset()'右键菜单复位mandBars("cell").ResetEnd SubSub uninstall()'====卸载=====If MsgBox("你确认要卸载[我的自定义菜单]吗？", vbOKCancel + vbQuestion, "YiDie提醒您：") = vbOK ThenmandBars("cell").ResetmandBars("Worksheet Menu Bar").ResetmandBars("standard").ResetMsgBox "菜单已复原！", vbOKOnly + vbInformation, "YiDie提醒您："ElseMsgBox "卸载操作已取消", vbOKOnly + vbInformation, "YiDie提醒您："End IfEnd Sub。

表格模板白程序 VB源码

excel vba常用代码

vba精典源程序源代码(工作表操作)

VB操作Excel报表(实例、图例、源码、注释)

使用VB做的报表源码

vb简单的计算机源代码

vba编程代码大全

vb excel编程实例

Excel VBA代码 亲测可用

VBnet操做Excel的代码

用VB操作Excel（VB6.0）（整理）

vba精典源程序源代码(工作表操作)

VB对Excel文件的操作代码

VBA代码全集模板

怎样在EXCEL中创建自定义菜单 vb源代码

Excel VBA代码亲测可用