求助：如何不打开多个EXCEL文件提取数据存入指定EXCEL文件

zhaogang1960 · 发表于 2011-3-5 21:11

原帖由 terryzj 于 2011-3-5 19:26 发表
还有一个问题
我用的是excel2007，vba程序是放在采用启用宏的excel（*.xlsm），用上面的代码，程序没有反应啊？
具体见附件
zhao：can u help me? thkx very much!

是要求输入若干条A列数据后单击按钮查询对应的B列数据？还是输入一个数据得到B列数据(Worksheet_Change事件)？下面是第一种情况：
Private Sub CommandButton1_Click()
Dim cnn As Object, SQL$, s$, s2$, Mypath$, MyFile$
Set cnn = CreateObject("adodb.connection")
Mypath = ThisWorkbook.Path & "\"
MyFile = Mypath & "sourcetable.xls"
If Dir(MyFile) = "" Then
      MsgBox "No File!"
Else
      cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & MyFile
      s = "[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & Me.Name & "$a1:a1" & Cells(Cells.Rows.Count, 1).End(xlUp).Row & "]"
      s2 = "SELECT distinct a.[Environment Data环境数据],a.[Daily Total日总计] FROM [Overview$] a," & s & " b where a.[Environment Data环境数据]=b.Type"
      SQL = "SELECT c.[Daily Total日总计] FROM (" & s2 & ") c right join " & s & " d on c.[Environment Data环境数据]=d.Type"
      [b2].CopyFromRecordset cnn.Execute(SQL)
      cnn.Close
      Set cnn = Nothing
End If
End Sub

zhaogang1960 · 发表于 2011-3-5 21:12

附件

GetData.rar (29.6 KB, 下载次数: 115)

terryzj · 发表于 2011-3-5 23:06

十分感谢！帮助很大！能说明下代码的意思嘛，看不明白那几句关键代码的意思。
另外，我的sourcetable的表头的格式有所变化，表头增加了好几行说明文字？这样的表该怎么处理啊？
在附件中重新提出了两个问题，希望能得到您的帮忙。
Thkx!

zhaogang1960 · 发表于 2011-3-5 23:46

数据源太不规范了，试试看吧：
Private Sub CommandButton1_Click()
Dim cnn As Object, SQL$, s$, s2$, Mypath$, MyFile$, i&
Set cnn = CreateObject("adodb.connection")
Mypath = ThisWorkbook.Path & "\"
MyFile = Mypath & "sourcetable.xls"
If Dir(MyFile) = "" Then
      MsgBox "No File!"
Else
      cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;hdr=no';Data Source=" & MyFile
      s = "[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & Me.Name & "$a1:a4]"
      s2 = "SELECT distinct a.f1,a.f7 FROM [Overview$a9:g20] a," & s & " b where a.f1=b.Type"
      SQL = "SELECT c.f7 FROM (" & s2 & ") c right join " & s & " d on c.f1=d.Type"
      [b2].CopyFromRecordset cnn.Execute(SQL)
      s = "[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & Me.Name & "$a6:a49]"
      s2 = "SELECT distinct a.f1,a.f9,a.f10,a.f12,a.f16 FROM [Data$b7:q55] a," & s & " b where a.f1=b.[Well No#]"
      SQL = "SELECT c.f9,c.f10,c.f12,c.f16 FROM (" & s2 & ") c right join " & s & " d on c.f1=d.[Well No#]"
      [b7].CopyFromRecordset cnn.Execute(SQL)
      cnn.Close
      Set cnn = Nothing
End If
End Sub

zhaogang1960 · 发表于 2011-3-5 23:49

附件

GetData-2.rar (43.31 KB, 下载次数: 124)

terryzj · 发表于 2011-3-6 09:24

代码是可以运行的
能简要说明下关键代码的意思嘛？谢谢
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;hdr=no';Data Source=" & MyFile
      s = "[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & Me.Name & "$a1:a4]"
      s2 = "SELECT distinct a.f1,a.f7 FROM [Overview$a9:g20] a," & s & " b where a.f1=b.Type"
      SQL = "SELECT c.f7 FROM (" & s2 & ") c right join " & s & " d on c.f1=d.Type"
      [b2].CopyFromRecordset cnn.Execute(SQL)
      s = "[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & Me.Name & "$a6:a49]"
      s2 = "SELECT distinct a.f1,a.f9,a.f10,a.f12,a.f16 FROM [Data$b7:q55] a," & s & " b where a.f1=b.[Well No#]"
      SQL = "SELECT c.f9,c.f10,c.f12,c.f16 FROM (" & s2 & ") c right join " & s & " d on c.f1=d.[Well No#]"

zhaogang1960 · 发表于 2011-3-6 11:45

原帖由 terryzj 于 2011-3-6 09:24 发表
代码是可以运行的
能简要说明下关键代码的意思嘛？谢谢
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties='Excel 12.0;hdr=no';Data Source=" & MyFile
s = "[Excel 12.0;Database=" & ...

      s = "[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & Me.Name & "$a1:a4]" '本工作簿数据区域，用于比较A列记录，由于没有连接本工作簿所以前面加上了完整路径
      s2 = "SELECT distinct a.f1,a.f7 FROM [Overview$a9:g20] a," & s & " b where a.f1=b.Type" '本工作簿和数据源都有的纪录
      SQL = "SELECT c.f7 FROM (" & s2 & ") c right join " & s & " d on c.f1=d.Type" '都有的纪录和本工作簿A列对应的G列数据
      [b2].CopyFromRecordset cnn.Execute(SQL) '复制查询

cw9999 · 发表于 2011-3-7 09:25

收藏,学习.........

terryzj · 发表于 2011-3-8 13:40

原帖由 zhaogang1960 于 2011-3-6 11:45 发表

s = "[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & Me.Name & "$a1:a4]" '本工作簿数据区域，用于比较A列记录，由于没有连接本工作簿所以前面加上了完整路径
s2 = "SELECT distinc ...

============================================================================
zhao:
我用你的代码在我程序中,还是出不来结果阿.数据格式都一样的. 能详细的说明下关键代码的意思吗?
s = "[Excel 12.0;Database=" & ThisWorkbook.FullName & "].[" & Me.Name & "$a6:a49]"
s2 = "SELECT distinct a.f1,a.f9,a.f10,a.f12,a.f16 FROM [Data$b7:q55] a," & s & " b where a.f1=b.[Well No#]" '=====a.f1这些代表什么意思啊？b又是指什么呢？
SQL = "SELECT c.f9,c.f10,c.f12,c.f16 FROM (" & s2 & ") c right join " & s & " d on c.f1=d.[Well No#]" '======这里的c又是什么意思？
谢谢！

terryzj · 发表于 2011-3-8 14:07

zhao:
还有一个问题，源文件里数据是按照A B C D这样的顺序排的
通过SQL按照A字段比较记录，想在目标文件中按照A D B C这样的顺序排
这样该怎么操作阿？
Thkx again

		自动登录	找回密码
密码			免费注册

[求助] 求助：如何不打开多个EXCEL文件提取数据存入指定EXCEL文件