Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

export report from VB to MS excel

Posted on 2010-08-25
9
Medium Priority
?
625 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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.
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…
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…
Suggested Courses
Course of the Month10 days, 8 hours left to enroll

571 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