i am new to vb script and dont know much so please help.
我是vb脚本的新手,不太了解所以请帮助。
I have a folder, which consists of many sub-folders . Each sub-folder has 10+ excel sheets in it. My aim is to copy the data from each and every excel file from all the sub-folders to a single excel sheet. the problem is i have written a code, but it is overwriting so the data gets deleted. And we have same header in all the excel files, i want the header to appear only once in the main excel sheet . please help and thnakyou in advance.
我有一个文件夹,它包含许多子文件夹。每个子文件夹中都有10多个excel表。我的目标是将每个excel文件中的数据从所有子文件夹复制到单个Excel工作表。问题是我写了一个代码,但它被覆盖,所以数据被删除。我们在所有excel文件中都有相同的标题,我希望标题只在主Excel工作表中出现一次。请提前帮助和thnakyou。
'Sub RunCodeOnAllXLSFiles()
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
strPath = ":\Documents and Settings\faizat\Desktop\leeza"
pathName="xlsx"
If strPath = "" Then WScript.quit
If pathName = "" Then WScript.quit
'Creating an Excel Workbook in My Documents
Set objWorkbook2= objExcel.Workbooks.Add()
objExcel.Visible = True
objExcel.DisplayAlerts = False
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder (strPath)
Set objsubFolder = objfolder.subFolders
Set objfile = objsubfolder.files
For Each objsubfolder In objfolder.subfolders
For Each objFile In objsubFolder.Files
If objFso.GetExtensionName (objFile.Path) = "xlsx" Then
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
Set objWorksheet = objWorkbook.WorkSheets(1)
objworksheet.Activate
' Select the range on Sheet1 you want to copy
objWorkbook.Worksheets("SHEET1").usedrange.Copy
objworkbook.close
Set objRange = objExcel.Range("A2")
intNewRow = objExcel.ActiveCell.Row + 10
strNewCell = "A" & intNewRow
objExcel.Range(strNewCell).Activate
For i = 1 To usedrange
objWorksheet.Cells(intNewRow, 1) = i * 1
intNewRow = intNewRow + i
Next
' Paste it on sheet1 of workbook2, starting at A1
objWorkbook2.Worksheets("Sheet1").Range(strNewCell).PasteSpecial
Set objWorksheet = objWorkbook2.Worksheets(1)
End If
Next
Next
1 个解决方案
#1
0
For i = 1 To usedrange
objWorksheet.Cells(intNewRow, 1) = i * 1
intNewRow = intNewRow + i
Next
You never initialize the variable usedrange
, so your loop never increments intNewRow
. Initialize intNewRow
with the value 1 at the beginning of your script, and use something like this in the inner loop:
您永远不会初始化变量usedrange,因此您的循环永远不会增加intNewRow。在脚本开头用值1初始化intNewRow,并在内部循环中使用类似的东西:
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
If intNewRow = 1 Then
startrow = 1
Else
startrow = 2
End If
endrow = objWorkbook.Worksheets("SHEET1").UsedRange.Rows.Count
objWorkbook.Worksheets("SHEET1").Range(startrow & ":" & endrow).Copy
objWorkbook2.Worksheets("Sheet1").Cells(intNewRow, 1).PasteSpecial
objWorkbook.close
intNewRow = intNewRow + (endrow - startrow - 1)
#1
0
For i = 1 To usedrange
objWorksheet.Cells(intNewRow, 1) = i * 1
intNewRow = intNewRow + i
Next
You never initialize the variable usedrange
, so your loop never increments intNewRow
. Initialize intNewRow
with the value 1 at the beginning of your script, and use something like this in the inner loop:
您永远不会初始化变量usedrange,因此您的循环永远不会增加intNewRow。在脚本开头用值1初始化intNewRow,并在内部循环中使用类似的东西:
Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
If intNewRow = 1 Then
startrow = 1
Else
startrow = 2
End If
endrow = objWorkbook.Worksheets("SHEET1").UsedRange.Rows.Count
objWorkbook.Worksheets("SHEET1").Range(startrow & ":" & endrow).Copy
objWorkbook2.Worksheets("Sheet1").Cells(intNewRow, 1).PasteSpecial
objWorkbook.close
intNewRow = intNewRow + (endrow - startrow - 1)