Solved

vb6 - Transfer from MSHFlexgrid1 to xls issue

Posted on 2016-11-28
8
45 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 46

Expert Comment

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

Author Comment

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

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

Author Comment

by:Wilder1626
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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 46

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

0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 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

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 41904535
This is exactly what i needed. Thank you again for your help
0
 
LVL 46

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

912 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

22 Experts available now in Live!

Get 1:1 Help Now