VBA-Excel从不同位置的不同工作表获取相同的数据 [英] VBA - Excel Getting same data from different sheets at different positions
问题描述
我想创建一个宏，该宏将打开一个文件夹中的所有excel书籍，读取填充的信息并将它们存储在代表我的数据库的工作表中. 我需要了解您的建议，以及做到这一点的最佳方法是什么，以获得快速而灵活的结果.
为帮助您理解我的问题，让我们假设我有3个excel模板，其中包含名字，姓氏和国家/地区，但在这些图片等不同的位置
模板1
模板2
模板3
基于此，我想得到的最终结果是:
我通过这些图片给出的示例确实非常简单，但这只是为了帮助您了解我想要的.现在，我将详细介绍真正的需求.实际上，我有3个模板，但是每个模板都包含大约80个数据字段(不只是名字，姓氏和国家/地区).而且我不必只读取3个文件，但我必须读取放置在一个文件夹中的大约200个文件，每个文件要么是template1，要么是2或3.将来，我们可能会有一个模板4，这就是为什么我需要灵活的东西.
我考虑过命名范围，但是模板1,2,3已经存在，并且我无法从200个用户中收集200个现有的excel文件，并且在启动宏之前，无法为80字段指定命名范围在每个文件.我可以使用命名范围，如果将来它们将是模板4，那么在将文件发送给将填充Excel的最终用户之前，我们先将范围命名并发送给他，但是在模板4之前，我必须修复当前的三个现有模板的问题.
我还考虑过基于列和行索引读取数据，例如，我检查文件的类型，如果正在读取文件模板，则从单元格(2,3)中获取名字，如果是模板2，我从单元格(5,6)中获取信息，如果它是模板3，我从Cel(9,4)中获取信息，但是问题是我的代码根本不够灵活.
我还说过，我可能会像参考文件那样工作，其中我根据模板模型定义每个字段的位置，例如，我说名字是模板1在位置2,3处的名称.模板2的名字是5,6，而template3的名字是9,4.如下图所示，当我遍历我的200个文件时，我检查是否是模板1，我已阅读参考表，并且我知道名字将在此位置，与模板2相同，依此类推... .此解决方案看起来像上一个解决方案，但是更灵活，因为如果有任何更改，我们要做的就是更改参考表，但是我想知道如果对于每个字段我都必须读取2个单元格，它是快速还是慢速?参考表以了解其位置.
我真的迷路了，因为在开始编码之前，我必须选择最好的方式来做自己想做的事情，以免浪费时间. 如果有专家可以告诉我什么是最好的，或者给我更多的想法，而不是我的想法，我将不胜感激.
提前感谢任何帮助者
@PEH，如果我将查找表做成这样的话，您会怎么想?
@PEH，这就是最近的评论中所建议的
基本思想(除了循环浏览文件之外):
-
将您的查找数据更改为以下内容:
-
然后阅读
Cells(1, 6)以获得模型.Dim Model As String Model = Worksheets("MyTemplate").Cells(1, 6).Value -
使用 WorksheetFunction.Match方法在查找表中找到您的字段.
Dim FieldRow As Long FieldRow = Application.WorksheetFunction.Match(Model & "-First name", Worksheets("LookupTable").Range("A:A"), 0) -
使用...
fRow = Worksheets("LookupTable").Cells(FieldRow, 2) fColumn = Worksheets("LookupTable").Cells(FieldRow, 3)在模板中查找该字段的行和列.
如果将字段查找内容放入方便的函数中，则代码将更易于维护.例如，将以下内容放入模块:
Option Explicit
Public LookupCache As Variant
Public LookupResults As Variant
Public Function ReadField(Ws As Worksheet, FieldName As String) As Variant
'Here we cache the lookup table. It reads the sheet LookupTable into an
'array if the array does not exist yet. If the function runs a second time,
'the array exists already and is used directly (saves time).
'Lookup in arrays is much faster than in cells.
'Caching makes this function about 2 times faster than without.
If IsEmpty(LookupCache) Or IsEmpty(LookupResults) Then
With ThisWorkbook.Worksheets("LookupTable")
Dim LastLookupRow As Long
LastLookupRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LookupCache = .Range("A2", "A" & LastLookupRow).Value
LookupResults = .Range("B2", "C" & LastLookupRow).Value
End With
End If
Dim ModelName As String
ModelName = Ws.Cells(1, 6).Value
Dim LookupRow As Long
On Error Resume Next
LookupRow = Application.WorksheetFunction.Match(ModelName & "-" & FieldName, LookupCache, 0)
On Error GoTo 0
If LookupRow = 0 Then
'field not found
ReadField = CVErr(xlErrNA)
Exit Function
End If
Dim fRow As Long, fColumn As Long
fRow = LookupResults(LookupRow, 1)
fColumn = LookupResults(LookupRow, 2)
ReadField = Ws.Cells(fRow, fColumn).Value
End Function
因此您可以阅读
之类的字段Debug.Print ReadField(MyLoopWorkbook.Worksheets("MyTemplate"), "First name")
'MyLoopWorkbook should be the current workbook in your files loop
根据评论进行编辑……
如果我们向新模型中添加了新字段
Company，则用户必须转到工作表查找表并在第11行的行和列处添加Model4-Company，还必须在他必须添加的代码中添加ReadField(MyLoopWorkbook.Worksheets("MyNewTemplate"), "Company")，对吗?这就是为什么我不理解如何只能依靠那些不编写代码的人的原因?您能否请您澄清一下，因为您说的话确实很重要.
如果将ReadField部分设为动态，则无需在此处进行编码.例如，如果您想得到一个像这样的表:
您只需在第4列中添加一个新标题即可，例如Company之类的字段.并编写一个循环遍历该标题行的列以收集所有字段的循环.
Sub ReadAllFields()
Dim wsData As Worksheet
Set wsData = Worksheets("CollectedData")
Dim FreeRow As Long 'find next free row in table
FreeRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row + 1
Dim Fields() As Variant 'read headers into array
Fields = wsData.Range("A1", wsData.Cells(1, wsData.Columns.Count).End(xlToLeft)).Value
Dim iCol As Long
For iCol = 1 To UBound(Fields, 2) 'loop through header columns
wsData.Cells(FreeRow, iCol).Value = ReadField(MyLoopWorkbook.Worksheets("MyNewTemplate"), Fields(1, iCol))
'reads fields dynamically depending on which headers exist in data sheet
Next iCol
End Sub
I want to make a macro that will open all excel books in a folder, read the filled information and store them in a sheet which will represent my database. I need to know your suggestions and what is the best way to do that, to get a fast and flexible result.
To help you understand my question, let us suppose that I have 3 excel templates containing First Name, Last Name and Country, but at different positions like these pictures
Template 1
Template 2
Template 3
Based on that, the final result that I would like to get is :
The exemple that I am giving by these pictures is really very simple, but it was just to help you understand what I want. Now I will detail about the real need. In fact, I have 3 templates, but each of them contains about 80 fields of data to collect (not only first name, last name and country). And i don't have to read only 3 files, but I have to read about 200 files placed in a folder and each of them is either template1, or 2 or 3. In the future we may have a template 4 that's why I need something flexible.
I thought about named ranges, but the template 1,2,3 already exists, and I can't collect from the 200 users the 200 existing excels files, and before launching my macro, giving a named range to the 80 field at each file. I can use named range if in the future they will be a template 4, so before sending the files to the final user who will fill the excel we name the ranges and send it to him, but before the template 4,i have to fix the problem of the current 3 existing templates.
I also thought about reading data based on columns and row indexes,for exemple I check the type of file and if I am reading a file template one, I get first name from the cell (2,3), and If it's a template 2, i get the information from cell (5,6) and if it's a template 3, i get the information from Cel (9,4), but the problem is that my code will not be at all flexible.
I also said, I may do like a sheet called reference, in which I define the positions of each field based on the template model, for example I say that the first name is for template 1 at the position 2,3 for the template 2, first name is at 5,6 and for template3 it's at 9,4. Like the following picture, and when I loop through my 200 files, I check, if it's template 1 i read the sheet of reference and I know that the first name will be at this position, same for template 2 and so on....this solution looks like previous one, but more flexible, because all we have to change is the reference table if something changes, but I am wondering if it will be fast or slow if for each field i have to come read 2 cells in the reference sheet to know the position.
I am really lost because I have to choose the best way to do what I want before start coding to avoid time wasting. If any expert can help by telling me what is best or giving me more ideas than what I thought about I will really appreciate.
Thanks in advance to any helper
EDIT: @PEH, what do you think about if I make my lookup table like that ?
EDIT2: @PEH, that's what is suggested in last comment
The basic idea (beside looping through your files):
Change your lookup data into the following:
Then read
Cells(1, 6)to get your model.Dim Model As String Model = Worksheets("MyTemplate").Cells(1, 6).ValueUse the WorksheetFunction.Match method to find your field in the lookup table.
Dim FieldRow As Long FieldRow = Application.WorksheetFunction.Match(Model & "-First name", Worksheets("LookupTable").Range("A:A"), 0)Use …
fRow = Worksheets("LookupTable").Cells(FieldRow, 2) fColumn = Worksheets("LookupTable").Cells(FieldRow, 3)To get row and column where to look for that field in your template.
If you put the field lookup stuff into a handy function, the code would get easier to maintain. For example put the following into a module:
Option Explicit
Public LookupCache As Variant
Public LookupResults As Variant
Public Function ReadField(Ws As Worksheet, FieldName As String) As Variant
'Here we cache the lookup table. It reads the sheet LookupTable into an
'array if the array does not exist yet. If the function runs a second time,
'the array exists already and is used directly (saves time).
'Lookup in arrays is much faster than in cells.
'Caching makes this function about 2 times faster than without.
If IsEmpty(LookupCache) Or IsEmpty(LookupResults) Then
With ThisWorkbook.Worksheets("LookupTable")
Dim LastLookupRow As Long
LastLookupRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LookupCache = .Range("A2", "A" & LastLookupRow).Value
LookupResults = .Range("B2", "C" & LastLookupRow).Value
End With
End If
Dim ModelName As String
ModelName = Ws.Cells(1, 6).Value
Dim LookupRow As Long
On Error Resume Next
LookupRow = Application.WorksheetFunction.Match(ModelName & "-" & FieldName, LookupCache, 0)
On Error GoTo 0
If LookupRow = 0 Then
'field not found
ReadField = CVErr(xlErrNA)
Exit Function
End If
Dim fRow As Long, fColumn As Long
fRow = LookupResults(LookupRow, 1)
fColumn = LookupResults(LookupRow, 2)
ReadField = Ws.Cells(fRow, fColumn).Value
End Function
So you could read a field like
Debug.Print ReadField(MyLoopWorkbook.Worksheets("MyTemplate"), "First name")
'MyLoopWorkbook should be the current workbook in your files loop
Edit according to the comment …
If we added a new field
Companyto a new model4,a user must go to the sheet lookuptable and addModel4-Companyat line 11 with the row and col, but also in the code he has to go and addReadField(MyLoopWorkbook.Worksheets("MyNewTemplate"), "Company"), right? That's why I am not understanding how I can count only on persons who don't code to add that? Can you clarify please because what you said is really important.
If you make the ReadField part dynamic you don't need to code here too. For example if you want to end up with a table like that:
You would just add a new header in column 4 called like the field eg Company. And write a loop that loops throug the columns of that header row to collect all fields.
Sub ReadAllFields()
Dim wsData As Worksheet
Set wsData = Worksheets("CollectedData")
Dim FreeRow As Long 'find next free row in table
FreeRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row + 1
Dim Fields() As Variant 'read headers into array
Fields = wsData.Range("A1", wsData.Cells(1, wsData.Columns.Count).End(xlToLeft)).Value
Dim iCol As Long
For iCol = 1 To UBound(Fields, 2) 'loop through header columns
wsData.Cells(FreeRow, iCol).Value = ReadField(MyLoopWorkbook.Worksheets("MyNewTemplate"), Fields(1, iCol))
'reads fields dynamically depending on which headers exist in data sheet
Next iCol
End Sub
这篇关于VBA-Excel从不同位置的不同工作表获取相同的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
