Excel and VB 6

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

---

[ Moved to separate Thread ]

I biginner to VB Devoloper and I got first project only confusion so please help me friends
This is a Excel merging program but it gives some Activex error plz anybody help me fast
this is my email :


Thank you friends

Attachment 32051

It would help if you told us what the error message is that you are getting. Some activeX error could mean almost anything.
It would also help if you clarify what you are coding in is it VBA [Excel] or is it in VB6?

The Error message is : ActiveX component cannot be create object errorActualy it is a VBA code I modified to use in VB 6.0
is it able to modify like after merging it ask to user to save plz sir


That code is :

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) 'this is the path of Excelfiles folder
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 component cannot be create object 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

---

I see that you dimmed a Var named ThisWorkbook but you did not set it to anything

I do not do coding with Excel so can't be of much help

Code:

---

Dim bookList As Workbook
---------
Dim ThisWorkbook As New Excel.Workbook
---------
Set bookList = Workbooks.Open(everyObj)
---------
ThisWorkbook.Worksheets(1).Activate

---

You've initiated two different variables, set instance to one and trying to use the other to work with the workbook.. merge these two variables, and the error should be resolved...

Sir I changed but same error was showing. and also it is not asking user to save this please check sir

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) 'this is the path of Excelfiles folder
    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 component cannot be create object 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
    ThisWorkbook.Worksheets(1).Close
    ThisWorkbook.Save
    End If
End Sub

---

Code:

---

    Dim bookList As Workbook
--------
    Dim ThisWorkbook As New Excel.Workbook
--------
        Set bookList = Workbooks.Open(everyObj)
--------
        ThisWorkbook.Worksheets(1).Activate
--------
    ThisWorkbook.Worksheets(1).Close
    ThisWorkbook.Save

---

you have not changed anything.... Use Either booklist or ThisWorkbook but NOT BOTH...

Also .... Save Before Close .. not close then save....

Hello sir,


In this program all the excel files are merging in one sheet but after merging all the source files are in red only form and also after merging all the source files are not closing at the same time excel gets struck.
Please rectify the errors
I am new to programming field so please ignore my mistakes

Code:

---

Private Sub Merge_Click()
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) 'this is the path of Excelfiles folder
    Set filesObj = dirObj.Files
    Dim ThisWorkbook As New Excel.Workbook
    For Each everyObj In filesObj
   
        Set ThisWorkbook = Workbooks.Open(everyObj)
        Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
       
        'Hear at the time of save the merged files it gives ActiveX component cannot be create object 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
       
    Next
        'ThisWorkbook.Worksheets(1).Close
        cdc1.Filter = "Status File (*.xls, *.xlsx)|*.xls;*.xlsx"
        cdc1.ShowSave

        If cdc1.FileName <> "" Then
            ThisWorkbook.SaveAs (cdc1.FileName)
            MsgBox "Saved Successfully.", vbInformation, "Save Output"
            ThisWorkbook.Close
            Unload Me
        End If
    End If
End Sub

---

nil.kul007, please use [ CODE] tags when posting code. It makes the code nice to read.

sorry next time I am using