Saving to a Fpath and Fname not working

[Mr Denove]

Dear All,

The folder path will change each month, the filename changes week on week. Im trying to use the data from the cell on the Control sheet to deal with the name changes and save to a specific folder with the new file name. But alas to no avail.
Can someone please advise why it wont work?

Sub Saving_Closing_File()
Dim sPath As String

'ThisWorkbook.Sheets("Control").Range("F23").Value = "File is saving...."
Application.ScreenUpdating = False
     
Application.DisplayAlerts = False

Application.DisplayStatusBar = False

ActiveSheet.DisplayPageBreaks = False

Application.CutCopyMode = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

'ThisWorkbook.Save
Dim FPath As String
Dim FName As String
FPath = Sheets("Control").Range("C80")
FName = Sheets("Control").Range("C81").Text

If FPath = vbNullString Then Exit Sub
Filename1 = InputBox("DATE FORMAT...DD.MM.YY", "Filename")
Filename2 = "BAU Extract " & Filename1
If StrPtr(Filename1) = 0 Then
        MsgBox "You pressed Cancel!"
        
        GoTo Error
    Else
        If Filename1 = "" Then
      
      End If

Sheets("Control").Range("C79") = Filename1
FP = Sheets("Control").Range("C80")
Dim Wk As Workbook

Set Wk = WorkBooks.Add
Application.DisplayAlerts = False

Dim arrSheets As Variant, sht As Variant
With Wk
ThisWorkbook.Sheets(Array("Data")).Copy Before:=Wk.Sheets(1)

End With

sarray = Array("Data")
Application.DisplayAlerts = False
For Each sht In Wk.Worksheets
    If IsError(Application.Match(sht.Name, sarray, 0)) Then
        sht.Delete
    End If
Next sht

'*************************
'SAVE THE BAU EXTRACT FILE
'*************************
Wk.Activate
   ActiveWorkbook.SaveAs FileName:=FPath & "\" & Filename2, FileFormat:=xlCSV, _
            ReadOnlyRecommended:=False, _
            CreateBackup:=False

ThisWorkbook.Activate
ThisWorkbook.Sheets("Control").Range("J11") = "Extract Saved"
ThisWorkbook.Sheets("Control").Range("J6").ClearContents
ThisWorkbook.Sheets("Control").Range("J9").ClearContents

ThisWorkbook.Sheets("Control").Range("F23").ClearContents
Wk.Close
Application.ScreenUpdating = False


Error:
Application.ScreenUpdating = True
     
Application.DisplayAlerts = True

Application.DisplayStatusBar = True

ActiveSheet.DisplayPageBreaks = False

Application.CutCopyMode = True

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

End If


End Sub

As always, many thanks in advance.
Stuart

 

[RoryA]

What exactly is happening?

 

[Mr Denove]

Hi Rory,

Run-time error 1004
Method 'SaveAs' of object_Workbook failed

is the pop up


Stuart

 

[RoryA]

What are the values of FPath and Filename2 at that point? Also, you can leave out the last two arguments as they won't apply to a CSV file.

 

[Mr Denove]

HI Rory

FPath is the folder path taken from concatenating in a cell to add the new month name and year when appropriate.
The Filename2 is similar its the full filename.

Can you please confirm which two arguments you are referring to?

Stuart

 

[Stiuart_W]

I assume Rory means leave these two out:-

ReadOnlyRecommended:=False, _
CreateBackup:=False


Also

When Rory asks what the values of FPath and Filename2 are he means stop the code and note down what the values are eg "C:\squidge\splat" and "funkymonkeysrus"

 

[Mr Denove]

The two values are
Filename2 = "BAU Extract 15.11.17"
FPath = "\\Internal links redacted for public display\Prep Data Nov 2017"

the file is being saved as a csv.

Thanks, Stuart

 

[Stiuart_W]

Your code works for me (not your path).

I don't like this "\\Internal......"

Shouldn't there be a drive letter? Something like ""C:\Users"

 

[RoryA]

The issue could be the dots - try adding the extension:

ActiveWorkbook.SaveAs FileName:=FPath & "\" & Filename2 & ".csv", FileFormat:=xlCSV

 

[Stiuart_W]

I assumed that might be the case Rory (definitely bad form to have the date in the format with dots) but it worked for me.