vjay883
09-10-2014, 10:26 AM
Hi,
This is doing my nut in and can't find an answer, can anyone help?:(:crying:
I need to do the following:
1.Open a CSV file in notepad
2.Delete all blank rows at the bottom of the file (There are usually 2 lines - could be more)
3.Save the file
Some reason when I replace the commas with pipes and save the file it always has these blank rows at the bottom. Here is my code I am using:
Dim F As Integer
Dim StrFile As Variant
Dim StrLines As Variant
Dim pos As Integer
'Open file to read
F = FreeFile()
Open "G:\Asset Management\EAMS Prog\03 Tranche Mgt\EP06 Data Mgt\Specialist\AUTOMATION\Load_Files\" + myfilename + ".csv" For Input As F
StrFile = Input(LOF(F), #F)
Close F
'Edit file content
StrFile = Replace(StrFile, ",", "|")
StrLines = Split(StrFile, vbNewLine)
pos = InStr(StrLines(0), "EN")
If pos > 0 Then
StrLines(0) = Left(StrLines(0), pos - 1) & Replace(StrLines(0), "|", "", pos)
StrLines(0) = Chr(239) & Chr(187) & Chr(191) & StrLines(0)
StrFile = Join(StrLines, vbNewLine)
End If
'Save file
F = FreeFile()
Open "G:\Asset Management\EAMS Prog\03 Tranche Mgt\EP06 Data Mgt\Specialist\AUTOMATION\Load_Files_Ready\" + myfilename + ".csv" For Output As F
Print #F, StrFile
Close F
Cheers
V
This is doing my nut in and can't find an answer, can anyone help?:(:crying:
I need to do the following:
1.Open a CSV file in notepad
2.Delete all blank rows at the bottom of the file (There are usually 2 lines - could be more)
3.Save the file
Some reason when I replace the commas with pipes and save the file it always has these blank rows at the bottom. Here is my code I am using:
Dim F As Integer
Dim StrFile As Variant
Dim StrLines As Variant
Dim pos As Integer
'Open file to read
F = FreeFile()
Open "G:\Asset Management\EAMS Prog\03 Tranche Mgt\EP06 Data Mgt\Specialist\AUTOMATION\Load_Files\" + myfilename + ".csv" For Input As F
StrFile = Input(LOF(F), #F)
Close F
'Edit file content
StrFile = Replace(StrFile, ",", "|")
StrLines = Split(StrFile, vbNewLine)
pos = InStr(StrLines(0), "EN")
If pos > 0 Then
StrLines(0) = Left(StrLines(0), pos - 1) & Replace(StrLines(0), "|", "", pos)
StrLines(0) = Chr(239) & Chr(187) & Chr(191) & StrLines(0)
StrFile = Join(StrLines, vbNewLine)
End If
'Save file
F = FreeFile()
Open "G:\Asset Management\EAMS Prog\03 Tranche Mgt\EP06 Data Mgt\Specialist\AUTOMATION\Load_Files_Ready\" + myfilename + ".csv" For Output As F
Print #F, StrFile
Close F
Cheers
V