[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


vb6 - Transfer from MSHFlexgrid1 to xls issue

Posted on 2016-11-28
Medium Priority
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 50

Expert Comment

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

Author Comment

ID: 41904453
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 50

Expert Comment

by:Martin Liss
ID: 41904468
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.
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 11

Author Comment

ID: 41904481
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

LVL 50

Expert Comment

by:Martin Liss
ID: 41904503
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 50

Accepted Solution

Martin Liss earned 2000 total points
ID: 41904532
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

ID: 41904535
This is exactly what i needed. Thank you again for your help
LVL 50

Expert Comment

by:Martin Liss
ID: 41904536
You're welcome.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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 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…
Suggested Courses

872 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