mustish1
asked on
One space gap before the last comma
Hi: Can any one please tell me in VBA code as this process works but there is a one space gap before comma in the last column. My question is how to i remove this one space gap before the last comma
SAMPLE DATA
AC0216425,3/10/2007,166.50 ,83.25
JM0338775,3/10/2007,100.00 ,50.00
JM0464931,3/9/2007,200.00 ,100.00
JM0337460,3/10/2007,93.75 ,46.87
JM0509209,3/10/2007,236.03 ,118.01
AZ0501422,3/10/2007,250.00 ,125.00
AC0522680,3/10/2007,201.80 ,100.90
JJ0402934,3/7/2007,200.00 ,100.00
JJ0501329,3/10/2007,356.50 ,178.25
JM0524012,3/10/2007,60.00 ,30.00
JM0431817,3/9/2007,100.00 ,50.00
JM0441390,3/10/2007,249.50 ,124.75
Y0406978,3/9/2007,105.00 ,52.50
JM0531129,3/8/2007,125.00 ,62.50
AC0338563,3/13/2007,50.00 ,25.00
RK0502896,3/9/2007,158.00 ,79.00
JJ0402244,3/9/2007,75.00 ,37.50
Private Sub excelfileprocess()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim strTempXL As String
Dim strTempTXT As String
Dim WShell As Object
Dim FileName As String
Dim FolderAddress As String
Dim strDestination As String
strDestination = OutputFileText.Text
FileName = getFileNam(OutputFileText. Text)
FolderAddress = getDir(OutputFileText.Text )
strTempTXT = FolderAddress & "TEMP_TXT" & Format$(Now, "yyyymmdd-hhmmss") & ".txt"
strTempXL = FolderAddress & "TEMP_XL" & Format$(Now, "yyyymmdd-hhmmss") & ".xls"
FileCopy InputFileText.Text, strTempXL
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(strTe mpXL)
Set xlWS = xlApp.Worksheets(1)
xlWS.Range("A:A,B:B,D:D,E: E,G:G,H:H, I:I,K:K,L: L,M:M,N:N, O:O,P:P,R: R,S:S,T:T, U:U,V:V,W: W").Delete Shift:=xlToLeft
xlWS.Columns("A:A").Insert Shift:=xlToRight
xlWS.Columns("E:E").Cut
xlWS.Range("A1").Select
ActiveSheet.Paste
xlWS.Columns("B:B").Insert Shift:=xlToRight
xlWS.Columns("E:E").Cut
xlWS.Range("B1").Select
ActiveSheet.Paste
xlWS.Columns("C:D").Number Format = "#,##0.00_);(#,##0.00)"
Debug.Print OutputFileText.Text
xlWB.SaveAs strTempTXT, xlCSV
xlWB.Close SaveChanges:=True
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
Dim fso As New FileSystemObject
Dim tsRead As TextStream
Dim tsWrite As TextStream
Dim inLine As String
Set tsRead = fso.OpenTextFile(strTempTX T, ForReading)
Set tsWrite = fso.OpenTextFile(strDestin ation, ForWriting)
If Not tsRead.AtEndOfStream Then
tsRead.SkipLine
While Not tsRead.AtEndOfStream
inLine = tsRead.ReadLine
tsWrite.WriteLine inLine
Wend
End If
tsRead.Close
tsWrite.Close
Kill strTempXL
Kill strTempTXT
MsgBox "Done"
SAMPLE DATA
AC0216425,3/10/2007,166.50
JM0338775,3/10/2007,100.00
JM0464931,3/9/2007,200.00 ,100.00
JM0337460,3/10/2007,93.75 ,46.87
JM0509209,3/10/2007,236.03
AZ0501422,3/10/2007,250.00
AC0522680,3/10/2007,201.80
JJ0402934,3/7/2007,200.00 ,100.00
JJ0501329,3/10/2007,356.50
JM0524012,3/10/2007,60.00 ,30.00
JM0431817,3/9/2007,100.00 ,50.00
JM0441390,3/10/2007,249.50
Y0406978,3/9/2007,105.00 ,52.50
JM0531129,3/8/2007,125.00 ,62.50
AC0338563,3/13/2007,50.00 ,25.00
RK0502896,3/9/2007,158.00 ,79.00
JJ0402244,3/9/2007,75.00 ,37.50
Private Sub excelfileprocess()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim strTempXL As String
Dim strTempTXT As String
Dim WShell As Object
Dim FileName As String
Dim FolderAddress As String
Dim strDestination As String
strDestination = OutputFileText.Text
FileName = getFileNam(OutputFileText.
FolderAddress = getDir(OutputFileText.Text
strTempTXT = FolderAddress & "TEMP_TXT" & Format$(Now, "yyyymmdd-hhmmss") & ".txt"
strTempXL = FolderAddress & "TEMP_XL" & Format$(Now, "yyyymmdd-hhmmss") & ".xls"
FileCopy InputFileText.Text, strTempXL
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(strTe
Set xlWS = xlApp.Worksheets(1)
xlWS.Range("A:A,B:B,D:D,E:
xlWS.Columns("A:A").Insert
xlWS.Columns("E:E").Cut
xlWS.Range("A1").Select
ActiveSheet.Paste
xlWS.Columns("B:B").Insert
xlWS.Columns("E:E").Cut
xlWS.Range("B1").Select
ActiveSheet.Paste
xlWS.Columns("C:D").Number
Debug.Print OutputFileText.Text
xlWB.SaveAs strTempTXT, xlCSV
xlWB.Close SaveChanges:=True
Set xlWS = Nothing
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
Dim fso As New FileSystemObject
Dim tsRead As TextStream
Dim tsWrite As TextStream
Dim inLine As String
Set tsRead = fso.OpenTextFile(strTempTX
Set tsWrite = fso.OpenTextFile(strDestin
If Not tsRead.AtEndOfStream Then
tsRead.SkipLine
While Not tsRead.AtEndOfStream
inLine = tsRead.ReadLine
tsWrite.WriteLine inLine
Wend
End If
tsRead.Close
tsWrite.Close
Kill strTempXL
Kill strTempTXT
MsgBox "Done"
replace("AC0216425,3/10/20 07,166.50 ,83.25", space(1),"")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Couple of ways...
Trim(YourString) removes leading and trailing spaces
Val(YourString) returns the numerical value starting with the left-most character
Then, you can use CCur( the above), CLng( the above), etc. to convert it to a currency, long integer, etc.