jmthompson
Well-known Member
- Joined
- Mar 31, 2008
- Messages
- 966
Good morning, I have a large macro (actually 4 macros) which opens a number of text and csv files, grabs information from the files and puts into a master file then renames the csv files and file into a new folder.
The macro works great, with one exception. As part of the new file name, I want to grab a value (job number) designated as MyNum from the csv file (cell B1) and add it to the save as name. This works for the two text files I work with in step one and two, but not the batch of csv files I work with in Step 3 (Function OpenCSV). For these files, it just skips the MyNum portion of the code. I wonder if Excel thinks the job number is really a date (ex. 38958) and the number isn't taking because it would be trying to add a date (ex. 8/29/2006) to the file name, which wouldn't work because of the "/"...
Any ideas how to make this work?
Complete code-
Step 3 and Function
The macro works great, with one exception. As part of the new file name, I want to grab a value (job number) designated as MyNum from the csv file (cell B1) and add it to the save as name. This works for the two text files I work with in step one and two, but not the batch of csv files I work with in Step 3 (Function OpenCSV). For these files, it just skips the MyNum portion of the code. I wonder if Excel thinks the job number is really a date (ex. 38958) and the number isn't taking because it would be trying to add a date (ex. 8/29/2006) to the file name, which wouldn't work because of the "/"...
Any ideas how to make this work?
Complete code-
Code:
Sub Anne_Daily_Step1_ImportNightlyFiles()
Application.ScreenUpdating = False
'sets date standard
MyDate = Format(Now, "mm.dd.yy")
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
'sets CSV Path
MyCSVPath = "P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
'sets master WFO file path and name
MyMasterPath = "P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\"
MyMaster = "WFO Daily Postage Log" & MyYear & ".update.xls"
Dim Valleyfile As Workbook
Dim CurBook As Worksheet
Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(MyMonth & " " & MyYear)
On Error GoTo NextStep
With Application.FileSearch
.NewSearch
.LookIn = MyCSVPath
.FileType = msoFileTypeAllFiles
.Filename = "*baycity_rpt.txt"
.Execute
Set txtfile = Workbooks.Open(.FoundFiles(1))
End With
lastRow = Cells(Rows.Count, "A").End(xlUp).Row - 3
Range("A:A").Select
Selection.Replace What:="Total ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Range("E" & lastRow).Value = "Finish"
With Application.FileSearch
.NewSearch
.LookIn = MyCSVPath
.FileType = msoFileTypeAllFiles
.Filename = "saginaw_baycity.csv"
.Execute
Set Valleyfile = Workbooks.Open(.FoundFiles(1))
End With
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row + 1
txtfile.ActiveSheet.Name = "Sheet1"
Valleyfile.ActiveSheet.Name = "Sheet1"
'------------------------------------------
'COPY DATA FROM SRC TO MASTER
'------------------------------------------
With CurBook.Rows(lastRowWFO)
.Cells(, 1).Value = txtfile.Worksheets("Sheet1").Cells(2, 1)
.Cells(, 2).Value = "14900 - The Bay City Times"
.Cells(, 3).Value = Valleyfile.Worksheets("Sheet1").Cells(2, 1)
.Cells(, 4).Value = txtfile.Worksheets("Sheet1").Cells(lastRow, 1)
.Cells(, 5).Value = txtfile.Worksheets("Sheet1").Cells(lastRow, 5)
.Cells(, 7).Formula = "=RC[-2]+RC[-1]"
End With
'------------------------------------------
'DISABLE ALERTS
'------------------------------------------
Application.DisplayAlerts = False
'------------------------------------------
'CLOSE SOURCE FILE & SAVE
'------------------------------------------
MyNum = Valleyfile.Worksheets("Sheet1").Cells(2, 1).Value
txtfile.SaveAs Filename:="P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\Bay City\baycity " & MyNum & " " & MyDate & ".xls"
Kill MyCSVPath & "*baycity_rpt.txt"
txtfile.Close
Valleyfile.Close
Application.DisplayAlerts = True
NextStep:
Call Anne_IgnoreMe1
Application.ScreenUpdating = True
End Sub
Sub Anne_IgnoreMe1()
Application.ScreenUpdating = False
'sets date standard
MyDate = Format(Now, "mm.dd.yy")
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
'sets CSV Path
MyCSVPath = "P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
'sets master WFO file path and name
MyMasterPath = "P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\"
MyMaster = "WFO Daily Postage Log" & MyYear & ".update.xls"
Dim Valleyfile As Workbook
Dim CurBook As Worksheet
Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(MyMonth & " " & MyYear)
On Error GoTo NextStep2
With Application.FileSearch
.NewSearch
.LookIn = MyCSVPath
.FileType = msoFileTypeAllFiles
.Filename = "*saginaw_rpt.txt"
.Execute
Set txtfile = Workbooks.Open(.FoundFiles(1))
End With
lastRow = Cells(Rows.Count, "A").End(xlUp).Row - 3
Range("A:A").Select
Selection.Replace What:="Total ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Range("E" & lastRow).Value = "Finish"
With Application.FileSearch
.NewSearch
.LookIn = MyCSVPath
.FileType = msoFileTypeAllFiles
.Filename = "saginaw_baycity.csv"
.Execute
Set Valleyfile = Workbooks.Open(.FoundFiles(1))
End With
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row + 1
txtfile.ActiveSheet.Name = "Sheet1"
Valleyfile.ActiveSheet.Name = "Sheet1"
'------------------------------------------
'COPY DATA FROM SRC TO MASTER
'------------------------------------------
With CurBook.Rows(lastRowWFO)
.Cells(, 1).Value = txtfile.Worksheets("Sheet1").Cells(2, 1)
.Cells(, 2).Value = "14700 - The Saginaw News"
.Cells(, 3).Value = Valleyfile.Worksheets("Sheet1").Cells(2, 1)
.Cells(, 4).Value = txtfile.Worksheets("Sheet1").Cells(lastRow, 1)
.Cells(, 5).Value = txtfile.Worksheets("Sheet1").Cells(lastRow, 5)
.Cells(, 7).Formula = "=RC[-2]+RC[-1]"
End With
'------------------------------------------
'DISABLE ALERTS
'------------------------------------------
Application.DisplayAlerts = False
'------------------------------------------
'CLOSE SOURCE FILE & SAVE
'------------------------------------------
MyNum = Valleyfile.Worksheets("Sheet1").Cells(2, 1).Value
txtfile.SaveAs Filename:="P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\Completed Postage Reports\Saginaw\saginaw " & MyNum & " " & MyDate & ".xls"
Kill MyCSVPath & "*saginaw_rpt.txt"
txtfile.Close
Valleyfile.Close
Application.DisplayAlerts = True
NextStep2:
Call Anne_IgnoreMe2
Application.ScreenUpdating = True
End Sub
Sub Anne_IgnoreMe3()
'------------------------------------------
'DEFINE VARIABLES
'------------------------------------------
Dim MyDate As String, MyMonth As String, MyYear As String, MyCSVPath As String
Dim MyMasterPath As String, MyMaster As String
Dim CurBook As Worksheet
Dim CSVListItem As Range
'------------------------------------------
'DISABLE SCREEN REFRESH
'------------------------------------------
Application.ScreenUpdating = False
'------------------------------------------
'ASSIGN KEY VARIABLES
'------------------------------------------
MyDate = Format(Now, "mm.dd.yy")
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyCSVPath = "P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
MyMasterPath = "P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\"
MyMaster = "WFO Daily Postage Log" & MyYear & ".update.xls"
Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(MyMonth & " " & MyYear)
'------------------------------------------
'LOOP FILE LIST AND INVOKE OPENCSV(...)
'------------------------------------------
For Each CSVListItem In ThisWorkbook.Sheets("CSVList").Range("A1:A22")
If Not OpenCsv(MyCSVPath, CStr(CSVListItem), CurBook, CSVListItem.Offset(, 1), _
MyCSVPath & "Completed Postage Reports\" & CSVListItem.Offset(, 3) & CSVListItem.Offset(, 2) & " " & MyNum & " " & MyDate & ".csv") Then
' MsgBox "Failed..."
End If
Next
'------------------------------------------
'END
'------------------------------------------
Call Anne_IgnoreMe4
End Sub
Function OpenCsv(MyCSVPath As String, CSVFileName As String, CurBook As Worksheet, _
BValue As String, SaveAsPath As String) As Boolean
'------------------------------------------
'DEFINE VARIABLES
'------------------------------------------
Dim SrceBook As Workbook
Dim lastRow As Long, lastRowWFO As Long
'------------------------------------------
'SET SPECIFIC HANDLER
'------------------------------------------
On Error GoTo Err_OpenCsv
'------------------------------------------
'OPEN PARSED FILE
'------------------------------------------
With Application.FileSearch
.NewSearch
.LookIn = MyCSVPath
.FileType = msoFileTypeAllFiles
.Filename = CSVFileName
.Execute
Set SrceBook = Workbooks.Open(.FoundFiles(1))
End With
'------------------------------------------
'RESET HANDLER
'------------------------------------------
On Error GoTo 0
'------------------------------------------
'SET PASTE ROW + LAST ROW IN SOURCE FILE
'------------------------------------------
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row + 1
SrceBook.ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
'------------------------------------------
'COPY DATA FROM SRC TO MASTER
'------------------------------------------
With CurBook.Rows(lastRowWFO)
.Cells(, 1).Value = SrceBook.Worksheets("Sheet1").Cells(3, 1)
.Cells(, 2).Value = BValue
.Cells(, 3).Value = SrceBook.Worksheets("Sheet1").Cells(2, 1)
.Cells(, 4).Value = SrceBook.Worksheets("Sheet1").Cells(lastRow, 2)
.Cells(, 5).Value = SrceBook.Worksheets("Sheet1").Cells(lastRow, 5)
.Cells(, 7).Formula = "=RC[-2]+RC[-1]"
End With
'------------------------------------------
'DISABLE ALERTS
'------------------------------------------
Application.DisplayAlerts = False
'------------------------------------------
'CLOSE SOURCE FILE & SAVE
'------------------------------------------
MyNum = SrceBook.Worksheets("Sheet1").Cells(2, 1).Value
SrceBook.SaveAs SaveAsPath
Kill MyCSVPath & CSVFileName
SrceBook.Close
'------------------------------------------
'ENABLE ALERTS
'------------------------------------------
Application.DisplayAlerts = True
'------------------------------------------
'RETURN BOOLEAN SUCCESS
'------------------------------------------
OpenCsv = True
'------------------------------------------
'END
'------------------------------------------
Exit Function
'------------------------------------------
'ERROR HANDLER FOR NON-EXISTENT FILE (to end)
'------------------------------------------
Err_OpenCsv:
End Function
Step 3 and Function
Code:
Sub Anne_IgnoreMe3()
'------------------------------------------
'DEFINE VARIABLES
'------------------------------------------
Dim MyDate As String, MyMonth As String, MyYear As String, MyCSVPath As String
Dim MyMasterPath As String, MyMaster As String
Dim CurBook As Worksheet
Dim CSVListItem As Range
'------------------------------------------
'DISABLE SCREEN REFRESH
'------------------------------------------
Application.ScreenUpdating = False
'------------------------------------------
'ASSIGN KEY VARIABLES
'------------------------------------------
MyDate = Format(Now, "mm.dd.yy")
MyMonth = Format(Now, "mmmm")
MyYear = Format(Now, "yyyy")
MyCSVPath = "P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
MyMasterPath = "P:\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\"
MyMaster = "WFO Daily Postage Log" & MyYear & ".update.xls"
Set CurBook = Workbooks("WFO Daily Postage Log" & MyYear & ".update.xls").Sheets(MyMonth & " " & MyYear)
'------------------------------------------
'LOOP FILE LIST AND INVOKE OPENCSV(...)
'------------------------------------------
For Each CSVListItem In ThisWorkbook.Sheets("CSVList").Range("A1:A22")
If Not OpenCsv(MyCSVPath, CStr(CSVListItem), CurBook, CSVListItem.Offset(, 1), _
MyCSVPath & "Completed Postage Reports\" & CSVListItem.Offset(, 3) & CSVListItem.Offset(, 2) & " " & MyNum & " " & MyDate & ".csv") Then
' MsgBox "Failed..."
End If
Next
'------------------------------------------
'END
'------------------------------------------
Call Anne_IgnoreMe4
End Sub
Function OpenCsv(MyCSVPath As String, CSVFileName As String, CurBook As Worksheet, _
BValue As String, SaveAsPath As String) As Boolean
'------------------------------------------
'DEFINE VARIABLES
'------------------------------------------
Dim SrceBook As Workbook
Dim lastRow As Long, lastRowWFO As Long
'------------------------------------------
'SET SPECIFIC HANDLER
'------------------------------------------
On Error GoTo Err_OpenCsv
'------------------------------------------
'OPEN PARSED FILE
'------------------------------------------
With Application.FileSearch
.NewSearch
.LookIn = MyCSVPath
.FileType = msoFileTypeAllFiles
.Filename = CSVFileName
.Execute
Set SrceBook = Workbooks.Open(.FoundFiles(1))
End With
'------------------------------------------
'RESET HANDLER
'------------------------------------------
On Error GoTo 0
'------------------------------------------
'SET PASTE ROW + LAST ROW IN SOURCE FILE
'------------------------------------------
lastRowWFO = CurBook.Cells(Rows.Count, "A").End(xlUp).Row + 1
SrceBook.ActiveSheet.Name = "Sheet1"
lastRow = SrceBook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
'------------------------------------------
'COPY DATA FROM SRC TO MASTER
'------------------------------------------
With CurBook.Rows(lastRowWFO)
.Cells(, 1).Value = SrceBook.Worksheets("Sheet1").Cells(3, 1)
.Cells(, 2).Value = BValue
.Cells(, 3).Value = SrceBook.Worksheets("Sheet1").Cells(2, 1)
.Cells(, 4).Value = SrceBook.Worksheets("Sheet1").Cells(lastRow, 2)
.Cells(, 5).Value = SrceBook.Worksheets("Sheet1").Cells(lastRow, 5)
.Cells(, 7).Formula = "=RC[-2]+RC[-1]"
End With
'------------------------------------------
'DISABLE ALERTS
'------------------------------------------
Application.DisplayAlerts = False
'------------------------------------------
'CLOSE SOURCE FILE & SAVE
'------------------------------------------
MyNum = SrceBook.Worksheets("Sheet1").Cells(2, 1).Value
SrceBook.SaveAs SaveAsPath
Kill MyCSVPath & CSVFileName
SrceBook.Close
'------------------------------------------
'ENABLE ALERTS
'------------------------------------------
Application.DisplayAlerts = True
'------------------------------------------
'RETURN BOOLEAN SUCCESS
'------------------------------------------
OpenCsv = True
'------------------------------------------
'END
'------------------------------------------
Exit Function
'------------------------------------------
'ERROR HANDLER FOR NON-EXISTENT FILE (to end)
'------------------------------------------
Err_OpenCsv:
End Function