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

Thread: Split excel files using vb6.0

  1. #1
    Join Date
    Nov 2013

    Lightbulb Split excel files using vb6.0

    Hello Friends,

    I used below code for merging excel file from path in visual basic 6.0. Now I'm thinking to split using particular cell reference from the merged excel file in separate sheets ... (like in cell(1,2) this cell column will be the reference & on that base we have to split and paste is it possible friends then plz suggest me)

    Private Sub Command2_Click()
    Dim fso As New FileSystemObject
    Dim fol As Folder
    Dim i, j, k As Long
    Dim name1 As String
    Fpath = Text1.Text
    If Fpath = "" Then
        MsgBox "Please select a path", vbExclamation
        Set fol = fso.GetFolder(Fpath)
        j = fol.Files.Count
        MsgBox " the number of files ur selected for merging is " & j
        Dim xlApp As New Excel.Application
        Dim wbN As Excel.Workbook
        Dim wsN As Excel.Worksheet
        Dim wb  As Excel.Workbook
        Dim ws As Excel.Worksheet
        Dim sNewFullName As String
        'Dim i As Integer
            Set objFiles = fso.GetFolder(Fpath).Files
            Set wbN = xlApp.Workbooks.Add
            Set wsN = wbN.Worksheets(1)
            Set wb = xlApp.Workbooks.Add
            Set ws = wb.Worksheets(1)
            'Hear merging willb happning hear
            For Each objfile In objFiles
                Dim Aad_Row As Long
                Dim Add_Col As Long
                Dim Mrg_Row As Long
                Dim Mrg_Col As Long
                'wsN.Name = "Merged"
                Mrg_Row = 0
                Mrg_Col = 0
                Form2.Caption = objfile.Name
                Mrg_Row = wsN.UsedRange.Rows.Count
                Mrg_Col = wsN.UsedRange.Columns.Count
                Set wb = xlApp.Workbooks.Open(objfile.Path)
                Set ws = wb.Worksheets(1)
                    'Actual Combining process will starts hear
                    Aad_Row = ws.UsedRange.Rows.Count
                    Add_Col = ws.UsedRange.Columns.Count
                    'MsgBox "Number of Rows = " & Aad_Row
                    wsN.Range(wsN.Cells(Mrg_Row + 1, 1), wsN.Cells(Mrg_Row + Aad_Row, Add_Col)).NumberFormat = "@"
                    wsN.Range(wsN.Cells(Mrg_Row, 1), wsN.Cells(Mrg_Row + Aad_Row, Add_Col)).Value = ws.Range(ws.Cells(1, 1), ws.Cells(Aad_Row + 1, Add_Col)).Value
                    'MsgBox "Rows : " & Aad_Row
            'For First row comming blank so I Deleted Hear
                'wsN.Range(wsN.Cells(1, 1), wsN.Cells(1, 150)).Delete
            'It is the process of saving
                ccd1.Filter = "Status File (*.xls, *.xlsx)|*.xls;*.xlsx"
                If ccd1.FileName <> "" Then
                    wbN.SaveAs (ccd1.FileName)
                    MsgBox "Saved Successfully..."
                    Unload Me
                End If
    End If  End Sub
    Last edited by HanneSThEGreaT; November 18th, 2013 at 07:24 AM. Reason: code tags

Tags for this Thread

Posting Permissions

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

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

On-Demand Webinars (sponsored)