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

 

[Mr Denove]

Would you suggest backslash or just date in numeric format? I am implementing better file and folder names throughout the business and yes there should be a drive letter but everyone maps their own drives therefore not consistent.

 

[RoryA]

IIRC that's one of those things that can vary depending on the version of Office you have, and the build, and the day of the week...

 

[Mr Denove]

Thanks Rory.

Fraid its still not working for me either way.

 

[Stiuart_W]

I have a macro that saves pdfs of part of my Excel workbook in multiple folders. It is run by 2 or 3 people and works fine.

However, our Corporate work environment means that we are all running the exact same version of Windows.

But you are saying that you can't get it working on YOUR computer. Make sure you are using the correct alias for the drive letter (why not try it with the drive letter path first?).

So for example with my code I would have:-

"W:\SQI Correspondence" For drive letter

"\\server01\quality\SQI correspondence" For alias

 

[Mr Denove]

I have a previous code which allowed the user to select the folder, then the filename entered and it saved fine. However if you hit OK on the navigation window too early ie the root folder not the relevant moth/year folder it saved it where it was prompted.

Long way of saying it worked without a drive letter etc

 

[Mr Denove]

Sorry guys my bad I had referenced the folder name wrong, should have been Nov 17 not 2017, now working. Sorry but thanks for your help.