CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12

Thread: Excel and VB 6

  1. #1
    Join Date
    Nov 2013
    Posts
    11

    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
    Last edited by HanneSThEGreaT; November 7th, 2013 at 06:19 AM.

  2. #2
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Excel and VB 6

    [ Moved to separate Thread ]

  3. #3
    Join Date
    Nov 2013
    Posts
    11

    Lightbulb Multiple Excel files merged in to one sheet

    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

    Merge.zip
    Last edited by HanneSThEGreaT; November 7th, 2013 at 08:28 AM. Reason: removed email

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Excel and VB 6

    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?
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Nov 2013
    Posts
    11

    Re: Excel and VB 6

    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
    Last edited by GremlinSA; November 8th, 2013 at 01:02 AM. Reason: added code tags

  6. #6
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Excel and VB 6

    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
    Always use [code][/code] tags when posting code.

  7. #7
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: Excel and VB 6

    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...
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  8. #8
    Join Date
    Nov 2013
    Posts
    11

    Re: Excel and VB 6

    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
    Last edited by GremlinSA; November 8th, 2013 at 04:28 AM. Reason: added code tags

  9. #9
    Join Date
    Jun 2005
    Location
    JHB South Africa
    Posts
    3,772

    Re: Excel and VB 6

    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....
    Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
    WPF Articles : 3D Animation 1 , 2 , 3
    Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
    Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
    All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.

  10. #10
    Join Date
    Nov 2013
    Posts
    11

    Re: Excel and VB 6

    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
    Last edited by HanneSThEGreaT; November 8th, 2013 at 06:18 AM. Reason: code tags.

  11. #11
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Excel and VB 6

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

  12. #12
    Join Date
    Nov 2013
    Posts
    11

    Re: Excel and VB 6

    sorry next time I am using

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured