如何在一个工作簿中将多个Excel spreasheets（.csv）聚合/编译成单独的工作表？ [英] How to aggregate/compile multiple Excel spreasheets (.csv) into separate sheets in one workbook?

问题描述

我不是一个程序员 - 但是我需要/想在Excel中编写一个命令，将多个.csv文件在一个工作簿中分成不同的工作表...它运行一次，并且复制/粘贴一个.csv的内容文件但是错误出现这个错误：

 运行时错误'438'：
 
对象不支持此属性或方法。 
  

我已经缩小到这一行：

 '粘贴
 ThisWorkbook.Worksheets（Sheets.Count）.Range（A1）。粘贴
  

但是，由于我是大多数所有这些的新手，我不确定该怎么做。到目前为止，我刚刚抓住了网络上似乎适用的代码片段。

  Private Sub CommandButton1_Click（）
 
 Dim strFile As String，strPath As String 
 Dim wkb As Workbook 
 
'为您自己的文件位置更改此路径：
 strPath =C：[FILE PATH HERE]
 
'如果文件不能被找到，则返回一个空字符串，如果文件夹不正确，将会出错
 
 strFile = Dir（strPath& * .csv）
 
尽管strFile<> 
打开csv文件并将其分配给变量，以便我们可以轻松地
引用它
设置wkb = Workbooks.Open（strPath& strFile）
 
'添加一个新的工作表在宏工作簿的最后粘贴到
 ThisWorkbook.Sheets.Add之后：= Sheets（Sheets.Count）
 
'获取范围和副本它
 wkb.Sheets（1）.UsedRange.Copy 
 Debug.Print（Sheets.Count）
'粘贴
 ThisWorkbook.Worksheets（Sheets.Count）.Range（ A1）。粘贴
 
'关闭csv文件
 wkb.Close 
 
'找到下一个文件 - 没有参数的目录将寻找下一个文件与第一个Dir调用匹配的文件夹
 strFile = Dir 
循环
 
 
 
 End Sub 
  pre> 
 
解决方案
 
  Sub Dougsloop（）
 
 Dim wbk As工作簿
 Dim Filename As String 
 Dim path As String 
 Dim wsO As Workbook 
 Dim StartTime As Double 
 Dim Seco ndsElapsed As Double 
 Dim aRR As Variant 
 Dim rowC As Long 
 Dim colC As Long 
 
 Application.ScreenUpdating = False 
 Application.DisplayAlerts = False 
 Application.Calculation = xlCalculationManual 
 
 StartTime = Timer 
 
 path =文件夹文件夹的路径& \
 Filename = Dir（path&.csv ??）
设置wsO = ThisWorkbook 
 wsO.Sheets（1）。选择
 
尽管Len（文件名）> 0 
 DoEvents 
设置wbk = Workbooks.Open（路径&文件名，True，True）
 aRR = wbk.Sheets（1）.UsedRange 
 rowC = wbk.Sheets 1）.UsedRange.Rows.Count 
 colC = wbk.Sheets（1）.UsedRange.Columns.Count 
 wsO.ActiveSheet.Range（wsO.ActiveSheet.Cells（1，1），wsO.ActiveSheet .Cells（rowC，colC））。Value2 = aRR 
 wbk.Close False 
 Filename = Dir 
 wsO.Sheets.Add After：= Worksheets（Worksheets.Count）
 Loop 
 
 Application.ScreenUpdating = True 
 Application.DisplayAlerts = True 
 Application.Calculation = xlCalculationAutomatic 
 
 SecondsElapsed = Round（Timer  -  StartTime，2）
 MsgBox此代码在&秒数seconds，vbInformation 
 
 End Sub 
  
 
I am NOT a programmer - but I need/want to write a command in Excel to aggregate multiple .csv files into separate sheets in one workbook... it runs once, and does copy/paste the contents of one .csv file but then errors out with this error: 
Runtime error '438':

Object does not support this property or method.
I've narrowed it down to this line: 
'paste it
ThisWorkbook.Worksheets(Sheets.Count).Range("A1").Paste
But, being that I am new to most all of this, I am unsure what to do. So far I have just been grabbing what seemed like applicable code snippets from around the web.
Private Sub CommandButton1_Click()

Dim strFile As String, strPath As String
Dim wkb As Workbook

'Change this path for your own file location:
strPath = "C:[FILE PATH HERE]"

'this returns an empty string "" if the file cannot be found and will error 
if the folder is incorrect
strFile = Dir(strPath & "*.csv")

Do While strFile <> ""
 'open the csv file and assign it to a variable so that we can easily 
 reference it later
Set wkb = Workbooks.Open(strPath & strFile)

 'add a new worksheet at the end of the macro workbook to paste into
ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)

 'get the range and copy it
wkb.Sheets(1).UsedRange.Copy
Debug.Print (Sheets.Count)
 'paste it
ThisWorkbook.Worksheets(Sheets.Count).Range("A1").Paste

 'close the csv file
wkb.Close

 'find the next file - Dir without parameters will look for the next file in the folder that matches the first Dir call
strFile = Dir
Loop



End Sub

解决方案
 Sub Dougsloop()

     Dim wbk As Workbook
     Dim Filename As String
     Dim path As String
     Dim wsO As Workbook
     Dim StartTime As Double
     Dim SecondsElapsed As Double
     Dim aRR As Variant
     Dim rowC As Long
     Dim colC As Long

     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
     Application.Calculation = xlCalculationManual

     StartTime = Timer

     path = "path to folder of files" & "\"
     Filename = Dir(path & "*.csv??")
     Set wsO = ThisWorkbook
     wsO.Sheets(1).Select

     Do While Len(Filename) > 0
         DoEvents
         Set wbk = Workbooks.Open(path & Filename, True, True)
         aRR = wbk.Sheets(1).UsedRange
         rowC = wbk.Sheets(1).UsedRange.Rows.Count
         colC = wbk.Sheets(1).UsedRange.Columns.Count
         wsO.ActiveSheet.Range(wsO.ActiveSheet.Cells(1, 1), wsO.ActiveSheet.Cells(rowC, colC)).Value2 = aRR
         wbk.Close False
         Filename = Dir
         wsO.Sheets.Add After:=Worksheets(Worksheets.Count)
     Loop

     Application.ScreenUpdating = True
     Application.DisplayAlerts = True
     Application.Calculation = xlCalculationAutomatic

     SecondsElapsed = Round(Timer - StartTime, 2)
     MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

 End Sub


                        
这篇关于如何在一个工作簿中将多个Excel spreasheets（.csv）聚合/编译成单独的工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！