Move, Rename, Format & Delete Row *.csv file

[Cheryl]

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

 

[Bob Phillips]

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]

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