Why won't my Save As name use one of my variables?

[jmthompson]

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-

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

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

 

[jmthompson]

I've done some more testing and I don't believe it is the format of MyNum that is the problem.

MyNum is being "loaded" properly in the function, but it is not translating in the SaveAsPath in the macro

In my test, MyNum is 37298 (displayed when hovering over MyNum) in the function OpenCsv:

MyNum = SrceBook.Worksheets("Sheet1").Cells(2, 1).Value
MyNum = Format(MyNum, "0")

But when I scroll up and hover over MyNum in the SaveAsPath, it is displayed as empty:

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

 

[Richard Schollar]

Hi

Where's MyNum declared and populated with a value? To debug properly, get rid of ALL of your On Error statements so they don't prevent you seeing what's really happening.

 

[jmthompson]

MyNum is being populated within the OpenCSV Function.

I've took out my disable alerts statement before the save as statement and no alerts were presented when I stepped through. Any On Errors are not related to the save as step.

This is what I think the problem is and I don't know how to fix it. The macro uses the custom OpenCSV function (not written by me, but by another, very helpful, board user, so I don't entirely understand it) to cycle through a list of possible file names (housed within a list in my master workbook), look for that file name in a particular folder. If found, take information from that file and put it in my master file, then save it under a new name which is comprised of a filename (housed in the same list in my master workbook), the job number (a variable that is found in cell B1 of the file being saved) and today's date. The date is being assigned in the macro that uses the function. The job number is being assigned in the function. I tried assigning the job number in the macro, but since that file hasn't been opened by the function yet, I get the out of range error. I just can't figure out how to assign this variable in such a way that it populates in the saveAspath (which is spelled out in the macro).

 

[Norie]

Even if you think the On Errors aren't related to the problem you might not be right.

No harm in taking them and seeing if they are hiding anything.

 

[jmthompson]

I have now removed all On error statements, no error messages were received.

 

[jmthompson]

I have been playing with this some more to no avail.

Bottom-line, my SaveAsPath is set before my variable of MyNum is defined. MyNum cannot be defined until the OpenCSVFunction is run. How could I rework this code so that the SaveAsPath is not set until after the OpenCSV Function is called?

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

 

[Richard Schollar]

In this bit of the code:

'CLOSE SOURCE FILE & SAVE
'------------------------------------------
MyNum = SrceBook.Worksheets("Sheet1").Cells(2, 1).Value
SrceBook.SaveAs SaveAsPath
Kill MyCSVPath & CSVFileName
SrceBook.Close

I can see that MyNum is being populated, but it isn't then being used in saving down the file using SaveAs. I am presuming it should be used here? So you need to incorporate it in:

SrceBook.SaveAs SaveAsPath 'eg as & MyNum

depending on what final name you want the file saved down as.

 

[jmthompson]

Richard- You've done it! Thank you for pushing me outside of the box!

Here is my final 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:\share\CBF MASTER FILES\CBF Operating Folder\WorkFlowOne\Updated Billing\" & MyYear & "\" & MyMonth & "\Daily CSV Files\"
MyMasterPath = "P:\share\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) & " job# ") 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
'------------------------------------------
MyDate = SrceBook.Worksheets("Sheet1").Cells(3, 1).Value
MyDate = Format(MyDate, "mm.dd.yy")
MyNum = SrceBook.Worksheets("Sheet1").Cells(2, 1).Value
MyNum = Format(MyNum, "0")
SrceBook.SaveAs SaveAsPath & MyNum & " " & MyDate & ".csv"
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