英文:
Combining multiple files into one but subscript out of range error on second iteration of loop
问题
我有一个包含.csv文件的文件夹,每个文件都包含一列数据,但行数不同。
我想要将每个.csv文件的数据复制到一个输出工作表中(在包含VBA代码的主工作簿内)。
.csv文件按顺序编号(比如,1.csv,2.csv等)。
在输出工作表上,我希望将复制的数据并排放置(因此来自1.csv的数据将粘贴到输出的A列中,来自2.csv的数据将粘贴到输出的B列中,依此类推)。
我编写了一个循环,将数据从每个.csv文件复制到主工作簿中的名为scratch的工作表中。
然后我将数据复制到输出,以对数据进行某些操作。
第一次运行时没有问题,但一旦进行到2.csv,我在将.csv数据复制到scratch时出现错误。
下标超出范围
我试图理解为什么会出现错误。(就方法而言,我知道如何做,因为几年前的代码是可以的。)
英文:
I have a folder of .csv files, each containing one column of data, but the number of rows varies.
I want to copy the data from each .csv file onto an output worksheet (inside a master workbook where the VBA code lives).
The .csv files are numbered sequentially (say, 1.csv, 2.csv, etc.).
On the output worksheet, I want to place the copied data side by side (so the data from 1.csv will be pasted in column A in output and data from 2.csv will be pasted in column B in output and so on.
I wrote a loop to copy the data from each .csv file, to a worksheet called scratch in the same master workbook.
Then I copy the data to output, to do something to the data.
It ran the first time, but as soon as it progressed to 2.csv, I got an error where I copy the .csv data to scratch.
>subscript out of range
I'm trying to understand why I get the error. (In terms of approach I know how to do it because the code worked several years ago.)
Public Sub Combine()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim filePath As String 'folder path of csv files, numbered sequentially, say if 10 files 1, 2, 3.......10.csv)
Dim fileMin As Integer 'number of the first csv file (e.g. 1)
Dim fileMax As Integer 'number of the last csv file (e.g. 10)
Dim fileNumber As Integer
Dim fileName As String
Dim rngSource As Range
Dim CLastFundRow As Long
Dim CFirstBlankCol As Long
'I'm on a mac
filePath = ThisWorkbook.Path
If Right(filePath, 1) = ":" Then
filePath = filePath & "Folder/"
Else
filePath = filePath & "/Folder/"
End If
fileMin = 1
fileMax = 10
For fileNumber = fileMin To fileMax
fileName = fileNumber & ".csv"
Workbooks.OpenText (filePath & fileName)
Windows(fileName).Activate
'Copy and paste data from .csv file to Scratch
'where on 2nd loop "subscript out of range" error occur
Workbooks("Master.xlsm").Worksheets("Scratch").Range("A1:A100").Value2 = Workbooks(fileName).Worksheets(fileNumber).Range("A1:A100").Value2
Windows(fileName).Close saveChanges:=False
Workbooks("Master.xlsm").Activate
'Finds last row of content in Scratch
CLastFundRow = Workbooks("Master.xlsm").Worksheets("Scratch").Cells(Rows.Count, 1).End(xlUp).Row
'Finds first column without content in Output
CFirstBlankCol = Workbooks("Master.xlsm").Worksheets("Output").Cells(1, Columns.Count).End(xlToLeft).Column + 1
'Copy and paste data from Scratch to Output
Set rngSource = Workbooks("Master.xlsm").Worksheets("Scratch").Range("A1:A" & CLastFundRow)
With ThisWorkbook.Worksheets("Output")
.Range(.Cells(1, CFirstBlankCol), .Cells(CLastFundRow, CFirstBlankCol + 1)).Value2 = rngSource.Value2
End With
Set rngSource = Nothing
Workbooks("Output.xlsm").Worksheets("Scratch").Cells.Clear
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
答案1
得分: 0
如@CDP1802指出的,我的问题与fileNumber有关,.csv文件只有一个表格。在纠正了这个问题之后,我的原始代码可以正常运行。尽管出现了新的问题,即每当代码要访问每个文件时(代码试图复制然后粘贴到主文件中),我会收到要授予访问权限的提示。我发现这与苹果的沙盒规则有关。故事的寓意是,微软产品和Mac不太合拍。我通过使用AppleScript解决了这个问题(打开所有文件然后在Excel中关闭它)。或者你可以像这个帖子中的评论者那样,将文件重命名为“temporary”以规避沙盒规则。
英文:
As @CDP1802 pointed out, my issue has to do with fileNumber, .csv files only have one sheet. after correcting that, my original code works. although new issue cropped up, namely I get prompted to grant access whenever the code wants to access each file (that the code is trying to copy then paste into the master). I found out this has to do with Apple's sandbox rules. Moral of the story is, MS products don't play nicely with Mac. Go figures. I worked around this issue by using Applescript (to open all my files then close it in Excel). Or you can do what the commenter did in this post (scroll down) and rename your files to make it appear as "temporary" to skirt around the sandbox rules.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论