PDA

View Full Version : [SOLVED] Opening and editing mutiple csv files



Chunk
10-16-2018, 10:23 AM
Looking for help.
I am fairly new to VBA and am trying to accomplish the following:

I have multiple csv files (up to 80) in a central location, that I need to open and possibly edit (by deleting column "A" if it has "FileName" in "A1").

I then need to rename the file to be able to use it in another application.
An example of the file name that I receive is:

PPS_PROJECT1_F18_January_XXX_10JAN2018.csv

I need it to be renamed (using the Main workbook for the conversion):

POA01_Q1_10JAN18.csv

I am using Main workbook to initiate the conversion and hold the lookup list for the projects.

Attached are 4 example files (3 that need the first column deleted and one that does not) That I receive and the Main workbook.

Any help/tie yo can give is greatly appreciated. Please let me know if you require more information.

Sincerely,

Chunk

Chunk
10-17-2018, 05:06 AM
PPS_PROJECT1_F18_January_XXX_10JAN2018.csv

should read

PPS_PROJECT1_FY18_Q1_January_XXX_10JAN2018.csv

Chunk
05-29-2019, 09:00 AM
Here is what I ended up using.

Hope it helps.

Chunk


Sub UpdateFiles()

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim rng As Range
Dim wbrng As Range
Dim proj As String


Dim nwproj As String
Dim nwtype As String
Dim nwdate1 As String
Dim nwdate2 As String
Dim nwdate3 As String
Dim nwFile As String




Set rng = Worksheets("LookUpList").Range("D2:D40")




'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


For Each cell In rng

If cell.Value = "Y" Then
'Target File Extension (must include wildcard "*")
myExtension = "*.csv"

proj = cell.Offset(0, -2)


myPath = "C:\ChunksStuff\datfiles\"
'Target Path with Ending Extention
myFile = Dir(myPath & "*" & proj & myExtension)


'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set wb = Workbooks.Open(myPath & myFile)

'Ensure Workbook has opened before moving on to next line of code
DoEvents

'Delete first column
With wb
If Range("A1") = "FileName" Then
Range("A:A").Select
Selection.Delete
Else

End If

End With


'Configure variables to rename file
nwproj = cell.Offset(0, 1)


If InStr(myFile, "_TL_") Then
nwtype = "_TL_"
ElseIf InStr(myFile, "_NN_") Then
nwtype = "_NN_"
ElseIf InStr(myFile, "_NU_") Then
nwtype = "_NU_"
End If



nwdate1 = Right(myFile, 13)
nwdate2 = Left(nwdate1, 5)
nwdate3 = Mid(nwdate1, 8, 9)

If InStr(myFile, "PPS") Then
nwFile = ("PO" & nwproj & nwtype & nwdate2 & nwdate3)
ElseIf InStr(myFile, "PWL") Then
nwFile = ("WO" & nwproj & nwtype & nwdate2 & nwdate3)
End If


'Save and Close Workbook
wb.SaveAs FileName:=myPath & "Converted\" & nwFile
wb.Close SaveChanges:=True

'Ensure Workbook has closed before moving on to next line of code
DoEvents


'Get next file name
myFile = Dir
Loop

End If


Next cell


'Message Box when tasks are completed
MsgBox "Task Complete!"


ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True




End Sub