vb6 - Transfer from MSHFlexgrid1 to xls issue

Posted on 2016-11-28
Last Modified: 2016-11-28

In attachment, you will have a sample of the project.

When you run the project, you will see that in MSHFlexgrid.TextMatrix(1,23), you have a long value. This is a concatenation of 3 textbox:
MSHFlexGrid1.TextMatrix(1, 23) = od.Text & plan_id.Text & dc.Text

Open in new window


The problem is that the value causes some feed line when i want to keep the value in the same cell when extracting.

this is the code to export into Excel:
 Dim xlObj As Object    'New Excel.Application -- only used with Excel reference
    Dim wkbOut As Object   'Excel.Workbook
    Dim wksOut As Object   'Excel.Worksheet
    Dim rngOut As Object   'Excel.Range
    Dim sngStart As Single    'forperformance measurement
    Dim start_time As Long
    Dim end_time As Long
    Dim total_time As Long
    Dim FileNm As Variant

    On Error Resume Next

    'Save file to
    With CommonDialog1
        .DialogTitle = "Audit analysis..."
        .FileName = "test" & " conversion " & Format(Date, "mmmm dd, yyyy")
        .CancelError = True  '<--moved
        '.Filter = "Excel Files (*.xls)|*.xls"
        .Filter = "Spreadsheet Files (*.xls)|*.xls| 2k7 Excel Files (*.xlsx)|*.xlsx"

        If Err.Number = 32755 Then
            MsgBox "not saved - user pressed cancel or closed the dialog"
            Exit Sub
            FileNm = .FileName
            path_link = "TEST" & ".xls"
        End If

    End With

    'output to Excel workbook
   ' lblStatus.Caption = "Begin Excel Data Export"
    Set xlObj = CreateObject("Excel.Application")
    Set wkbOut = xlObj.Workbooks.Add
    Set wksOut = wkbOut.Worksheets("Sheet1")  'can skip this step
    Set rngOut = wksOut.Range("A2")           'by replacing with wkbOut.Worksheets("Sheet1").Range("A1")

    Me.MousePointer = vbHourglass
    Me.Enabled = False

    xlObj.ScreenUpdating = False
    xlObj.Calculation = -4135     '=xlCalculationManual

    'BulkLoad rngOut, sngData
    Clipboard.Clear    'Clear the Clipboard
    With MSHFlexGrid1
        .Col = 0
        .Row = 0
        .ColSel = .Cols - 1
        .RowSel = .Rows - 1
        Clipboard.SetText .Clip
    End With
    With xlObj.ActiveWorkbook.ActiveSheet
        .Range("A2:BC2").Interior.Color = RGB(205, 197, 191)
        .Columns("A:BC").NumberFormat = "@"
        .Columns("B:BC").HorizontalAlignment = xlCenter
        .Columns("F:BC").WrapText = True
        .Columns("A:BC").Borders.LineStyle = xlContinuous
        .Range("A1").HorizontalAlignment = xlLeft
        .Range("A1") = flat_file_name.Text & " Excel format " & Format(Date, "mmmm dd, yyyy")
        .Range("A1").HorizontalAlignment = xlLeft
        .Range("A1").Font.Bold = True
        .Range("A1").Font.Size = 20
        ActiveWindow.FreezePanes = True
        .Range("B1:D1").HorizontalAlignment = xlCenterAcrossSelection
        .Range("E1:F1").HorizontalAlignment = xlCenterAcrossSelection
        .Range("G1:H1").HorizontalAlignment = xlCenterAcrossSelection
    End With

    xlObj.ActiveWorkbook.SaveAs FileNm

   ' xlObj.Calculation = -4105     '=xlCalculationAutomatic
    xlObj.ScreenUpdating = True
    xlObj.Visible = True

    Set rngOut = Nothing
    Set wksOut = Nothing
    Set wkbOut = Nothing
    Set xlObj = Nothing

Open in new window

How can i do that?

Question by:Wilder1626
  • 5
  • 3
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
When I run the program I see this. What should it look like?
LVL 11

Author Comment

Comment Utility
Look in column 23 and you should see (MSHFlexGrid1.TextMatrix(1, 23) = od.Text & plan_id.Text & dc.Text) value into 1 single cell.
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
When I run your app I get log file errors. What type of controls are they?

Line 27: Property Text in od had an invalid file reference.
Line 38: Property Text in plan_id had an invalid file reference.
Line 49: Property Text in dc had an invalid file reference.
LVL 11

Author Comment

Comment Utility
strange. They are only text box multiLine. and it looks like the text box don't keep the text in it when i zip it.

Below you will see what's in the od textbox. Just put the same value in plan_id and dc textbox
In od TextBox, i have the value:


mber Value="

Open in new window

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Someplace before you fill the 3 textboxes you need to do something like this where MyInput is the string that's going to be in the textboxes.
MyInput = Replace(MyInput, vbCrLf, "")

Open in new window

LVL 45

Accepted Solution

Martin Liss earned 500 total points
Comment Utility
You could also do it after filling the textboxes. For example

od.Text = Replace(od.Text, vbCrLf, "")

Open in new window

LVL 11

Author Closing Comment

Comment Utility
This is exactly what i needed. Thank you again for your help
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You're welcome.

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 …
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…
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…
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now