Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

export report from VB to MS excel

Posted on 2010-08-25
9
Medium Priority
?
619 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

660 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