VbScript Error for Excel

[edTech]

I have created a script that is failing in one line and I have looked for the past 6 weeks on why its failing with no results can you please help. The error produced is: Select method of Range class failed (line failure noted in red)

Option Explicit

'Define variables
Dim strPathSrc, strMaskSrc, iSheetSrc, strPathDst, iSheetDst, objExcel, objWorkBookDst, objSheetDst, objShellApp, objFolder, objItems, objItem, objUsedRangeDst, objWorkBookSrc, objSheetSrc, iRowsCount, sSubF, objfso

'Source files folder
strPathSrc = "C:\Testing\"

'Source files filter mask
strMaskSrc = "*.xlsx"

'Source sheet index or name
iSheetSrc = 1

'Destination file
strPathDst = "H:\Results.xlsx"

'Destination sheet index or name
iSheetDst = "Sheet1"

Set objfso = CreateObject("Scripting.FileSystemObject")

'Set up object
Set objExcel = CreateObject("Excel.Application")

'hide sheet viewing
objExcel.Visible = False

'Open excel workbooks
Set objWorkBookDst = objExcel.Workbooks.Open(strPathDst)

'Define sheets
Set objSheetDst = objWorkBookDst.Sheets(iSheetDst)

'Define Shell app
Set objShellApp = CreateObject("Shell.Application")

'Deine folder path
'Set objFolder = objShellApp.NameSpace(strPathSrc)

Set objFolder = objfso.GetFolder(strPathSrc)

'msgbox objFolder

Set sSubF = objFolder.SubFolders

'Set objitems = sSubf.Items()

For each objItem in sSubF

dim sFolder, colFiles, x

Set sFolder = objfso.GetFolder(objitem.path)

Set colFiles = sFolder.files

objExcel.DisplayAlerts = False

For each x in colFiles

Set objWorkBookSrc = objExcel.Workbooks.Open(x.Path)

Set objSheetSrc = objWorkBookSrc.Sheets(iSheetSrc)

GetUsedRange(objSheetSrc).Copy

Set objUsedRangeDst = GetUsedRange(objSheetDst)

iRowsCount = objUsedRangeDst.Rows.Count

objWorkBookDst.Activate

objSheetDst.Cells(iRowsCount + 1, 1).Select 'Failing here

objSheetDst.Paste

objWorkBookDst.Application.CutCopyMode = False

objWorkBookSrc.Close
Next

next

 

[JoeMo]

objSheetDst.Cells(iRowsCount + 1, 1).Select 'Failing here

Possibly either Sheet1 is not the active sheet when the failing line executes (you can't select a cell on Sheet1 if Sheet1 isn't the active sheet), or iRowsCount is equal to the last row number on the sheet so iRowsCount +1 is literally off the sheet.

 

[edTech]

Thank you JoeMo, I changed it to objSheetDst.Cells(iRowsCount, 1).Select and it copies the data but when I change it to start in Row 2 on the function it truncates the first set of data into the Master Workbook. File 1 gets Truncated.

Original Dataset:

File1:

Fnam	Lname
John	Doe
1​	2​	3​
4​	5​	6​
20​	23​	25​

File2:

Fname	Lname
John	Smith
1​	2​	3​
45​	6​	8​	7​
2​	8​	9​

Results Workbook:

John	Doe
1​	2​	3​
John	Smith
1​	2​	3​
45​	6​	8​	7​
2​	8​	9​

 

[edTech]

JoeMo, I have it working now. I changed to this objSheetDst.Cells(iRowsCount, 1).Select and changed function to + 1 instead of - 1 and it works. Thank you

 

[JoeMo]

JoeMo, I have it working now. I changed to this objSheetDst.Cells(iRowsCount, 1).Select and changed function to + 1 instead of - 1 and it works. Thank you

You are welcome - thanks for the reply.

 

[edTech]

JoeMo, I spoke too soon when I turn off the objExcel.Visible = false, I get the same error again. Its almost like it selects the data while its open, but when I do not make it visible it loses itself.. . Odd.

 

[JoeMo]

JoeMo, I spoke too soon when I turn off the objExcel.Visible = false, I get the same error again. Its almost like it selects the data while its open, but when I do not make it visible it loses itself.. . Odd.

You can't select a range on a worksheet that's not the active sheet - the sheet must be visible.

 

[edTech]

Thank you JoeMo, how do I modify it to make so it does not have to be visible?