Solved

export report from VB to MS excel

Posted on 2010-08-25
9
616 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
Technology Partners: 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!

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

734 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