Solved

export report from VB to MS excel

Posted on 2010-08-25
9
617 Views
Last Modified: 2013-11-26
I have an application built by VB6 and I need to export the data on the Flixgrid to excel file.
I'm using also CR10 for reporting tools.
Thanks
Mostafa
0
Comment
Question by:Mostafa_ElSadek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 9

Expert Comment

by:gregcmcse
ID: 33525074
I'm not familiar with flixgrid -- if you can export as a CSV or TSV file, you can open that directly in Excel.  Should just be a matter of enumerating the fields for each record in output, shouldn't it?
I feel I'm missing something.  What part are you specifically struggling with?
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 33531534
VB6 - Flexgrid to Excel / Excel to Flexgrid. Fast copy using the Clipboard.
http://www.vbforums.com/showthread.php?t=393082

Export MSFlexGrid Values to Excel.
http://www.freevbcode.com/ShowCode.Asp?ID=1112
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 33561249
Use this (just pass it the FlexGrid's name. It will auto-size the column width and alignment too!:

Sub FlexExcel(fg As MSFlexGrid)
   Rem *** Get total number of records ***
   TotalRecs& = fg.Rows - 2
   
   If TotalRecs& = 0 Then
      MsgBox "Zero records to export", vbCritical
   Else
      On Error GoTo GoofedUp
      Rem *** Set up Progress Bar ***
      leg$ = Lang$(453) 'XL "Processing data"
      Call BigGauge(leg$, TotalRecs& + 1)
      Rem *** Do da Excel magic! ***
      Dim AppExcel As Variant, txt$
      Set AppExcel = CreateObject("Excel.application")
      AppExcel.Visible = False
      AppExcel.Workbooks.add
      Rem *** Add column headers ***
      For c% = 0 To fg.Cols - 1
         AppExcel.cells(1, c% + 1).Font.Bold = True
         AppExcel.cells(1, c% + 1).formula = fg.TextMatrix(0, c%)
         AppExcel.cells(1, c% + 1).borders.Weight = 2
      Next c%
      r& = 1
      Rem *** Add data ***
      While cnt& < TotalRecs&
         r& = r& + 1
         txt$ = FlexGet$(fg, r&)
         For c% = 0 To fg.Cols - 1
            AppExcel.Range(Chr$(65 + c%) & CStr(r&)) = ParseLine$(txt$, vbTab, c% + 1)
         Next c%
         cnt& = cnt& + 1
         Call BigGauge("", cnt&)
      Wend
      Rem *** Add cell borders ***
      AppExcel.Range("A2:" & Chr$(64 + fg.Cols) & CStr(TotalRecs& + 1)).borders.Weight = 1
      Rem *** Resize all columns to width of their content ***
      AppExcel.activesheet.Columns.autofit
      Rem *** Set proper column alignment
      For c% = 0 To fg.Cols - 1
         Select Case fg.ColAlignment(c%)
            Case 0 To 2 'Left
               a% = 2
            Case 3 To 5 'Center
               a% = 3
            Case 6 To 8 'Right
               a% = 4
            Case Else   'Contents
               a% = 1
         End Select
         AppExcel.activesheet.Columns(Chr$(65 + c%)).horizontalAlignment = a%
      Next c%
      AppExcel.Visible = True
   End If
   
   Call BigGauge("Q", 0)
   Exit Sub
GoofedUp:
   If Err.Number >= 1 Then
      MsgBox Err.Description, vbCritical, Err.Number
   End If
End Sub
BigGauge is just my "progress bar". Either use your own, or comment the lines out. Here are the two other routines it uses:
Function FlexGet$(fg As MSFlexGrid, r&)
   If r& < 2 Then r& = fg.Row
   txt$ = fg.TextMatrix(r&, 0)
   For c% = 1 To fg.Cols - 1
      txt$ = txt$ & vbTab & fg.TextMatrix(r&, c%)
   Next c%
   FlexGet$ = txt$
End Function
Function ParseLine$(ByVal txt$, delim$, num%)
   Rem *** Parse line ***
   Do
      d% = InStr(txt$, delim$)
      If d% = 0 Then
         If Found% Then
            If Found% + 1 = num% Then
               p$ = txt$
            Else
               p$ = ""
            End If
         Else
            If num% = 1 Then p$ = txt$
         End If
         Exit Do
      End If
      p$ = Left$(txt$, d% - 1)
      txt$ = Right$(txt$, Len(txt$) - d%)
      Found% = Found% + 1
      If Found% = num% Then Exit Do
   Loop
   ParseLine$ = p$
End Function
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 33561283
Oops! You should be aware I set up all my FlexGrid's to have two rows when empty...the header row and an almost invisible second row (height of 15 twips) that makes it look nice. It also makes clearing the contents without removing the header row possible. Anyway, I'm sure you don't use this extra row, so statements like:
 TotalRecs& = fg.Rows - 2

in my code should really be:
 TotalRecs& = fg.Rows - 1
0
 

Author Comment

by:Mostafa_ElSadek
ID: 33679638
Sorry I tryied to use your code but I have one problem is that:
your code has one flex grid and two functions when I add the functions exactly on the genreal or what?

Thanks
0
 

Author Comment

by:Mostafa_ElSadek
ID: 33679721
I go an error message for (Sub FlexExcel(fg As MSFlexGrid1)) as user-defined type not defined.

Should I add any decliration or add any compenent .

I have already FlexGrid1 that has the retreived data.
Thanks
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 33681549
Are you using an MSFlexGrid or an MSHFlexGrid? If MSH, change the passed parameter accordingly. Also, yes the other two functions should be added either in the same form's code, or declared public in a BAS module.
0
 

Author Comment

by:Mostafa_ElSadek
ID: 33688733
I'm using MSHFlexGrid
and I added the Function on the base module but what type of variable I should define the FlexGet and Parseline.

Thanks
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 33691213
Just change the main routine's first line to:
Sub FlexExcel(fg As MSHFlexGrid1)
also change the FlexGet$ routine's line to:
Function FlexGet$(fg As MSHFlexGrid, r&)
where MSHFlexGrid1 is the name of your MSHFlexGrid, of course.
ParseLine is fine as it is.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 22 hours left to enroll

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question