Solved

vb6 - Transfer from MSHFlexgrid1 to xls issue

Posted on 2016-11-28
8
9 Views
Last Modified: 2016-11-28
Hi,

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"
        .ShowSave


        If Err.Number = 32755 Then
            MsgBox "not saved - user pressed cancel or closed the dialog"
            Exit Sub
        Else
            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").Select
        .Range("A2:BC2").Interior.Color = RGB(205, 197, 191)
        .Columns("A:BC").NumberFormat = "@"
        .Paste
        .Columns("A:BC").AutoFit
        .Columns("B:BC").HorizontalAlignment = xlCenter
        .Columns("F:BC").WrapText = True
        .Columns("A:BC").Borders.LineStyle = xlContinuous
        .Range("A1:M1").Merge
        .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
        .Range("C3").Select
        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?

Thanks
test1_code.zip
0
Comment
Question by:Wilder1626
  • 5
  • 3
8 Comments
 
LVL 45

Expert Comment

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

Author Comment

by:Wilder1626
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.
0
 
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.
0
 
LVL 11

Author Comment

by:Wilder1626
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
Ex:
In od TextBox, i have the value:

<RESPONSE 
Status="Succ
ess">
<ApiHeader>
<OperationNa
me 
Value="proce
ssShipmentOr
derMaintain"/

<ClientID 
Value="0000
00051022949
2"/>
</ApiHeader>
<ProcessShip
mentOrderCre
ate>
<ApiHeader>
<OperationNa
me 
Value="proce
ssShipmentOr
derCreate"/>
</ApiHeader>
<Shipment>
<ShipmentNu
mber Value="

Open in new window

0
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

0
 
LVL 45

Accepted Solution

by:
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

0
 
LVL 11

Author Closing Comment

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

Expert Comment

by:Martin Liss
Comment Utility
You're welcome.
0

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