-
May 24th, 2015, 01:49 AM
#1
Thread Starter
New Member
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
-
May 24th, 2015, 05:46 AM
#2
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.
-
May 24th, 2015, 07:29 AM
#3
Thread Starter
New Member
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
-
May 25th, 2015, 10:08 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|