VBA Excel - 编译错误 - 属性使用无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25430306/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel - Compile Error - Invalid Use of property
提问by Doug
I'm brand new, my first script EVER. So thanks in advance for any help you can offer.
我是全新的，我的第一个脚本。因此，在此先感谢您提供的任何帮助。
In a few days, I'm going to receive a set of surveys from 200+ affiliates of my company. The survey data is being collected in individual excel spreadsheets.
几天后，我将收到来自我公司 200 多个附属公司的一组调查。调查数据收集在单独的 Excel 电子表格中。
I am trying to modify a script I got from a Microsoft website that loops through all spreadsheets and compiles the data to a single spreadsheet.
我正在尝试修改从 Microsoft 网站获得的脚本，该脚本循环遍历所有电子表格并将数据编译为单个电子表格。
The error I am getting is: Compile Error: Invalid Use of Property
我得到的错误是：编译错误：属性的无效使用
Here's my code:
这是我的代码：
Sub MergeGTISurvey()
Dim SurveySummary As Worksheet
Set SurveySummary = Workbooks.Add(xlWBATWorksheet).Worksheets
Dim FolderPath As String
FolderPath = "C:\Users\dloots\mycompany\testsurveyfolder\"
Dim NRow As Long
NRow = 1
Dim Filename As String
Filename = Dir(FolderPath & "*.xl*")
Do While Filename <> ""
Dim WorkBk As Workbook
Set WorkBk = Workbooks.Open(FolderPath & Filename)
SurveySummary.Range("A" & NRow).Value = Filename
Dim Sheet As Worksheets
Set Worksheets = Sheet
Dim SourceRange As Range
Set SourceRange = WorkBk.Worksheets("Network").Range("B4:B16").Select
Dim DestRange As Range
Set DestRange = SurveySummary.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)
DestRange.Value = SourceRange.Value
NRow = NRow + DestRange.Rows.Count
WorkBk.Close savechanges:=False
Filename = Dir()
Loop
采纳答案by Tak
Here is a modified version of your code. You can try it out. You may still need to modify some of the range
这是您的代码的修改版本。你可以试试看。您可能仍需要修改某些范围
Sub MergeGTISurvey()
Dim SurveySummary As Workbook
Set SurveySummary = Workbooks.Add(xlWBATWorksheet)
Dim SurveySummarySheet As Worksheet
Set SurveySummarySheet = SurveySummary.ActiveSheet
Dim FolderPath As String
FolderPath = "C:\Users\dloots\mycompany\testsurveyfolder\"
Dim NRow As Long
NRow = 1
Dim Filename As String
Filename = Dir(FolderPath & "*.xl*")
Do While Filename <> ""
Dim WorkBk As Workbook
Set WorkBk = Workbooks.Open(FolderPath & Filename)
SurveySummarySheet.Range("A" & NRow).Value = Filename
Dim Worksht As Worksheet
Set Worksht = WorkBk.Worksheets("Network")
Worksht.Range("B4:B16").Copy
SurveySummarySheet.Range("B" & CStr(NRow)).PasteSpecial
' This will get last row after paste
NRow = SurveySummarySheet.Cells.SpecialCells(xlLastCell).Row + 1
WorkBk.Close savechanges:=False
Filename = Dir()
Loop
End Sub
