[已解决]导入某表的部分符合条件的列的数据

chensir · 发表于 2014-3-25 09:33

本帖最后由 chensir 于 2014-3-25 10:40 编辑

想将“基础表”里“表1”里月份为1的数据导入到“分析表”的“表1”里。只将“品牌”、“年份”、“季节”、“月份”、“店铺名称（HD）”这几列符合月份为“1”的数据导入。求VBA语句，多谢。

最佳答案

xdragon

2014-3-25 09:42

本帖最后由 xdragon 于 2014-3-25 11:37 编辑

Sub Test()
Dim Conn As Object, Rst As Object
Dim strConn As String, SQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.Path & "\基础表.xlsx"
Select Case Application.Version * 1
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Conn.Open strConn
SQL = "select 品牌,年份,季节,月份,分销系统代码,[店铺名称(HD)] from [表1$] where 月份=1"
Set Rst = Conn.Execute(SQL)
With Sheets("表1")
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit
End With
Rst.Close
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
End Sub

复制代码

xdragon · 发表于 2014-3-25 09:42

本帖最后由 xdragon 于 2014-3-25 11:37 编辑

Sub Test()
Dim Conn As Object, Rst As Object
Dim strConn As String, SQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.Path & "\基础表.xlsx"
Select Case Application.Version * 1
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Conn.Open strConn
SQL = "select 品牌,年份,季节,月份,分销系统代码,[店铺名称(HD)] from [表1$] where 月份=1"
Set Rst = Conn.Execute(SQL)
With Sheets("表1")
.Cells.Clear
For i = 0 To Rst.Fields.Count - 1
.Cells(1, i + 1) = Rst.Fields(i).Name
Next i
.Range("A2").CopyFromRecordset Rst
.Cells.EntireColumn.AutoFit
End With
Rst.Close
Conn.Close
Set Conn = Nothing
Set Rst = Nothing
End Sub

复制代码

xpw6061 · 发表于 2014-3-25 09:57

xdragon 发表于 2014-3-25 09:42

xdragon · 发表于 2014-3-25 09:58

本帖最后由 xdragon 于 2014-3-25 10:01 编辑

xpw6061 发表于 2014-3-25 09:57 SQL = "select 品牌,年份,季节,月份,分销系统代码,[店铺名称(HD)] from [表1$] where 月份=1"

谢谢提醒，好像看错要求了。

xdragon · 发表于 2014-3-25 10:00

复制代码

楼主，如果按照你网页的来，把17行sql语句改一下就可以了。

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