Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move, Rename, Format & Delete Row *.csv file
Hi all,
Pardon my lack of knowledge in Excel VBA. I have a scheduled task to download a daily data file in a csv format. The file name is unique and is in the format of yyyyMMddHHmmss.csv The following needs to be done in order to import the file into an accounting application. 1) Format amount (col C) to remove the double quotes ("1,000.00" needs to be 1000.00). Only col C needs to be reformatted. 2) Delete rows that have a zero value in col D 3) Rename file from unique name to generic name (yyyyMMddHHmmss.csv to WC.csv) in same directory 4) Move file from source to target folder (in same directory) to be imported into accounting application The following code works to move the file, but does not change the file name. Would like to be able to rename file from unique to generic file name: Sub Movefile() Dim SourceFolder As String, TargetFolder As String Dim fs, f, f1, fc, sf, tf SourceFolder = "T:\Accounting\BANKING\WC\Schedule\" TargetFolder = "T:\Accounting\BANKING\Formatted Data Files\" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(SourceFolder) Set fc = f.Files For Each f1 In fc If Right(f1.Name, 3) = "csv" Then sf = SourceFolder & f1.Name tf = TargetFolder & f1.Name Name sf As tf End If Next MsgBox "Done" End Sub The following code deletes all rows that have a zero value in column D. Sub Delete_blank_rows() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "D").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "D").Value = "0" Then .Rows(Lrow).Delete 'This will delete each row with the Value "0" in Column D, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I also recorded a macro to format the file. Can all 4 steps be combined into one code so that I can run it on a scheduled task? Thanks in advance for any assistance. Cheryl |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move, Rename, Format & Delete Row *.csv file
Haven't looked at your code to see if it can be improved, just added what
you said Sub Movefile() Dim SourceFolder As String, TargetFolder As String Dim fs, f, f1, fc, sf, tf SourceFolder = "T:\Accounting\BANKING\WC\Schedule\" TargetFolder = "T:\Accounting\BANKING\Formatted Data Files\" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(SourceFolder) Set fc = f.Files For Each f1 In fc If Right(f1.Name, 3) = "csv" Then sf = SourceFolder & f1.Name tf = TargetFolder & "\wc.csv" Name sf As tf End If Next Workbooks.Open TargetFolder & "\wc.csv" Call Delete_blank_rows Columns("C:C").Replace What:="""", _ Replacement:="", _ LookAt:=xlPart MsgBox "Done" End Sub Sub Delete_blank_rows() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "D").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "D").Value = "0" Then .Rows(Lrow).Delete 'This will delete each row with the Value "0" in Column D,case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Cheryl" wrote in message ... Hi all, Pardon my lack of knowledge in Excel VBA. I have a scheduled task to download a daily data file in a csv format. The file name is unique and is in the format of yyyyMMddHHmmss.csv The following needs to be done in order to import the file into an accounting application. 1) Format amount (col C) to remove the double quotes ("1,000.00" needs to be 1000.00). Only col C needs to be reformatted. 2) Delete rows that have a zero value in col D 3) Rename file from unique name to generic name (yyyyMMddHHmmss.csv to WC.csv) in same directory 4) Move file from source to target folder (in same directory) to be imported into accounting application The following code works to move the file, but does not change the file name. Would like to be able to rename file from unique to generic file name: Sub Movefile() Dim SourceFolder As String, TargetFolder As String Dim fs, f, f1, fc, sf, tf SourceFolder = "T:\Accounting\BANKING\WC\Schedule\" TargetFolder = "T:\Accounting\BANKING\Formatted Data Files\" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(SourceFolder) Set fc = f.Files For Each f1 In fc If Right(f1.Name, 3) = "csv" Then sf = SourceFolder & f1.Name tf = TargetFolder & f1.Name Name sf As tf End If Next MsgBox "Done" End Sub The following code deletes all rows that have a zero value in column D. Sub Delete_blank_rows() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "D").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "D").Value = "0" Then .Rows(Lrow).Delete 'This will delete each row with the Value "0" in Column D, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I also recorded a macro to format the file. Can all 4 steps be combined into one code so that I can run it on a scheduled task? Thanks in advance for any assistance. Cheryl |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move, Rename, Format & Delete Row *.csv file
Hi Bob,
Thanks for the tip. Everything worked except for the " " formatting. Col C still has double quotes around the amount. And now col A that was ok has a formatting issue. Col A is a 13 digit account no. It now reads 2.0E+12. Any reason why this would have occurred? I opened up the new WC.csv file in notepad both before and after the file got moved, renamed & formatted. Thanks again, Cheryl "Bob Phillips" wrote: Haven't looked at your code to see if it can be improved, just added what you said Sub Movefile() Dim SourceFolder As String, TargetFolder As String Dim fs, f, f1, fc, sf, tf SourceFolder = "T:\Accounting\BANKING\WC\Schedule\" TargetFolder = "T:\Accounting\BANKING\Formatted Data Files\" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(SourceFolder) Set fc = f.Files For Each f1 In fc If Right(f1.Name, 3) = "csv" Then sf = SourceFolder & f1.Name tf = TargetFolder & "\wc.csv" Name sf As tf End If Next Workbooks.Open TargetFolder & "\wc.csv" Call Delete_blank_rows Columns("C:C").Replace What:="""", _ Replacement:="", _ LookAt:=xlPart MsgBox "Done" End Sub Sub Delete_blank_rows() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "D").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "D").Value = "0" Then .Rows(Lrow).Delete 'This will delete each row with the Value "0" in Column D,case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Cheryl" wrote in message ... Hi all, Pardon my lack of knowledge in Excel VBA. I have a scheduled task to download a daily data file in a csv format. The file name is unique and is in the format of yyyyMMddHHmmss.csv The following needs to be done in order to import the file into an accounting application. 1) Format amount (col C) to remove the double quotes ("1,000.00" needs to be 1000.00). Only col C needs to be reformatted. 2) Delete rows that have a zero value in col D 3) Rename file from unique name to generic name (yyyyMMddHHmmss.csv to WC.csv) in same directory 4) Move file from source to target folder (in same directory) to be imported into accounting application The following code works to move the file, but does not change the file name. Would like to be able to rename file from unique to generic file name: Sub Movefile() Dim SourceFolder As String, TargetFolder As String Dim fs, f, f1, fc, sf, tf SourceFolder = "T:\Accounting\BANKING\WC\Schedule\" TargetFolder = "T:\Accounting\BANKING\Formatted Data Files\" Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(SourceFolder) Set fc = f.Files For Each f1 In fc If Right(f1.Name, 3) = "csv" Then sf = SourceFolder & f1.Name tf = TargetFolder & f1.Name Name sf As tf End If Next MsgBox "Done" End Sub The following code deletes all rows that have a zero value in column D. Sub Delete_blank_rows() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If IsError(.Cells(Lrow, "D").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "D").Value = "0" Then .Rows(Lrow).Delete 'This will delete each row with the Value "0" in Column D, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I also recorded a macro to format the file. Can all 4 steps be combined into one code so that I can run it on a scheduled task? Thanks in advance for any assistance. Cheryl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot Move, Copy, Insert, Rename, or Delete a Worksheet in Any Wo | Excel Discussion (Misc queries) | |||
Move and rename a file | Excel Programming | |||
Move and rename a file | Excel Programming | |||
Rename or move file function | Excel Programming | |||
rename current file save and delete original | Excel Programming |