• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

vb6 - Transfer from MSHFlexgrid1 to xls issue

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
Wilder1626
Asked:
Wilder1626
  • 5
  • 3
1 Solution
 
Martin LissOlder than dirtCommented:
When I run the program I see this. What should it look like?
result
0
 
Wilder1626Author Commented:
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
 
Martin LissOlder than dirtCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Wilder1626Author Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Martin LissOlder than dirtCommented:
You could also do it after filling the textboxes. For example

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

Open in new window

0
 
Wilder1626Author Commented:
This is exactly what i needed. Thank you again for your help
0
 
Martin LissOlder than dirtCommented:
You're welcome.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now