Hello code guru It is a program to merge all excel sheets to one sheet but I wrote this code on the base of VBA right now giving Activex error plzzzz check and conform me back
Code:
Private Sub Merge_Click()
Dim fso As New FileSystemObject
Dim fol As Folder
Dim i, j As Integer
Dim name1 As String
Fpath = Text1.Text
If Fpath = "" Then
MsgBox "Please select a path", vbExclamation
Else
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj = mergeObj.GetFolder(Text1.Text)
Set filesObj = dirObj.Files
Dim ThisWorkbook As New Excel.Workbook
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
'Hear at the time of save the merged files it gives ActiveX error
'Please rectify the error
'I wrote this code with the ref.of VAB code
ThisWorkbook.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End If
End Sub