Code check

M

Miya

Well-known Member
Joined
Nov 29, 2008
Messages
662
hi what is wrong with this code? i have set ws as Sheet2
With ws
.Copy.SaveAs Filename:= _
"U:\TL\PV\CSV\test.csv", FileFormat:=xlCSV, _
CreateBackup:=False
.Close = False
End With
 
Sort by date Sort by votes
Seiya said:
I don't know...

Do you have any data with double quotes with comma ?
Click to expand...

Ok this the line the data does not work

<TABLE style="WIDTH: 735pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=979 border=0 x:str><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 215pt; mso-width-source: userset; mso-width-alt: 10496" width=287><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" span=2 width=59><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 80pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=107 height=17>GB00B1YW4409</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 215pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=287>3I Group, ORD GBP0.738636</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=85 x:num="-26000">-26000.00 </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=50 x:num>200</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=85 x:num="-5200000">-5200000.00 </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=78 x:num="-115609.99">-115609.99 </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=85 x:num="10401359.949999999">10401359.95 </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=59> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=59>GBp</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=84 x:num="2.22326894506862E-2">0.022232689</TD></TR></TBODY></TABLE>
This is what happens to the data after running macro

<TABLE style="WIDTH: 1089pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1451 border=0 x:str><COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 215pt; mso-width-source: userset; mso-width-alt: 10496" width=287><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 140pt; mso-width-source: userset; mso-width-alt: 6838" width=187><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 113pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=150 height=17>GB00B1YW4409</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 215pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=287>3I Group</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=126> ORD GBP0.738636</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=117 x:num>-26000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 140pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=187 x:num>200</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 94pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=125 x:num="-5200000">-5200000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 66pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=88 x:num>-115609.99</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 101pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=135 x:num="10401359.949999999">10401359.95</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 44pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=59> </TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 70pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=93>GBp</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=84 x:num="2.2232689E-2">0.022232689</TD></TR></TBODY></TABLE>
 
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
As I said earlier, Cells.Text property get what it looks like on the sheet, so if the cell width is not wide enough to show all the data, it will be cut off.

I'm not sure if that is a case though,
try
Rich (BB code): 
Sub test()
Dim temp As String, txt As String, i As Long, ii As Long
With Sheets("sheet2").UsedRange
    .EntireColumn.AutoFit
    For i = 1 To .Rows.Count
        For ii = 1 To .Columns.Count
            If Cells(i, ii).Text Like "*,*" Then flg = True
            temp = temp & "," & IIf(flg,Chr(34), "") & _
                .Cells(i, ii).Text & IIf(flg,Chr(34), "")
            flg = False
        Next
        txt = txt & vbCrLf & Mid$(temp, 2) : temp = ""
    Next
End With
CreateObject("WScript.Shell").<WBR>CurrentDirectory = "\\server\TL\PV\CSV"
Open "test.csv" For Output As #1
    Print #1, Mid$(txt, 3)
Close #1
End Sub
 
Upvote 0
Nope still that didnt work, the columwidth is wide enough on my source sheet but when i open CSV i have to widen the columns,
Seiya said:
As I said earlier, Cells.Text property get what it looks like on the sheet, so if the cell width is not wide enough to show all the data, it will be cut off.

I'm not sure if that is a case though,
try
Rich (BB code): 
Sub test()
Dim temp As String, txt As String, i As Long, ii As Long
With Sheets("sheet2").UsedRange
    .EntireColumn.AutoFit
    For i = 1 To .Rows.Count
        For ii = 1 To .Columns.Count
            If Cells(i, ii).Text Like "*,*" Then flg = True
            temp = temp & "," & IIf(flg,Chr(34), "") & _
                .Cells(i, ii).Text & IIf(flg,Chr(34), "")
            flg = False
        Next
        txt = txt & vbCrLf & Mid$(temp, 2) : temp = ""
    Next
End With
CreateObject("WScript.Shell").<WBR>CurrentDirectory = "\\server\TL\PV\CSV"
Open "test.csv" For Output As #1
    Print #1, Mid$(txt, 3)
Close #1
End Sub
Click to expand...
 
Upvote 0
Hummm
Does this make any difference ?
Rich (BB code): 
Sub test()
Dim temp As String, txt As String, i As Long, ii As Long
With Sheets("sheet2").UsedRange
    For i = 1 To .Rows.Count
        For ii = 1 To .Columns.Count
            flg = InStr(.Cells(i, ii).Text, ",") > 0
            temp = temp & "," & IIf(flg,Chr(34), "") & _
                .Cells(i, ii).Text & IIf(flg,Chr(34), "")
        Next
        txt = txt & vbCrLf & Mid$(temp, 2) : temp = ""
    Next
End With
CreateObject("WScript.Shell").<WBR>CurrentDirectory = "\\server\TL\PV\CSV"
Open "test.csv" For Output As #1
    Print #1, Mid$(txt, 3)
Close #1
End Sub
 
Upvote 0
Thats the one seiya that works, could you please explain what you did to make it work.
Seiya said:
Hummm
Does this make any difference ?
Rich (BB code): 
Sub test()
Dim temp As String, txt As String, i As Long, ii As Long
With Sheets("sheet2").UsedRange
    For i = 1 To .Rows.Count
        For ii = 1 To .Columns.Count
            flg = InStr(.Cells(i, ii).Text, ",") > 0
            temp = temp & "," & IIf(flg,Chr(34), "") & _
                .Cells(i, ii).Text & IIf(flg,Chr(34), "")
        Next
        txt = txt & vbCrLf & Mid$(temp, 2) : temp = ""
    Next
End With
CreateObject("WScript.Shell").<WBR>CurrentDirectory = "\\server\TL\PV\CSV"
Open "test.csv" For Output As #1
    Print #1, Mid$(txt, 3)
Close #1
End Sub
Click to expand...
 
Upvote 0
Rich (BB code): 
 If Cells(i, ii).Text Like "*,*" Then flg = True
was not picking up the data with the comma at the end.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Trying to save to csv or txt without applying "" to strings containing comma values
Replies
2
Views
189
Roxenham
R
S
  • Question
CSV export to remove column
Replies
1
Views
123
StellarRumble59
S
C
  • Question
Setting the default save path with prompt
Replies
2
Views
355
CaptainGravyBum
C
J
  • Question
Line or lines needed to ignore if sheet not available in a VBA code
Replies
5
Views
311
jack109
J
H
  • Solved
Macro to convert Files into CSV Files-limit selection when browsing
Replies
1
Views
253
howard
H

Forum statistics

Threads
1,215,241
Messages
6,123,823
Members
449,127
Latest member
Cyko

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top