根据特定单元格值从Excel中复制特定的行 [英] Copy specific rows from excel based on a specific cell value
本文介绍了根据特定单元格值从Excel中复制特定的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在 rawData.xls 中，所有工作表都如下所示>
ABC
Module1 999 asda
Module2 22 asda
Module1 33 asda
Module7 44 asda
Module3 55 asda
Module2 66 asda
Module5 77 asda
我需要迭代 rawData.xls 中的所有工作表，复制包含Module1的所有行，并将其粘贴到 result.xls ，然后重复进行Module2，Module3，..
有没有办法使用VB脚本自动使用这种方式？
任何帮助都是赞赏。感谢提前
我的代码：
Sub copy（）
设置objRawData = objExcel.Workbooks.Open（rawData.xls）
设置objPasteData = objExcel.Workbooks.Open（result.xls）
StartRow = 1 RowNum = 2
do Until IsEmpty（objRawData.WorkSheets（Sheet1）。Range（C& RowNum））
如果objRawData.WorkSheets（Sheet1）。Range（C& RowNum）=module1 然后
StartRow = StartRow + 1
objPasteData.WorkSheets（Final）。Rows（StartRow）.Value = _
objRawData.WorkSheets（Sheet1）。Rows（RowNum）。值
结束If
RowNum = RowNum + 1
循环
End Sub
解决方案
而不是让流行的你尝试了什么？强制你编写
代码而没有计划，想想（并要求）知道如何/知道/方法/工具
需要选择特定行的表/表到新的表/ ta bles。
select表示SQL，而Excel不是数据库管理系统，您可以
使用.XLS作为数据库：有一点帮助来自 ADO 。
所以我的计划是：
（1）打开 ADODB.Connection 到您的源.XLS
（2）获取要处理的所有表/表的列表
（3）使用（2）生成语句喜欢
SELECT [A] FROM [Tbl1] UNION SELECT [A] FROM [Tbl2] UNION SELECT [A] FROM [Tbl3 ] ORDER BY [A]
（4）执行（3）并循环结果集
（5）For Each Module1 ... ModuleLast
（5a）为模块M创建新的工作表/表在您的目的地.XLS中，执行如
SELECT * INTO [TblModuleM] INpath\to\your\dst.xlsExcel 8.0; FROM [Tbl1] WHERE [A] ='ModuleM'
（5b）对于每个Tbl2 ... TblLast使用
INSERT INTO [TblModuleM] INpath\to\your\dst中的语句追加ModuleM行。 xlsExcel 8.0; SELECT * FROM [TblT] WHERE [A] ='ModuleM'
演示代码给你一些信心在计划和一些关键字查找：
Const csSFSpec =..\data\14515369\src。 xls
Const csDFSpec =..\data\14515369\dst.xls
Const csTables =[Tbl1] [Tbl2] [Tbl3]
Dim aTblNs：aTblNs = Split（csTables）
Dim oFS：设置oFS = CreateObject（Scripting.FileSystemObject）
Dim sSFSpec：sSFSpec = oFS.GetAbsolutePathName（csSFSpec）
Dim sDFSpec：sDFSpec = oFS.GetAbsolutePathName（csDFSpec）
如果oFS.FileExists（sDFSpec）然后oFS.DeleteFile sDFSpec
Dim oDbS：设置oDbS = CreateObJect（ADODB.Connection）
Dim sCS：sCS = Join（Array（_
Provider = Microsoft.Jet.OLEDB.4.0，Data Source =& sSFSpec，_
Extended Properties =Excel 8.0; HDR = True; IMEX = 0; Readonly = False_
），;）
WScript.EchoConne ctionstring：
WScript.Echo sCS
oDbS.Open sCS
Dim sInExt：sInExt =IN& sDFSpec& Excel 8.0;
Dim sSelI：sSelI =SELECT * INTO [Tbl @ Mod]& sInExt& FROM @Tbl WHERE [A] ='@Mod'
Dim sInsI：sInsI =INSERT INTO [Tbl @ Mod]& sInExt& SELECT * FROM @Tbl WHERE [A] ='@Mod'
WScript.Echo sSelI
WScript.Echo sInsI
Dim sMods：sMods =SELECT [A] FROM&对于i = 1 TO UBound（aTblNs）
sMods = sMods& UNION SELECT [A] FROM& aTblNs（i）
下一个
sMods = sMods& ORDER BY [A]
WScript.Echo sMods
Dim oRS：设置oRS = oDbS.Execute（sMods）
Dim sSQL
直到oRS.EOF
WScript.Echo处理，oRS（A），...
sSQL =替换（替换（sSelI，@Mod，oRS（A）），@ Tbl，aTblNs（0））
WScript.Echo创建并填写新表，oRS（A）
WScript.Echo sSQL
oDbS.Execute sSQL
对于i = 1到UBound（aTblNs）
sSQL =替换（替换（sInsI，@Mod，oRS（A）），@Tbl，aTblNs（i））
WScript .EchoAppending for，oRS（A），from，aTblNs（i）
WScript.Echo sSQL
oDbS.Execute sSQL
下一个
oRS.MoveNext
循环
oRS.Close
oDbS.Close
输出：
Connectionstring：
Provider = Microsoft.Jet.OLEDB.4.0; Data Source = somewheresrc.xls;扩展
属性=Excel 8.0; HDR = True; IMEX = 0; Readonly = False
SELECT * INTO [Tbl @ Mod] IN .xlsExcel 8.0; FROM @Tbl
WHERE [A] ='@Mod'
INSERT INTO [Tbl @ Mod] INsometheredst.xlsExcel 8.0; SELECT * FRO
M @Tbl WHERE [A] ='@Mod'
SELECT [A] FROM [Tbl1] UNION SELECT [A] FROM [Tbl2] UNION SELECT [A] FROM [Tbl3] ORDER BY [A]
处理模块1 ...
创建&填写Module1的新表
SELECT * INTO [TblModule1] INsometheredst.xlsExcel 8.0; FROM [T
bl1] WHERE [A] ='Module1'
从[Tbl2]追加Module1
INSERT INTO [TblModule1] INsometheredst.xlsExcel 8.0; SELECT *
FROM [Tbl2] WHERE [A] ='Module1'
从[Tbl3]追加到Module1
INSERT INTO [TblModule1] INsometheredst.xlsExcel 8.0; SELECT *
FROM [Tbl3] WHERE [A] ='Module1'
处理模块2 ...
创建&填写Module2的新表
SELECT * INTO [TblModule2] INsometheredst.xlsExcel 8.0; FROM [T
bl1] WHERE [A] ='Module2'
从[Tbl2]追加到Module2
INSERT INTO [TblModule2] INsometheredst.xlsExcel 8.0; SELECT *
FROM [Tbl2] WHERE [A] ='Module2'
从[Tbl3]追加到Module2
INSERT INTO [TblModule2] INsometheredst.xlsExcel 8.0; SELECT *
FROM [Tbl3] WHERE [A] ='Module2'
处理模块3 ...
创建&填写Module3的新表
SELECT * INTO [TblModule3] INsometheredst.xlsExcel 8.0; FROM [T
bl1] WHERE [A] ='Module3'
从[Tbl2]追加Module3
INSERT INTO [TblModule3] INsometheredst.xlsExcel 8.0; SELECT *
FROM [Tbl2] WHERE [A] ='Module3'
从[Tbl3]追加到Module3
INSERT INTO [TblModule3] INsometheredst.xlsExcel 8.0; SELECT *
FROM [Tbl3] WHERE [A] ='Module3'
处理模块4 ...
创建&填写Module4的新表
SELECT * INTO [TblModule4] INsometheredst.xlsExcel 8.0; FROM [T
bl1] WHERE [A] ='Module4'
从[Tbl2]追加到Module4
INSERT INTO [TblModule4] INsometheredst.xlsExcel 8.0; SELECT *
FROM [Tbl2] WHERE [A] ='Module4'
从[Tbl3]追加到Module4
INSERT INTO [TblModule4] INsometheredst.xlsExcel 8.0; SELECT *
FROM [Tbl3] WHERE [A] ='Module4'
I have multiple worksheets in a excel book and each of these worksheet contains module wise data. I want to copy all the module data from each worksheet and paste it in a new excel book. How can this be done using VBScript?
All sheets looks something like this in rawData.xls
A B C
Module1 999 asda
Module2 22 asda
Module1 33 asda
Module7 44 asda
Module3 55 asda
Module2 66 asda
Module5 77 asda
I need to iterate all the sheets in rawData.xls, copy all rows containing "Module1" and paste it to result.xls, and repeat for Module2, Module3, ...
Is there a way to make this kind of an automated one using VB Script?
Any help is appreciated. Thanks in advance
My Code:
Sub copy()
Set objRawData = objExcel.Workbooks.Open("rawData.xls")
Set objPasteData = objExcel.Workbooks.Open("result.xls")
StartRow = 1 RowNum = 2
Do Until IsEmpty(objRawData.WorkSheets("Sheet1").Range("C" & RowNum))
If objRawData.WorkSheets("Sheet1").Range("C" & RowNum) = "module1" Then
StartRow = StartRow + 1
objPasteData.WorkSheets("Final").Rows(StartRow).Value = _
objRawData.WorkSheets("Sheet1").Rows(RowNum).Value
End If
RowNum = RowNum + 1
Loop
End Sub
解决方案
Instead of letting the popular 'What have you tried?' coerce you into writing code without a plan, think about (and ask for) the know how/know to/methods/tools necessary for selecting specific rows of sheets/tables into new sheets/tables.
"select" implies SQL and while Excel is not a database mangement system, you can use an .XLS as a database: with a little help from ADO.
So my plan would be:
(1) Open an ADODB.Connection to your source .XLS
(2) Get a list of all sheets/tables to process
(3) Use (2) to generate a statement like
SELECT [A] FROM [Tbl1] UNION SELECT [A] FROM [Tbl2] UNION SELECT [A] FROM [Tbl3] ORDER BY [A]
(4) Execute (3) and loop over the resultset
(5) For Each Module1 ... ModuleLast
(5a) To create the new sheet/table for Module M in your destination .XLS, execute a statement like
SELECT * INTO [TblModuleM] IN "path\to\your\dst.xls" "Excel 8.0;" FROM [Tbl1] WHERE [A] = 'ModuleM'
(5b) For Each Tbl2 ... TblLast append the ModuleM rows using statements like
INSERT INTO [TblModuleM] IN "path\to\your\dst.xls" "Excel 8.0;" SELECT * FROM [TblT] WHERE [A] = 'ModuleM'
Demo code to give you some confidence in the plan and some keywords to look up:
Const csSFSpec = "..\data\14515369\src.xls"
Const csDFSpec = "..\data\14515369\dst.xls"
Const csTables = "[Tbl1] [Tbl2] [Tbl3]"
Dim aTblNs : aTblNs = Split(csTables)
Dim oFS : Set oFS = CreateObject("Scripting.FileSystemObject")
Dim sSFSpec : sSFSpec = oFS.GetAbsolutePathName(csSFSpec)
Dim sDFSpec : sDFSpec = oFS.GetAbsolutePathName(csDFSpec)
If oFS.FileExists(sDFSpec) Then oFS.DeleteFile sDFSpec
Dim oDbS : Set oDbS = CreateObJect("ADODB.Connection")
Dim sCS : sCS = Join(Array( _
"Provider=Microsoft.Jet.OLEDB.4.0", "Data Source=" & sSFSpec, _
"Extended Properties=""Excel 8.0;HDR=True;IMEX=0;Readonly=False""" _
),";")
WScript.Echo "Connectionstring:"
WScript.Echo sCS
oDbS.Open sCS
Dim sInExt : sInExt = " IN """ & sDFSpec & """ ""Excel 8.0;"""
Dim sSelI : sSelI = "SELECT * INTO [Tbl@Mod] " & sInExt & " FROM @Tbl WHERE [A] = '@Mod'"
Dim sInsI : sInsI = "INSERT INTO [Tbl@Mod] " & sInExt & " SELECT * FROM @Tbl WHERE [A] = '@Mod'"
WScript.Echo sSelI
WScript.Echo sInsI
Dim sMods : sMods = "SELECT [A] FROM " & aTblNs(0)
Dim i
For i = 1 TO UBound(aTblNs)
sMods = sMods & " UNION SELECT [A] FROM " & aTblNs(i)
Next
sMods = sMods & " ORDER BY [A]"
WScript.Echo sMods
Dim oRS : Set oRS = oDbS.Execute(sMods)
Dim sSQL
Do Until oRS.EOF
WScript.Echo "Processing", oRS("A"), "..."
sSQL = Replace(Replace(sSelI, "@Mod", oRS("A")), "@Tbl", aTblNs(0))
WScript.Echo "Create & fill new table for", oRS("A")
WScript.Echo sSQL
oDbS.Execute sSQL
For i = 1 To UBound(aTblNs)
sSQL = Replace(Replace(sInsI, "@Mod", oRS("A")), "@Tbl", aTblNs(i))
WScript.Echo "Appending for", oRS("A"), "from", aTblNs(i)
WScript.Echo sSQL
oDbS.Execute sSQL
Next
oRS.MoveNext
Loop
oRS.Close
oDbS.Close
output:
Connectionstring:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=somewheresrc.xls;Extended
Properties="Excel 8.0;HDR=True;IMEX=0;Readonly=False"
SELECT * INTO [Tbl@Mod] IN "somewheredst.xls" "Excel 8.0;" FROM @Tbl
WHERE [A] = '@Mod'
INSERT INTO [Tbl@Mod] IN "somewheredst.xls" "Excel 8.0;" SELECT * FRO
M @Tbl WHERE [A] = '@Mod'
SELECT [A] FROM [Tbl1] UNION SELECT [A] FROM [Tbl2] UNION SELECT [A] FROM [Tbl3] ORDER BY [A]
Processing Module1 ...
Create & fill new table for Module1
SELECT * INTO [TblModule1] IN "somewheredst.xls" "Excel 8.0;" FROM [T
bl1] WHERE [A] = 'Module1'
Appending for Module1 from [Tbl2]
INSERT INTO [TblModule1] IN "somewheredst.xls" "Excel 8.0;" SELECT *
FROM [Tbl2] WHERE [A] = 'Module1'
Appending for Module1 from [Tbl3]
INSERT INTO [TblModule1] IN "somewheredst.xls" "Excel 8.0;" SELECT *
FROM [Tbl3] WHERE [A] = 'Module1'
Processing Module2 ...
Create & fill new table for Module2
SELECT * INTO [TblModule2] IN "somewheredst.xls" "Excel 8.0;" FROM [T
bl1] WHERE [A] = 'Module2'
Appending for Module2 from [Tbl2]
INSERT INTO [TblModule2] IN "somewheredst.xls" "Excel 8.0;" SELECT *
FROM [Tbl2] WHERE [A] = 'Module2'
Appending for Module2 from [Tbl3]
INSERT INTO [TblModule2] IN "somewheredst.xls" "Excel 8.0;" SELECT *
FROM [Tbl3] WHERE [A] = 'Module2'
Processing Module3 ...
Create & fill new table for Module3
SELECT * INTO [TblModule3] IN "somewheredst.xls" "Excel 8.0;" FROM [T
bl1] WHERE [A] = 'Module3'
Appending for Module3 from [Tbl2]
INSERT INTO [TblModule3] IN "somewheredst.xls" "Excel 8.0;" SELECT *
FROM [Tbl2] WHERE [A] = 'Module3'
Appending for Module3 from [Tbl3]
INSERT INTO [TblModule3] IN "somewheredst.xls" "Excel 8.0;" SELECT *
FROM [Tbl3] WHERE [A] = 'Module3'
Processing Module4 ...
Create & fill new table for Module4
SELECT * INTO [TblModule4] IN "somewheredst.xls" "Excel 8.0;" FROM [T
bl1] WHERE [A] = 'Module4'
Appending for Module4 from [Tbl2]
INSERT INTO [TblModule4] IN "somewheredst.xls" "Excel 8.0;" SELECT *
FROM [Tbl2] WHERE [A] = 'Module4'
Appending for Module4 from [Tbl3]
INSERT INTO [TblModule4] IN "somewheredst.xls" "Excel 8.0;" SELECT *
FROM [Tbl3] WHERE [A] = 'Module4'
这篇关于根据特定单元格值从Excel中复制特定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
