Wilder1626
asked on
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,2 3), you have a long value. This is a concatenation of 3 textbox:
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:
How can i do that?
Thanks
test1_code.zip
In attachment, you will have a sample of the project.
When you run the project, you will see that in MSHFlexgrid.TextMatrix(1,2
MSHFlexGrid1.TextMatrix(1, 23) = od.Text & plan_id.Text & dc.Text
.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
How can i do that?
Thanks
test1_code.zip
When I run the program I see this. What should it look like?
ASKER
Look in column 23 and you should see (MSHFlexGrid1.TextMatrix(1 , 23) = od.Text & plan_id.Text & dc.Text) value into 1 single cell.
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.
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.
ASKER
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:
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="
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, "")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is exactly what i needed. Thank you again for your help
You're welcome.