Results 1 to 4 of 4

Thread: Export two Flexgrid data to Excel Sheet as multiple range

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Location
    Jeddah
    Posts
    13

    Export two Flexgrid data to Excel Sheet as multiple range

    Dear friends,
    I have two Flexgrids in a Form populating data, i wonna export to single Excel sheet of that both Flexgrid data. It is working fine if i export any one flexgrid data with a single range, but it is not working if i declare multiple range for those 2 flexgrid data. Please help me how to export data of 2 flexgrid to a single excel sheet. Please have a look at attached screen shots for my Flexgrids data and excel file.

    My Code:-
    ------------------------


    Dim r1, r2 As Range

    Dim lngRows As Long
    Dim lngCols As Long

    Dim lngRows1 As Long
    Dim lngCols1 As Long

    Set appexcel = New Excel.Application

    ffile = App.Path & "\Test.xls"


    Set exwork = appexcel.Workbooks.Open(ffile)
    Set ExcelSheet = appexcel.ActiveSheet

    appexcel.ActiveSheet.Cells(11, 1).Value = Format(Date, "dddd") & "-" & Format(Date, "dd/mmmm/yyyy")
    Set r1 = appexcel.Sheets("Report").Range("A13:E42")


    For lngRows = 1 To Flexgrid1.Rows - 1

    For lngCols = 0 To Flexgrid1.Cols - 1
    Screen.MousePointer = vbHourglass
    appexcel.Cells(lngRows + 13, lngCols + 1) = Flexgrid1.TextMatrix(lngRows, lngCols)

    Next lngCols
    Next lngRows



    Set r2 = appexcel.Sheets("Report").Range("G13:N42")

    For lngRows1 = 1 To Flexgrid2.Rows - 1
    For lngCols1 = 0 To Flexgrid2.Cols - 1
    Screen.MousePointer = vbHourglass


    appexcel.Cells(lngRows1 + 13, lngCols1 + 1) = Flexgrid2.TextMatrix(lngRows1, lngCols1)

    Next lngCols1
    Next lngRows1




    Screen.MousePointer = vbDefault
    appexcel.Visible = True
    Set appexcel = Nothing
    Attached Images Attached Images   

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Export two Flexgrid data to Excel Sheet as multiple range

    You haven't mentioned what the actual problem is (just "it is not working if i declare multiple range"), but here is an improved version of your code which fixes various issues:
    Code:
    Dim lngRows As Long
    Dim lngCols As Long
    
    Dim lngRows1 As Long
    Dim lngCols1 As Long
    
    Screen.MousePointer = vbHourglass
    
    Set appexcel = New Excel.Application
    
    ffile = App.Path & "\Test.xls"
    
    Set exwork = appexcel.Workbooks.Open(ffile)
    Set ExcelSheet = exwork.Sheets("Report")
    
    ExcelSheet.Cells(11, 1).Value = Format(Date, "dddd") & "-" & Format(Date, "dd/mmmm/yyyy")
    
    For lngRows = 1 To Flexgrid1.Rows - 1
        For lngCols = 0 To  Flexgrid1.Cols - 1
            ExcelSheet.Cells(lngRows + 13, lngCols + 1) = Flexgrid1.TextMatrix(lngRows, lngCols)
        Next lngCols
    Next lngRows
    
    For lngRows1 = 1 To  Flexgrid2.Rows - 1
        For lngCols1 = 0 To  Flexgrid2.Cols - 1
            ExcelSheet.Cells(lngRows1 + 13, lngCols1 + 1 + 6) =  Flexgrid2.TextMatrix(lngRows1, lngCols1)
        Next lngCols1
    Next lngRows1
    
    Screen.MousePointer = vbDefault
    appexcel.Visible = True
    Set ExcelSheet = Nothing
    Set exwork = Nothing
    Set appexcel = Nothing
    I'm assuming that adding the +6 to the second loop is the part that solves your current problem, but the other changes I've made are useful too.

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2007
    Location
    Jeddah
    Posts
    13

    Re: Export two Flexgrid data to Excel Sheet as multiple range

    First of all i would like to thank you so much for the quick and kind response....
    Yes that was the problem that i was facing and thanks a lot for fixing and helping me sir.
    But now with your updated code, still the other loop not copying the last two columns (Total and User)



    Dim lngRows As Long
    Dim lngCols As Long

    Dim lngRows1 As Long
    Dim lngCols1 As Long

    Screen.MousePointer = vbHourglass

    Set appexcel = New Excel.Application

    ffile = App.Path & "\Test.xls"

    Set exwork = appexcel.Workbooks.Open(ffile)
    Set ExcelSheet = exwork.Sheets("Report")

    ExcelSheet.Cells(11, 1).Value = Format(Date, "dddd") & "-" & Format(Date, "dd/mmmm/yyyy")

    For lngRows = 1 To Flexgrid1.Rows - 1
    For lngCols = 0 To Flexgrid1.Cols - 1
    ExcelSheet.Cells(lngRows + 13, lngCols + 1) = Flexgrid1.TextMatrix(lngRows, lngCols)
    Next lngCols
    Next lngRows

    For lngRows1 = 1 To Flexgrid2.Rows - 1
    For lngCols1 = 0 To Flexgrid2.Cols - 1
    ExcelSheet.Cells(lngRows1 + 13, lngCols1 + 1 + 6) = Flexgrid2.TextMatrix(lngRows1, lngCols1)
    Next lngCols1
    Next lngRows1

    Screen.MousePointer = vbDefault
    appexcel.Visible = True
    Set ExcelSheet = Nothing
    Set exwork = Nothing
    Set appexcel = Nothing

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Export two Flexgrid data to Excel Sheet as multiple range

    I'm afraid I can't see why that would be happening, the code looks like it should copy all the cells (except the header row).

    One minor change I didn't make before was to specify the Value property of the cells, eg:
    Code:
    ExcelSheet.Cells(lngRows + 13, lngCols + 1).Value = Flexgrid1.TextMatrix(lngRows, lngCols)
    That may fix it, but if not I recommend debugging to make sure the loop is actually reading all the values from the grid. If you don't know how to debug, our Classic VB FAQs (in the FAQ forum) links to a tutorial.

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