export report from VB to MS excel

Posted on 2010-08-25
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.
Question by:Mostafa_ElSadek

Expert Comment

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?
LVL 53

Accepted Solution

Dhaest earned 500 total points
ID: 33531534
VB6 - Flexgrid to Excel / Excel to Flexgrid. Fast copy using the Clipboard.

Export MSFlexGrid Values to Excel.
LVL 14

Expert Comment

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
      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
      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&)
      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 ***
      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
   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 ***
      d% = InStr(txt$, delim$)
      If d% = 0 Then
         If Found% Then
            If Found% + 1 = num% Then
               p$ = txt$
               p$ = ""
            End If
            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
   ParseLine$ = p$
End Function
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

LVL 14

Expert Comment

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

Author Comment

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?


Author Comment

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.
LVL 14

Expert Comment

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.

Author Comment

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.

LVL 14

Expert Comment

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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…

860 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