EXCEL使用VBA宏生成SQL语句

合集下载

Excel如何用VBA批量添加及更新SQL数据库中的数据

Excel如何用VBA批量添加及更新SQL数据库中的数据

Excel如何用VBA批量添加及更新SQL数据库中的数据
各位高手朋友,谁知道怎么在excel中,用VBA实现将excel工作表的数据,批量添加/修改到SQL数据库中去,数据库是SQL2008,excel是2007版。

假如,excel中得数据表名称为“sheet1”,有3列数据:学号、姓名、专业。

其中,学号是唯一的,不重复。

SQL数据库名字为:mydata,表名为:mytable。

mytable中存有的数据与上述sheet1中的格式相同。

现在需要实现下述功能:
将sheet1中的数据更新至mytable中,如果经过判断,mytable 中存在相同学号,则进行覆盖修改(对于同一个学号,sheet1中的其它信息可能跟SQL中不同,所以需要覆盖更新);如果经过判断,mytable中没有相同学号,则在SQL中新增记录。

请高手帮忙,非常感谢。

ExcelVBAADOSQL实例集锦

ExcelVBAADOSQL实例集锦

ExcelVBAADOSQL实例集锦1, 包含空值的记录f13 is null‘‘订单生成系统.xls‘f6-第6列,f2-第2列Private Sub Worksheet_Activate()On Error Resume NextDim x As Object, yy As Object, sql As StringSet x = CreateObject("ADODB.Connection")x.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no;';Data Source=" & ActiveWorkbook.FullNamesql = "select f6,f2,f3,f4,f5,f7,f13,f24 -f25 from [sheet1$] where f24 -f25<="" and="" bdsfid="72">'C3' or f13 is null)" ‘不等于字符串用‘C3’包含空值用is nullSet yy = x.Execute(sql)Range("a:h").ClearContentsRange("a1:h1") = Array("编号", "品名", "规格", "产地", "单位", "件装", "属性", "计划") ‘表头另外赋值[a2].CopyFromRecordset yySet yy = NothingSet x = NothingEnd Sub2,用ADO Connection对象查询Option ExplicitPublic conn As ADODB.ConnectionSub Myquery()Dim sConnect$, sql1$Set conn = CreateObject("adodb.connection")Sheets("sheet1").Cells.ClearContentssConnect = "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;" & _"Data Source=" & ThisWorkbook.Path & "\" & /doc/0317259515.html, sql1 = "select 物料代码,物料描述,属性,单位from [物料代码表$] where 属性= '采购' " '表格名要用[$],条件部分用单引号''ThisWorkbook.Sheets("sheet1").Cells(2,1).CopyFromRecordset conn.Execute(sql1) 'copy 后面紧接SQL查询执行语句With Sheets("sheet1").Range("A1") = "物料代码" '建立表头.Range("B1") = "物料描述".Range("C1") = "属性".Range("D1") = "单位"End With'conn.Close '可不用每次关闭数据源的连接End Sub3,用记录集执行单个查询Option ExplicitSub Myquery()Dim rd As ADODB.RecordsetDim i%, j%, k%, sConnect$, sql1$, str$Set rd = New ADODB.Recordsetstr = "外协"Sheets("sheet1").Cells.ClearContentssConnect = "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;" & _"Data Source=" & ThisWorkbook.Path & "\" & /doc/0317259515.html,'conn.Open sConnect '打开数据源sql1 = "select 物料代码,物料描述,属性,单位from [物料代码表$] where 属性= '采购' " '表格名要用[$],条件部分用单引号'' rd.Open sql1, sConnect, adOpenForwardOnly, adLockReadOnlyThisWorkbook.Sheets("sheet1").Cells(2,1).CopyFromRecordset rdWith Sheets("sheet1").Range("A1") = "物料代码" '建立表头.Range("B1") = "物料描述".Range("C1") = "属性".Range("D1") = "单位"End Withrd.Close '关闭记录集Set rd=Nothing '关闭End Sub4,引用一列,如A列‘引用单列、单行、单个单元格.xls'引用一列,如A列Sub onecolumn()Dim Sql$Set Conn = CreateObject("Adodb.Connection")Conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no';data source=" & ThisWorkbook.Path & "\1.xls"Sql = "select f1 from [sheet1$]"Cells.Clear[a1].CopyFromRecordset Conn.Execute(Sql)Conn.CloseSet Conn = NothingEnd SubSub dgzbhz()'2008/12/2‘‘Book12021.xls‘由于分表的第2列表头是“金额”,不用它,改为“一中”,所以要用hdr=no无标题,拷贝时把第一行表头归零,所以最后要加表头。

excel vba sql语句示例

excel vba sql语句示例

excel vba sql语句示例Excel VBA中SQL语句示例-以中括号为主题在Excel VBA中,SQL(Structured Query Language)是一种用于管理关系数据库的语言。

它允许用户从数据库中检索数据,更新和删除数据,并与数据库进行交互。

中括号[]在SQL语句中用于标识数据表或字段名称。

本文将介绍几个常用的Excel VBA中使用SQL语句并涉及中括号的示例。

1.查询数据表中所有字段使用SELECT语句可以从数据表中选择一条或多条记录。

要选择所有字段,可以使用“*”或字段列表。

使用“*”选取所有字段非常方便,但不建议在大型数据表中使用。

以下是一个示例,它使用“*”选取数据表中的所有字段。

Sub SelectAllFields()'Define variablesDim cn As ADODB.ConnectionDim rs As ADODB.RecordsetDim strQuery As String'Open connection to the databaseSet cn = New ADODB.Connectioncn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Documents\Database.accdb;Persist Security Info=False;"cn.Open'Create SQL query to select all fields from the data tablestrQuery = "SELECT * FROM [Data Table]"'Execute the query and store the result in a recordsetSet rs = cn.Execute(strQuery)'Close the database connectioncn.CloseSet cn = NothingEnd Sub2.根据条件查询数据表中的记录使用WHERE子句可以根据指定的条件从数据表中检索一条或多条记录。

excel文件转成sql的函数

excel文件转成sql的函数

excel文件转成sql的函数在Excel中,可以通过编写VBA宏来将数据转换为SQL语句。

下面是一个示例函数,它可以读取Excel文件中的数据并生成相应的INSERT语句。

```Sub ConvertExcelToSQL()Dim conn As ObjectDim rs As ObjectDim sConnString As StringDim strSQL As StringDim i As Integer, j As IntegerDim sSheet As StringDim sDataRange As StringDim sTableName As String' 设置连接字符串(这里使用的是Microsoft OLE DB Provider for SQL Server)sConnString = "Provider=SQLOLEDB;DataSource=ServerName;Initial Catalog=DatabaseName;UserID=UserName;Password=Password;"' 设置要读取数据的工作表名称、数据范围和目标表名称sSheet = "Sheet1"sDataRange = "A1:C10"sTableName = "TableName"' 创建连接对象和记录集对象Set conn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")' 打开连接conn.Open sConnString' 选择要操作的工作表strSQL = "SELECT * FROM [" & sSheet & "$" & sDataRange & "]"' 执行查询语句rs.Open strSQL, conn' 生成INSERT语句strSQL = "INSERT INTO " & sTableName & " VALUES "' 遍历查询结果,生成INSERT语句的值部分While Not rs.EOFstrSQL = strSQL & "("For j = 0 To rs.Fields.Count - 1strSQL = strSQL & "'" & rs.Fields(j).Value & "',"Next j' 删除最后一个逗号strSQL = Left(strSQL, Len(strSQL) - 1) & "),"rs.MoveNextWend' 删除最后一个逗号strSQL = Left(strSQL, Len(strSQL) - 1)' 执行插入语句conn.Execute strSQL' 关闭连接和记录集rs.Closeconn.CloseSet rs = NothingSet conn = NothingEnd Sub```要使用这个函数,可以在Excel中按下`Alt + F11`打开VBA编辑器,然后选择`插入`菜单中的`模块`,将上述代码粘贴到模块中。

VBA与SQL语句的结合与应用实例

VBA与SQL语句的结合与应用实例

VBA与SQL语句的结合与应用实例在现代信息化时代,数据处理已经成为各个行业中不可或缺的一环。

在处理大量数据时,使用Excel和SQL数据库是非常常见的选择。

而结合VBA(Visual Basic for Applications)和SQL语句,可以将两者的优势发挥到极致,提高数据处理的效率和准确性。

本文将通过一些实例来展示VBA与SQL语句的结合与应用。

案例一:数据导入与清洗假设我们有一个存储了客户订单的Excel表格,我们需要将其中的数据导入到SQL数据库中进行进一步处理。

这时,我们可以使用VBA编写一个宏来实现自动将Excel中的数据导入到数据库表中。

首先,我们需要在Excel中添加一个按钮,通过宏来触发数据导入的操作。

然后,我们可以使用VBA代码来连接到数据库,并执行相应的SQL语句将数据导入。

示例代码如下:```vbaSub ImportDataToSQL()Dim conn As ObjectDim rs As ObjectDim strSQL As StringDim rng As RangeDim cell As RangeSet conn = CreateObject("ADODB.Connection")conn.ConnectionString = "Provider=<provider>; Data Source=<data_source>; Initial Catalog=<catalog>; User ID=<user_id>; Password=<password>"conn.OpenSet rng = ThisWorkbook.Sheets("Sheet1").Range("A2:D10") ' 假设数据范围为A2:D10strSQL = "INSERT INTO TableName (Column1, Column2, Column3, Column4) VALUES (?,?,?,?)"For Each cell In rngSet rs = CreateObject("ADODB.Recordset")rs.Open strSQL, connrs.AddNewrs.Fields("Column1").Value = cell.Offset(0, 0).Valuers.Fields("Column2").Value = cell.Offset(0, 1).Valuers.Fields("Column3").Value = cell.Offset(0, 2).Valuers.Fields("Column4").Value = cell.Offset(0, 3).Valuers.Updaters.CloseSet rs = NothingNext cellconn.CloseSet conn = NothingEnd Sub```在上述示例代码中,我们需要替换掉连接字符串中的`<provider>`、`<data_source>`、`<catalog>`、`<user_id>`和`<password>`,以便正确连接到目标数据库。

ExcelVBA导入Excel中数据到SQLServer中

ExcelVBA导入Excel中数据到SQLServer中

[方法一]如何把EXC‎EL中的数据‎导入SQL SERVER‎数据库中(2010-12-30 17:16:53)有时候需要把‎E XCEL中‎的数据导入到‎数据库中。

一条一条的在‎数据库中建数‎据显然不可取‎,如何找一条快‎捷的途径进行‎转换是很有必‎要的。

在这里主要是‎介绍一种在E‎X CEL中用‎V BA进行编‎程,把EXCEL‎的数据转换成‎S QL语句,存入到一个文‎件中,然后在数据库‎服务器上提交‎这些SQL语‎句来实现。

一、两边的数据格‎式(一)数据库的表结‎构生成数据库表‎的SQL语句‎如下:create‎table Addres‎s(ID Intege‎r identi‎t y(1,1) not null primar‎y key,Name varcha‎r(20) not null,Dept varcha‎r(50),Spell varcha‎r(20),Mobile‎ varcha‎r(11),Tel varcha‎r(20),EMail varcha‎r(30),VOIP varcha‎r(6),Remark‎ varcha‎r(200),);EXCEL中‎的数据格式(二)转换的VBA‎函数Sub Genera‎t eSQL()Dim i, k As LongDim s As String‎Open "d:\txl.sql" For Output‎As #1 '打开一个文件‎以供输入SQ‎L语句i = 2While Cells(i, 1) <> ""s = "INSERT‎INTO ADDRES‎S (Name,Dept,Mobile‎,Tel,VOIP,EMail) VALUES‎("For k = 1 To 5s = s & "'" & Cells(i, k) & "',"Nexts = s & "'" & Cells(i, 6) & "');"Print #1, s '输出一条语句‎至文件i = i + 1WendClose #1 '关闭文件End Sub以上的代码用‎于逐行把数据‎转换成SQL‎语句写入“d:\txl.sql”中。

把excel数据生成sqlinsert语句(原)

把excel数据生成sqlinsert语句(原)

把excel数据生成sqlinsert语句(原)
实际应用中,我们经常需要把Excel中的数据导入MS SQL Server 或MySQL Oracle等数据库中。

这些数据库都提供了很好的工具供用户直接将Excel中的数据导入数据库中。

但是很多时候我们并不能直接操作数据库管理器(因为安全需要会配置防火墙拦截数据库端口),我们只能通过web页面对数据库进行远程操作。

这个时候,将Excel中的数据转换成sql Insert语句就非常有必要了。

例子:
excel表格中有A、B、C三列数据,希望导入到数据库users表中,对应的字段分别是name,sex,age 。

在你的excel表格中增加一列,利用excel的公式自动生成sql语句,方法如下:
1、增加一列(D列)
2、在第一行的D列,就是D1中输入公式:=CONCATENATE("insert into users (name,sex,age) values ('",A1,"','",B1,"','",C1,"');")
3、此时D1已经生成了如下的sql语句:insert into users (name,sex,age) values ('ls','女','24');
4、将D1的公式复制到所有行的D列
5、此时D列已经生成了所有的sql语句
6、把D列复制到一个纯文本文件中。

注意:生成的insert语句中有一个特殊字符"?",需要转换成空格。

推荐使用UltraEdit进行处理。

通过Excel生成批量SQL语句(Excel快速生成SQL更新语句)

通过Excel生成批量SQL语句(Excel快速生成SQL更新语句)

通过Excel⽣成批量SQL语句(Excel快速⽣成SQL更新语句)们经常会遇到这样的要求:⽤户给发过来⼀些数据,要我们直接给存放到数据库⾥⾯,有的是Insert,有的是Update等等,少量的数据我们可以采取最原始的办法,也就是在SQL⾥⾯⽤Insert into来实现,但是如果有⼏⼗条⼏百条甚⾄上千条数据的时候继续写单独的SQL语句的话那就惨了,其实有两种简单的⽅法;第⼀,将Excel数据整理好了之后,通过SQL的导⼊功能直接导进数据库,但是得保证数据库字段和Excel的字段⼀致。

第⼆,通过Excel来⽣成对应的SQL语句,直接将SQL语句复制到分析器⾥⾯执⾏即可,本⽂就说⼀下如何来实现这第⼆种办法。

⾸先看下图,我们的⽬的就是将这20条数据Insert到数据库⾥⾯去,⼀条两条的话可以⾃⼰写Insert语句,这⾥有20条数据,总不能完全⼿写20条语句出来吧,很显然,不能⼀条⼀条的去写SQL了,太多了,这⾥还只有20条,如果是200条,2000条数据呢?INSERT INTO TableName(Column1,Column2,Column3) VALUES('Value1','Value2','Value3')写出⼀条语句之后,直接从头拉到尾,你会发现所有的数据都有对应的脚本了,这个时候你便可以直接复制到分析器,按⼀下"F5",OK,你的任务完成了。

因为在公式⾥⾯,所以有时候那些语句会变化,当你⽣成这些语句之后,你可以选择性的粘贴为数值,然后再放到SQL⾥⾯去执⾏,如下:好了,以上是我的⼀点⼉⼩经验,希望对⼤家有⽤,只有互相分享才能得到提⾼,如果您觉得还⾏的话请帮忙顶⼀下,谢谢!下⾯补充是启源分享的Excel快速⽣成SQL更新语句供应商调整了产品信息,我们的业务系统需要进⾏同步。

运维部同事已经把产品新的产品信息发过来。

如图:虽然后台可以调整参数,但是竟然有⼏百个产品都更新了,作为程序员的我们当然不会傻傻的去挨个调整,使⽤SQL语句⼏分钟就搞定,⽽且还不出错。

vba-sql

vba-sql

对于不少的朋友来说,Excel可能还是在用最基本的功能,但就是最基本的功能,已能帮助我们完成很多的工作,一般的Excel网站,把Excel知识分成——基础知识,公式与函数,图表与数据透视表和VBA程序开发。

其它方面在这不谈,Excel就是因为用VBA,这个平民化的编程平台,而让广大的使用者开发出了相当多的实用小程序,甚至是仅仅用录制宏和稍稍的修改,就可以做成一段非常有用的小程序。

就是因为这个吧,连WPS从2005版都不惜重写代码来支持VBA,背着模仿者的骂名。

而VBA的强大,还不仅仅如此,Excelhome的VBA程序板块中的这句话,应该更能让你体会到其中的意义——认识宏,学习从Excel VBA基础入门到设计制作模板、加载宏,交流各种VBA方法、VBA属性、VBA事件、窗体控件、Excel对象的使用技巧,以及借助API、ADO、SQL构建完整软件系统。

把Excel当成是数据库软件是不正确的,也是不公平的。

它有它相对于数据库易上手,易处理,可视化,简单易用的优点,但也有数据库没有的对大量数据处理,与数据与数据间关系统和引用不便的缺点。

虽然Excel2007版的单个工作表文件已缯加了行与列,但对于大量的数据处理,还是远远不如数据库的。

因此,由于最近的工作需要,接触与了解了一点SQL与数据库方面的知识,所以就以Excel 为程序的前台,用来录入与输出数据或报表,而利用ACCESS数据库来保存数据,最后用VBA+SQL与ADO 来处理数据与联接Excel与数据库,解决了以前对大量数据,多人同时处理数据,数据间统计,对比,引用,关联等诸多以前没办法解决的难题。

因此就把自己使用的心得与大家一起分享,一起学习进步。

千里之行,始于足下,接下来的时间,我用不是很正规,也不是很专业,也不是很严肃,更不是很深奥的语言,来试着带领大家进入这个看似与Excel无关的世界,而这第一步,我们要认识的,就是要认识SQL语言中各个SQL语法,SQL函数与SQL指令。

Excel VBAADOSQL实例集锦

Excel VBAADOSQL实例集锦

1, 包含空值的记录f13 is null‘‘订单生成系统.xls‘f6-第6列,f2-第2列Private Sub Worksheet_Activate()On Error Resume NextDim x As Object, yy As Object, sql As StringSet x = CreateObject("ADODB.Connection")x.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no;';Data Source=" & ActiveWorkbook.FullNamesql = "select f6,f2,f3,f4,f5,f7,f13,f24 -f25 from [sheet1$] where f24 -f25<f17 and (f13<>'C3' or f13 is null)" ‘不等于字符串用‘C3’包含空值用is nullSet yy = x.Execute(sql)Range("a:h").ClearContentsRange("a1:h1") = Array("编号", "品名", "规格", "产地", "单位", "件装", "属性", "计划") ‘表头另外赋值[a2].CopyFromRecordset yySet yy = NothingSet x = NothingEnd Sub2,用ADO Connection对象查询Option ExplicitPublic conn As ADODB.ConnectionSub Myquery()Dim sConnect$, sql1$Set conn = CreateObject("adodb.connection")Sheets("sheet1").Cells.ClearContentssConnect = "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;" & _"Data Source=" & ThisWorkbook.Path & "\" & sql1 = "select 物料代码,物料描述,属性,单位from [物料代码表$] where 属性= '采购' " '表格名要用[$],条件部分用单引号''ThisWorkbook.Sheets("sheet1").Cells(2, 1).CopyFromRecordset conn.Execute(sql1) 'copy 后面紧接SQL查询执行语句With Sheets("sheet1").Range("A1") = "物料代码" '建立表头.Range("B1") = "物料描述".Range("C1") = "属性".Range("D1") = "单位"End With'conn.Close '可不用每次关闭数据源的连接End Sub3,用记录集执行单个查询Option ExplicitSub Myquery()Dim rd As ADODB.RecordsetDim i%, j%, k%, sConnect$, sql1$, str$Set rd = New ADODB.Recordsetstr = "外协"Sheets("sheet1").Cells.ClearContentssConnect = "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;" & _"Data Source=" & ThisWorkbook.Path & "\" & 'conn.Open sConnect '打开数据源sql1 = "select 物料代码,物料描述,属性,单位from [物料代码表$] where 属性= '采购' " '表格名要用[$],条件部分用单引号''rd.Open sql1, sConnect, adOpenForwardOnly, adLockReadOnlyThisWorkbook.Sheets("sheet1").Cells(2, 1).CopyFromRecordset rdWith Sheets("sheet1").Range("A1") = "物料代码" '建立表头.Range("B1") = "物料描述".Range("C1") = "属性".Range("D1") = "单位"End Withrd.Close '关闭记录集Set rd=Nothing '关闭End Sub4,引用一列,如A列‘引用单列、单行、单个单元格.xls'引用一列,如A列Sub onecolumn()Dim Sql$Set Conn = CreateObject("Adodb.Connection")Conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no';data source=" & ThisWorkbook.Path & "\1.xls"Sql = "select f1 from [sheet1$]"Cells.Clear[a1].CopyFromRecordset Conn.Execute(Sql)Conn.CloseSet Conn = NothingEnd SubSub dgzbhz()'2008/12/2‘‘Book12021.xls‘由于分表的第2列表头是“金额”,不用它,改为“一中”,所以要用hdr=no无标题,拷贝时把第一行表头归零,所以最后要加表头。

使EXCEL中使用宏生成T-SQL实现数据导入

使EXCEL中使用宏生成T-SQL实现数据导入

使EXCEL中使⽤宏⽣成T-SQL实现数据导⼊有时我们需要从EXCEL⽂档中把数据导⼊到数据库,这时我们可以使⽤Excel的宏功能。

假设如下图这样的DEMO数据:然后通过“视图”找到如下选项:接着我们创建⼀个宏,在编辑器中输⼊(Vbscript):Sub CreateInsertScript()Dim Row As IntegerDim Col As Integer'To store all the columns available in the current active sheetDim ColNames(100) As StringCol = 1Row = 1Dim ColCount As IntegerColCount = 0'Get Columns from the sheetDo Until ActiveSheet.Cells(Row, Col) = ""'Loop until you find a blank.ColNames(ColCount) = "[" + ActiveSheet.Cells(Row, Col) + "]"ColCount = ColCount + 1Col = Col + 1LoopColCount = ColCount - 1'Inputs for the starting and ending point for the rowsRow = InputBox("Give the starting Row No.")Dim MaxRow As IntegerMaxRow = InputBox("Give the Maximum Row No.")'File to save the generated insert statementsFile = "c:\\InsertCode.txt"fHandle = FreeFile()Open File For Output As fHandleDim CellColCount As IntegerDim StringStore As String'Temporary variable to store partial statementDo While Row <= MaxRowStringStore = ""CellColCount = 0' will give the current active sheet name'this can be treated as table name in the databaseStringStore = StringStore + "insert into [" + + "] ( "Do While CellColCount <= ColCountStringStore = StringStore + ColNames(CellColCount)'To avoid "," after last columnIf CellColCount <> ColCount ThenStringStore = StringStore + " , "End IfCellColCount = CellColCount + 1Loop'Here it will print "insert into [TableName] ( [Col1] , [Col2] , ..."Print #fHandle, StringStore + " ) "'For printing the values for the above columnsStringStore = " values( "CellColCount = 0Do While CellColCount <= ColCountStringStore = StringStore + " '" + CStr(ActiveSheet.Cells(Row, CellColCount + 1)) + "'"If CellColCount <> ColCount ThenStringStore = StringStore + ", "End IfCellColCount = CellColCount + 1Loop'Here it will print "values( 'value1', 'value2', ..."Print #fHandle, StringStore + ");"Print #fHandle, " "Row = Row + 1LoopClose #fHandleMsgBox ("Successfully Done")End Sub接着点击运⾏,好了弹出两个对话框,输⼊起始⾏2,结束⾏5,确定后在⽣成⼀个⽂本⽂件,这些参数你可以修改的 c:\\InsertCode.txt内容是我们最终想要的T-SQL:insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] ) values( 'Peter', '23', '2009-01-01', '3003.5');insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] ) values( 'Lucy', '21', '2003-10-01', '2087.65');insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] ) values( 'Max', '29', '2011-01-01', '1989.11');insert into [Person] ( [Name ] , [Age] , [EnterTime] , [Salary] ) values( 'Eric', '35', '1999-05-01', '5043.2');很简单,您可以⾃⼰动⼿试⼀下。

excel转sql建表语句

excel转sql建表语句

excel转sql建表语句
将Excel表格转换成SQL建表语句可以通过以下步骤实现:
1. 首先,打开Excel表格,确保表格中的数据是按照列进行组织的,每列代表一个字段,每行代表一个记录。

2. 确定每个字段的数据类型,例如文本、数字、日期等。

这将有助于确定在SQL表中为每个字段定义合适的数据类型。

3. 在Excel中,将第一行作为表的字段名,确保每个字段有一个唯一的名称。

4. 接下来,将数据从Excel表格中导出为CSV文件,以便稍后导入到数据库中。

5. 打开你喜欢使用的SQL数据库管理工具,例如MySQL Workbench、SQL Server Management Studio等。

6. 在数据库管理工具中创建一个新的数据库或选择一个现有的数据库,用于存储从Excel表格中导入的数据。

7. 利用数据库管理工具提供的导入功能,将CSV文件中的数据导入到数据库中,这将创建一个包含Excel数据的新表。

8. 最后,使用SQL语句查询新创建的表,以便生成相应的建表语句。

例如,对于MySQL数据库,可以使用如下语句:
sql.
SHOW CREATE TABLE table_name;
这条SQL语句将返回创建指定表的SQL语句,包括表的结构和约束等信息。

综上所述,将Excel表格转换成SQL建表语句的关键步骤包括确定字段数据类型、导出数据到CSV文件、导入数据库并查询生成建表语句。

希望这些步骤能够帮助你成功地将Excel数据转换成SQL建表语句。

VBA中模块化调用SQL

VBA中模块化调用SQL

VBA中模块化调用SQLVBA中模块化调用SQLExcel通过ADO组件可以连接并查询SQL数据库,因为常用的服务器就那么一个或两个,一般写法中多次出现定义对象–连接数据库–执行查询–输出结果这个过程,非常繁琐,面对大块的代码还容易出错。

可以先定义好一个连接模块,要调用时只需要写几行相应的sql语句即可,大大减少工作量。

定义模块如下:Option ExplicitPublic Const Str_Conn = "Provider=SQLOLEDB.1;server=192.168.1.50;user id =sqlid;password=pwd888;database=opdata"Public conn As ADODB.ConnectionPublic Rs As ADODB.RecordsetPublic StrSQL As StringPublic Sub ConnectDB()Set conn = New ADODB.Connectionconn.Open Str_ConnEnd Sub调用:Private Sub CommandButton1_Click()Set Rs = New ADODB.RecordsetCall ConnectDBStrSQL = "SELECT * FROM [OPSDATA].[dbo].[test]"Rs.Open StrSQL, conn, 1, 1'提取数据,复制到Sheets1.A2单元格Sheets(1).Range("a2").CopyFromRecordset RsRs.Closeconn.CloseSet Rs = Nothing Set conn = Nothing End Sub。

VBA中的数据库操作与SQL语句

VBA中的数据库操作与SQL语句

VBA中的数据库操作与SQL语句VBA(Visual Basic for Applications)是一种广泛应用于Microsoft Office平台的编程语言,它允许用户通过编写宏来自动化处理文档、电子表格和数据库等内容。

在VBA中,数据库操作是一项非常常见和重要的任务。

通过使用SQL语句(Structured Query Language),我们可以进行各种数据库操作,包括数据查询、插入、更新和删除等。

本文将介绍如何在VBA中进行数据库操作,并使用SQL语句实现不同的功能。

我们将重点讨论以下几个方面:连接数据库、查询数据、插入数据、更新数据和删除数据。

1. 连接数据库在VBA中连接数据库可以使用ADODB对象模型。

首先,需要创建一个ADODB.Connection对象来进行连接。

在连接之前,需要确定使用的数据库类型,并检查计算机是否已安装相应的数据库驱动。

例如,如果使用的是Microsoft Access数据库,则需要安装Access数据库驱动。

另外,还需要提供数据库的连接字符串,该字符串包含数据库类型、路径、用户名和密码等信息。

下面是一个连接Access数据库的示例:```vbaDim conn As ObjectSet conn = CreateObject("ADODB.Connection")conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydatabase.accdb;User Id=myUsername;Password=myPassword;"conn.Open```2. 查询数据查询数据是数据库操作中最常见的任务之一。

在VBA中,我们可以通过执行SELECT语句来实现数据查询。

首先,需要创建一个ADODB.Recordset 对象来接收查询结果。

Excel使用VBA宏生成SQL语句

Excel使用VBA宏生成SQL语句

有时候需要将Excel文件中的数据导入到数据库中,常用的做法是使用程序读取Excel,然后存入数据库;这里换一个方法,用Excel生成SQL语句,然后用这些SQL语句来更新数据库。

本文主要说明Excel如何使用VB宏构造需要的SQL语句,并生成文件。

2、如何添加按钮:依次打开:视图–工具栏–控件工具箱,选择“命令按钮”,自己画一个就行了;依次打开:右键–属性,可以修改名称、样式等。

具体操作可以上网找找。

3、如何使用VB宏:依次打开:工具–宏–安全性,看看你的Excel是否允许使用“宏”。

然后打开:工具–宏–Visual Basic 编辑器,双击左侧的“Sheet1”,然后在右侧的代码区域粘贴如下代码:'最大行数Const MAX_NUM_ROW = 5000'导出文件路径所在单元格Const PATH_OUTPUT_ROW = 3Const PATH_OUTPUT_COL = 3'定义列常量Const NAME_COL = 1Const GENDER_COL = 2Const PHONE_COL = 3Const EMAIL_COL = 4'读取数据开始行数Const START_ROW = 5'定义数据实体类Private Type TmpltNAME As StringGENDER As StringPHONE As StringEMAIL As StringEnd Type'行数变量Dim noOfTmplts As Integer'数据实体类数组Dim TmpltArray(MAX_NUM_ROW) As Tmplt'点击按钮触发事件Private Sub CommandButton1_Click()generateSQLEnd Sub'生成SQLPrivate Sub generateSQL()makedirinitDatawriteToFileEnd Sub'构建文件输出路径Private Sub makedir()On Error Resume NextMkDir Sheet1.Cells(PATH_OUTPUT_ROW, PA TH_OUTPUT_COL)End Sub'读取Excel数据,填充实体类数组Private Sub initData()Erase TmpltArraynoOfTmplts = 0Dim j As Integer'循环读取Excel数据行For j = START_ROW To MAX_NUM_ROWTmpltArray(noOfTmplts).NAME = Sheet1.Cells(j, NAME_COL) TmpltArray(noOfTmplts).GENDER = Sheet1.Cells(j, GENDER_COL) TmpltArray(noOfTmplts).PHONE = Sheet1.Cells(j, PHONE_COL) TmpltArray(noOfTmplts).EMAIL = Sheet1.Cells(j, EMAIL_COL) noOfTmplts = noOfTmplts + 1NextEnd Sub'读取实体类数组,生成SQL并写入文件Private Sub writeToFile()Dim lvOutputPath As String'输出文件路径lvOutputPath = Sheet1.Cells(PATH_OUTPUT_ROW, PA TH_OUTPUT_COL) If lvOutputPath = "" ThenMsgBox "没有找到输出文件路径!"Exit SubEnd IffileNum = FreeFile'打开输出文件Open lvOutputPath For Output As fileNumDim lvUserSql As StringDim nameStr As StringDim genderStr As StringDim phoneStr As StringDim emailStr As String'循环生成SQLFor j = 0 To noOfTmplts - 1nameStr = TmpltArray(j).NAMEgenderStr = TmpltArray(j).GENDERphoneStr = TmpltArray(j).PHONEemailStr = TmpltArray(j).EMAILIf nameStr <> "" ThenlvUserSql = "Insert into Students(name,gender,phone,email) values('" & nameStr & "','" & genderStr & "','" & phoneStr & "','" & emailStr & "');"Print #fileNum, lvUserSqlEnd IfNextClose fileNumMsgBox "文件生成完成!"Exit SubErr_Open_File:Close lvFileNumIf Err.Number = 76 Then'路径未找到MsgBox Err.DescriptionExit SubElseMsgBox Err.DescriptionExit SubEnd IfEnd SubOK,这样就可以了!点击你的按钮,看看生成文件了吗?如果按钮还是编辑状态,关了再打开就行了。

使用excel中的数据快速生成sql语句

使用excel中的数据快速生成sql语句

使⽤excel中的数据快速⽣成sql语句在⼩公司的话,总是会有要开发去导⼊历史数据(数据从旧系统迁移到新系统上)的时候。

这个时候,现场实施或客户会给你⼀份EXCEL⽂档,⾥⾯包含了⼀些别的系统上的历史数据,然后就让你导⼊到现在的系统上⾯去。

且不说表数据结构的不同了,这个协调过程就能⽤⼤量的篇幅去说故事,这⾥只假设已经将别的系统上的历史数据整理成了适合导⼊当前系统的数据。

如何⾼效地完成这项导⼊⼯作,就是这⾥讨论的议题:使⽤EXECL表格中的数据快速⽣成SQL语句。

因为导⼊数据就是往表⾥⾯插⼊新数据,因此⽤的是INSERT语句。

INSERT INTO TABLE(COLUMN1, COLUMN2, ...) VALUES(VALUE1, VALUE2, ...);那么快速⽣成这些INSERT语句的秘诀是什么?答案就是EXCEL中的强⼤的公式和快速⾃动填充。

这⾥⽤⼀个简单结构的user表做⼀个简单的例⼦。

user表中有两个字段,⼀个是code,⼀个是name。

在给定的EXCEL中,这两个字段分别对应列C和列D,数据从第2⾏开始,那么我们就可以从第2⾏开始写,在第2⾏的最后新增⼀列,⽤于存放公式。

贴出公式来:=CONCATENATE("insert into user(code, name) values('",C2, "','", D2, "');")然后点击回车就会根据公式⽣成对应的INSERT语句了。

然后,重点来了,将⿏标悬停在当前公式单元格的右下⾓,当右下⾓变成加号时,往下拖动,系统会⾃动填充这些公式了。

最后把这些⽣成好的INSERT语句拷贝到控制台⼯具或数据库⼯具中去执⾏就好了,EXCEL中选中当前列往下所有有内容的单元格快捷键是【ctrl+shift+↓】,然后使⽤cv⼤法就⾏了。

当EXCEL中存在成千上万条数据的时候,效率就体现出来了。

Excel2007VBASQL代码解析fromExcelhome

Excel2007VBASQL代码解析fromExcelhome

Excel2007VBASQL代码解析fromExcelhomeSub Example()Dim cnn As ADODB.ConnectionSet cnn = New ADODB.Connection'创建连接设置字符串,用OLEDB方式连接Dim lian As Stringlian = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;" _& "HDR=yes;" _& "IMEX=2';" _& "Data Source=" & ThisWorkbook.FullName'HDR=Yes,代表Excel 档中的工作表第一行是标题栏,No,代表Excel 档中的工作表无标题行'IMEX 汇入模式 0 只读 1 只写 2 可读写'Data Source 存储查询数据来源的工作薄名称'Provider 连接方式或连接提供程序 Extended Properties 连接方式的扩展属性、证书'Extended Properties='Excel 12.0 ---EXECL 2007'Extended Properties='Excel 8.0 ---EXECL 2003cnn.Open lian' ' 创建连接设置字符串,用ODBC方式打开连接.' Dim lian As String' lian = "Driver={Microsoft Excel Driver(*.xls,*xlsx,*.xlsm,*xlsb)};" _' & "ReadOnly=False;" _' & "DBQ=" & ThisWorkbook.FullName'' 'driver ODBC连接驱动属性' 'ReadOnly 相当于IMEX属性' 'DBQ 连接数据源' con.Open lian' 检查是否完成连接' If cnn.State = adStateOpen Then' 'cnn.State -->adstratclosed 关闭状态 adstartopen 打开状态' 'adstartconnecting 连接中 adstartexecuting 执行命令中' MsgBox "Welcome to Pubs!"' Else' MsgBox "Sorry. No Pubs today."' End If'关闭connection对象cnn.Close'销毁连接Set cnn = Nothing'设置SQL命令的字符串Dim SqlCommandStr As StringSqlCommandStr = "SELECT top 10 存货编码 from [主材汇总$] "'[主材汇总$]--连接数据源中的具体表名,可指定具体的range,直接跟在表名后即可,也可使用在表中定义的名称'如果设置第一行为标题,则可直接使用第一行数据为字段名'SqlCommandStr = "SELECT 存货名称from [主材汇总$A1:M100] where 存货编码='A01020506001500200'"'执行SQl命令'A、不要返回值'cnn.Execute SqlCommandStr' Execute方法:Execute <SQL命令文本>,[recordsaffected 查询作用范围],[options 参数选项]' [recordsaffected 查询作用范围] 如:1NumAffected 仅第一条记录' [options 参数选项] -- SQL命令类型 or 执行方式' SQL命令类型:' adcomdtext: 原始 SQL字符串' adcomdtable: 表的名称,向连接提供驱动提供类似SELECT * from_name的语句' adcmdstoreproc: 存储过程' adcmdtabledirect:表名,不同于table的是不会产生类似SELECT * from_name的SQL语句' SQL命令执行方式:' adasyncexecute: 异步执行命令,立即执行代码' adexecutenorecords: 不构建recoredset对像' 如:adcmdtext or adexecutenorecords'B、需要返回值Dim jilu As ADODB.RecordsetSet jilu = New ADODB.Recordsetjilu.Open SqlCommandStr, lian' open方法:open <SQL命令文本或数据源>,<activeconnection>,[cursortype],[locktype],[options 参数选项]' <activeconnection> 指定用于查询的连接设置,可以OPEN方法以前将一个字符串赋值给Recordset的activeconnection属性,那么该记录集则自动产生' 一个connection对像' [cursortype] 打开记录集时的数据指针类型:默认值为adopenforwardonly' adopenforwardonly-->只能向下移动且不能修改,常规查询' adopenstatic -->静态指针,用于脱机记录集,允许完全操作' 此参数不同于OPEN方法之前设置的Recordset的cursorlocation属性(规定recordset使用的数据指针类型:' aduserclient --客户端数据指针' aduseserver-- 服务器端数据指针,默认值)' [locktype] 打开记录时的数据源锁定类型:' adlockreadonly:常规查询使用,锁定源记录' adlockbatchoptimistic:脱机记录集使用,开放式批量更新' [options 参数选项] -- SQL命令类型 or 执行方式 connection 对象的Execute方法设置相同Do While Not jilu.EOF '如果指针不是未尾则表示记录内有数据'EOF --指针位于记录集最后一条记录之后 BOF --指针位于记录集第一条记录之前(Recordset对象的属性)MsgBox jilu.Fields(0).Value'Fields对象:记录集所包括的所有字段,带有Name,Item,Value等属性与Append等方法jilu.MoveNext'Move方法:' MoveFirst:移动到第一条记录' MoveLast: 移动最后一条记录' MoveNext:移动到下一条记录Loopjilu.Filter = "存货编码 like 'A0202*'"'.Filter属性:对记录按一定条件进行选择过滤但不删除数据,将过滤条件设置为空则恢复所有数据,多个条件可以使用逻辑运算符进行联接Do While Not jilu.EOF MsgBox jilu.Fields(0).Value Loop'关闭Recordsetjilu.Close'销毁RecordsetSet jilu = NothingEnd Sub。

使用VBA执行SQL

使用VBA执行SQL

使用VBA执行SQLVBA是一种Visual Basic的应用,它是一个宏编程语言,由微软公司开发。

VBA可以用来开发Office应用程序,如Word,Excel,PowerPoint和Access,并且可以用它连接到支持ODBC(Open Database Connectivity)或OLEDB(Object Linking and Embedding Database)的外部数据源,以执行使用SQL语言的查询。

SQL(结构化查询语言)是一种用于存取数据库的语言。

它可以用于,插入,更新和删除数据库中的信息。

使用VBA执行SQL语句可以很容易地从Excel文件中访问外部数据库,并将其内容插入表格中或编写查询来获取信息。

要执行使用VBA执行SQL,首先需要使用ADO(ActiveX Data Objects)对象模型,它是一种允许开发人员建立应用程序来访问和操作数据库的框架。

使用ADO对象模型可以建立连接和会话以连接到数据库(如SQL Server,Oracle,Access,FoxPro,Text等),并使用Recordset对象中的查询来执行SQL语句。

下面是使用VBA执行SQL语句的典型示例://创建ADO连接Dim conn As ADODB.ConnectionSet conn = New ADODB.Connection//设置数据库连接信息conn.Open "provider=System.OleDb.OleDbConnection;" & _"Data Source=c:\mydatabase.mdb"//创建SQL查询Dim sql As Stringsql = "SELECT * FROM Products WHERE Price > 30"//使用SQL查询创建RecordsetDim rs As ADODB.RecordsetSet rs = conn.Execute(sql)//打印查询结果Do Until rs.EOFDebug.Print rs!Name, rs!Pricers.MoveNextLoop//关闭连接conn.Close上面的示例显示了如何使用VBA执行SQL语句,以便从数据库中获取信息。

利用VBA实现Excel与SQL数据库的数据读写与处理

利用VBA实现Excel与SQL数据库的数据读写与处理

利用VBA实现Excel与SQL数据库的数据读写与处理在信息时代,数据是企业的宝贵资产。

而在数据处理与分析中,Excel和SQL数据库是两个非常重要的工具。

Excel是一种强大的电子表格软件,而SQL数据库则是一种用于存储和管理数据的关系型数据库系统。

使用VBA(Visual Basic for Applications)编程语言,我们可以实现Excel与SQL数据库之间的数据读写与处理,为企业决策提供支持。

首先,利用VBA实现Excel与SQL数据库之间的数据读取是非常有用的功能。

通过VBA代码,我们可以连接到SQL数据库,并将数据库中的数据导入Excel中进行分析。

以下是实现数据读取的基本步骤:1. 建立数据库连接:通过VBA代码,使用适当的连接字符串来连接到SQL数据库。

可以使用ADODB(ActiveX Data Objects)库来实现数据库连接。

2. 执行SQL查询:使用VBA代码编写适当的SQL查询语句,例如SELECT语句,来从数据库中选择所需的数据。

通过使用ADODB.Recordset对象,可以执行该查询并将结果存储在Recordset中。

3. 将数据导入Excel:使用VBA代码,将Recordset中的数据导入Excel的指定单元格区域中。

可以使用Range对象来实现此功能。

通过以上步骤,我们可以轻松地将SQL数据库中的数据导入到Excel中进行进一步分析和处理。

其次,利用VBA实现Excel与SQL数据库之间的数据写入也是非常有用的。

通过将Excel中的数据写入SQL数据库,我们可以实现数据的持久化存储,并通过数据库的查询功能来进行数据检索和分析。

以下是实现数据写入的基本步骤:1. 建立数据库连接:同样通过VBA代码,使用适当的连接字符串连接到SQL数据库。

可以使用ADODB库来实现数据库连接。

2. 准备数据:在Excel中,准备要写入数据库的数据。

可以使用VBA代码将这些数据存储在适当的数据类型中(例如数组或集合)。

在Excel的VBA中使用SQL语句

在Excel的VBA中使用SQL语句

要求一,将EXCEL 文件SG Master List SO Outanding 090520_ZY.xls中Master页内容中,ItemCode字段左边六位字符值,和U_Cat1字符值加上U_Cat2加上”-”号,再加上U_Cat3右边两位数相比较,将不相同所有行记录,复制到sheet2页中去.Sub 筛选()Dim cn As New ADODB.ConnectionDim sql As String'cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullNamecn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.Path & "\SG Master List SO Outanding 090520_ZY.xls"sql = "select * from [Master$] where left(ItemCode,6) <> U_Cat1 & U_Cat2 & '-' & right(U_Cat3,2) Sheets("Sheet2").[A4].CopyFromRecordset cn.Execute(sql)cn.CloseSet cn = NothingEnd Sub一,在没有写代码这前要先通过菜单栏中”工具”,”引用”加载”ADO”类.'cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullNamecn.Open "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.Path & "\SG Master List SO Outanding 090520_ZY.xls"这两句都能成功建立过程与文件的链接.二,在SQL语句中, FROM后面的格式一定要[Master$],中间Master是页名,SQL中用到的字段名是这个页中第一行数据值.三, Sheets("Sheet2").[A4].CopyFromRecordset cn.Execute(sql)语句中, Sheets("Sheet2")代表要复制的目标页(在写VBA,之前要先建立好.).[A4]是要粘贴的起启单元格.要求二,将EXCEL 文件SG Master List SO Outanding 090520_ZY.xls中Master页内容中,ItemCode字段左边六位字符值,和U_Cat1字符值加上U_Cat2加上”-”号,再加上U_Cat3右边两位数相比较,将不相同所有行记录,标上”黄颜色”.一,先选择ItemCode字段第一行单元格,按住”shift”+”cntre”+”向下箭头”这样,就能将本列单元格全部选定.(适合大量数据的表中).二,在”格式”,---“条件格式”,选择”公式”写入“=LEFT($B1,6)<>($N1&$O1&"-"&RIGHT($P1,2))”(这里的列是用$B1表示,因为选中所有列,所以EXCEL会将公式自动刷新所有列.)EXCEL(VBA)~SQL 经典写法范本汇集****************************************************************A、根据本工作簿的1个表查询求和写法范本Sub 查询方法一()Set CONN = CreateObject("ADODB.Connection")CONN.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullNamesql = "select 区域,存货类, sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from [sheet4$a:i] where 区域='" & [b3] & "' and month(日期)='" & Month(Range("F3")) & "' group by 区域,存货类"Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)CONN.Close: Set CONN = NothingEnd Sub-----------------Sub 查询方法二()Set CONN = CreateObject("ADODB.Connection")CONN.Open "dsn=excel files;dbq=" & ThisWorkbook.FullNamesql = "select 区域,存货类, sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from [sheet4$a:i] where 区域='" & [b3] & "' and month(日期)='" & Month(Range("F3")) & "' group by 区域,存货类"Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)CONN.Close: Set CONN = NothingEnd Sub********************************************************************* *****************************B、根据本工作簿2个表的不同类别查询求和写法范本Sub 根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询()Set conn = CreateObject("adodb.connection")conn.Open "provider=microsoft.jet.oledb.4.0;" & _"extended properties=excel 8.0;datasource=" & ThisWorkbook.FullNameSheet3.ActivateSql = " select a.存货类,a.fh ,b.hk from (select 存货类,sum(本月发货数量) " _& " as fh from [入库$] where 存货类 is not null and 区域='" & [b2] _& "' and month(日期)=" & [d2] & " group by 存货类) as a" _& " left join (select 存货类,sum(数量) as hk from [回款$] where 存货类" _& " is not null and 区域='" & [b2] & "' and month(开票日期)=" & [d2] & "" _& " group by 存货类) as b on a.存货类=b.存货类"Range("a5").CopyFromRecordset conn.Execute(Sql)End Sub*******************************************************************C、根据本文件夹下其他工作簿1个表区域的区域求和Sub 在工作表1汇总本文件夹下001工作薄的表1分数列查询汇总()Set conn = CreateObject("ADODB.Connection")conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "\001.xls"sql = "select sum(分数) from [sheet1$]"Sheets(1).[a2].CopyFromRecordset conn.Execute(sql)conn.Close: Set conn = NothingEnd Sub---------------------Sub 在工作表1汇总本文件夹下001工作薄的表1A1:A10查询汇总()Set conn = CreateObject("ADODB.Connection")conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no;';data source=" & ThisWorkbook.Path & "\001.xls"sql = "select sum(f1) from [sheet1$a1:a10]"Sheets(1).[A5].CopyFromRecordset conn.Execute(sql)conn.Close: Set conn = NothingEnd Sub-----------------------Sub 在工作表1汇总本文件夹下001工作薄的表1分数列A1:A7查询并msgbox 表达汇总()Set conn = CreateObject("ADODB.Connection")Set rr = CreateObject("ADODB.recordset")conn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "\001.xls"sql = "select sum(分数) from [sheet1$a1:a7]"Sheets(1).[A8].CopyFromRecordset conn.Execute(sql)rr.Open sql, conn, 3, 1, 1MsgBox rr.fields(0)conn.Close: Set conn = NothingEnd Sub********************************************************************* *********************D、根据本文件夹下其他工作簿多个表区域的单列区域查询求和sub 本文件夹下其他工作簿的每个工作簿的第4列 30行查询求和Dim cn As Object, f$, arr&(1 To 30), i%Application.ScreenUpdating = FalseSet cn = CreateObject("adodb.connection")f = Dir(ThisWorkbook.Path & "\*.xls")Do While f <> ""If f <> Thencn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no;';data source=" & ThisWorkbook.Path & "\" & fRange("d5").CopyFromRecordset cn.Execute("select f4 from [基表1$a5:d65536]")cn.CloseFor i = 1 To 30arr(i) = arr(i) + Range("d" & i + 4)Next iEnd Iff = DirLoopRange("d5").Resize(UBound(arr), 1) = WorksheetFunction.Transpose(arr) Application.ScreenUpdating = TrueEnd Sub********************************************************************* *****************************E、根据本文件夹下其他工作簿多个表区域的多列区域查询求和sub 本文件夹下其他工作簿的每个工作簿的第B\C\D列 25行查询求和Dim cn As Object, f$, arr&(1 To 25, 1 To 3), i%Application.ScreenUpdating = FalseSet cn = CreateObject("adodb.connection")f = Dir(ThisWorkbook.Path & "\*.xls")Do While f <> ""If f <> Thencn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no;';data source=" & ThisWorkbook.Path & "\" & fRange("b6").CopyFromRecordset cn.Execute("selectf2,f3,f4 from [基表3$a6:e65536]")cn.CloseFor i = 1 To 25For j = 1 To 3arr(i, j) = arr(i, j) + Cells(i + 5, j + 1)Next jNext iEnd Iff = DirLoopRange("b6").Resize(UBound(arr), 3) = arrApplication.ScreenUpdating = TrueEnd Sub********************************************************************* **************F、其他相关知识整理' 用excel SQL方法'conn是建立的连接对象,用open打开' 通过 CreateObject("ADODB.Connection") 这一句建立了一个数据库连接对象conn' 在工程中就不再需要引用“Microsot ActiveX Data Objects 2.0 Library“对象'设置对象 conn 为一个新的 ADO 链接实例,也可以用 set conn = New ADODB.Connection。

  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

有时候需要将Excel文件中的数据导入到数据库中,常用的做法是使用程序读取Excel,然后存入数据库;这里换一个方法,用Excel生成SQL语句,然后用这些SQL语句来更新数据库。

本文主要说明Excel如何使用VB宏构造需要的SQL语句,并生成文件。

2、如何添加按钮:
依次打开:视图–工具栏–控件工具箱,选择“命令按钮”,自己画一个就行了;
依次打开:右键–属性,可以修改名称、样式等。

具体操作可以上网找找。

3、如何使用VB宏:
依次打开:工具–宏–安全性,看看你的Excel是否允许使用“宏”。

然后打开:工具–宏–Visual Basic编辑器,双击左侧的“Sheet1”,然后在右侧的代码区域粘贴如下代码:
'最大行数
Const MAX_NUM_ROW=5000
'导出文件路径所在单元格
Const PATH_OUTPUT_ROW=3
Const PATH_OUTPUT_COL=3
'定义列常量
Const NAME_COL=1
Const GENDER_COL=2
Const PHONE_COL=3
Const EMAIL_COL=4
'读取数据开始行数
Const START_ROW=5
'定义数据实体类
Private Type Tmplt
NAME As String
GENDER As String
PHONE As String
EMAIL As String
End Type
'行数变量
Dim noOfTmplts As Integer
'数据实体类数组
Dim TmpltArray(MAX_NUM_ROW)As Tmplt
'点击按钮触发事件
Private Sub CommandButton1_Click()
generateSQL
End Sub
'生成SQL
Private Sub generateSQL()
makedir
initData
writeToFile
End Sub
'构建文件输出路径
Private Sub makedir()
On Error Resume Next
MkDir Sheet1.Cells(PATH_OUTPUT_ROW,PATH_OUTPUT_COL)
End Sub
'读取Excel数据,填充实体类数组
Private Sub initData()
Erase TmpltArray
noOfTmplts=0
Dim j As Integer
'循环读取Excel数据行
For j=START_ROW To MAX_NUM_ROW
TmpltArray(noOfTmplts).NAME=Sheet1.Cells(j,NAME_COL) TmpltArray(noOfTmplts).GENDER=Sheet1.Cells(j,GENDER_COL) TmpltArray(noOfTmplts).PHONE=Sheet1.Cells(j,PHONE_COL) TmpltArray(noOfTmplts).EMAIL=Sheet1.Cells(j,EMAIL_COL) noOfTmplts=noOfTmplts+1
Next
End Sub
'读取实体类数组,生成SQL并写入文件
Private Sub writeToFile()
Dim lvOutputPath As String
'输出文件路径
lvOutputPath=Sheet1.Cells(PATH_OUTPUT_ROW,PATH_OUTPUT_COL) If lvOutputPath=""Then
MsgBox"没有找到输出文件路径!"
Exit Sub
End If
fileNum=FreeFile
'打开输出文件
Open lvOutputPath For Output As fileNum
Dim lvUserSql As String
Dim nameStr As String
Dim genderStr As String
Dim phoneStr As String
Dim emailStr As String
'循环生成SQL
For j=0To noOfTmplts-1
nameStr=TmpltArray(j).NAME
genderStr=TmpltArray(j).GENDER
phoneStr=TmpltArray(j).PHONE
emailStr=TmpltArray(j).EMAIL
If nameStr<>""Then
lvUserSql="Insert into Students(name,gender,phone,email)values('"&nameStr&"','"& genderStr&"','"&phoneStr&"','"&emailStr&"');"
Print#fileNum,lvUserSql
End If
Next
Close fileNum
MsgBox"文件生成完成!"
Exit Sub
Err_Open_File:
Close lvFileNum
If Err.Number=76Then
'路径未找到
MsgBox Err.Description
Exit Sub
Else
MsgBox Err.Description
Exit Sub
End If
End Sub
OK,这样就可以了!点击你的按钮,看看生成文件了吗?
如果按钮还是编辑状态,关了再打开就行了。

相关文档
最新文档