0

Following this thread:

VBA Excel - problem with saving file on newly created folder

I've changed the circumstances slightly by adding an additional subfolder.

Unfortunately, it looks like now I am unable to save the excel file in the target directory, as VBA Excel keeps throwing the 1004 error, that the path wasn't found.

My code looks like this:

Dim BPcode As String, GPcode As String, PCity As String, Folder1 As String, Folder2 As String
Dim Target As Range
Dim SelectedRow As Long

Dim PathName As String, fileName As String

Set Target = ActiveCell

SelectedRow = Target.Row

Set WAddress = cstws.Range("K" & SelectedRow)
Set City = cstws.Range("L" & SelectedRow)

PathName = GetLocalPath(ThisWorkbook.path)
fileName = RemoveForbiddenFilenameChars(WAddress)

BPcode = Split(PCode, " ")(0)
GPcode = Remove_Number(BPcode)

Select Case GPcode
Case "CB"
PCity = "Cambridge"
Case "NN"
PCity = "Northampton"
End Select

Folder1 = RemoveForbiddenFilenameChars(UCase(PCity)) & " [" & GPcode & "]"
Folder2 = RemoveForbiddenFilenameChars(UCase(City))

If Folder2 = "" Then MsgBox "What is the Site Address City?", vbCritical: Exit Sub
If fileName = "" Then MsgBox "Address incorrect or not provided", vbCritical: Exit Sub

Dim NewFolderPath As String
NewFolderPath = PathName & Application.PathSeparator & Folder1
If Dir(NewFolderPath, vbDirectory) = "" Then
MkDir NewFolderPath
Else
MsgBox "The Folder " & Folder1 & " already exists"
End If

Dim NewSubFolderPath As String
NewSubFolderPath = NewFolderPath & Application.PathSeparator & Folder2
If Dir(NewSubFolderPath, vbDirectory) = "" Then
MkDir NewSubFolderPath
Else
MsgBox "The Folder " & Folder2 & " already exists"
End If

Set wkb = Workbooks.Add

With wkb

Application.DisplayAlerts = False

.SaveAs fileName:=NewSubFolderPath & "\" & fileName & "- file", FileFormat:=xlOpenXMLWorkbookMacroEnabled

I tried also with NewSubFolderPath & Application.PathSeparator & fileName & "-file", but unfortunately it didn't work either. Is there any way for pointing out the error here? The saveAs work just when the solely PathName is used. Otherwise, Error 1004 is thrown.

Geographos
  • 827
  • 2
  • 23
  • 57
  • `PathName` is a local path (not http) ? – Tim Williams Mar 29 '23 at 15:12
  • Yes, it's the local one. It will change only when the file is transferred to the other path. In fact, eventually, I would need it on the SharePoint (HTTP), therefore the alternative solution could be also vital. – Geographos Mar 29 '23 at 15:19
  • 1
    What line causes the error? The `.SaveAs`? Check the values of those two variables, NewSubFolderPath and FileName once this line of code is reached. Make sure they are what you're expecting them to be. – TehDrunkSailor Mar 29 '23 at 17:00
  • You put `[` and `]` in the folder name, but then `RemoveForbiddenFilenameChars` removes them. Those characters are allowed in a folder name, but not in an Excel *file* name. – Tim Williams Mar 29 '23 at 17:28
  • If you will need to create those new folders on SharePoint then the only (easy) way to do that is to sync the folder to your local drive and create the folders in the synced copy. – Tim Williams Mar 29 '23 at 18:32
  • Set WAddress = ... Set City = ... The WAddress and City are Objects? If not, then the: fileName = RemoveForbiddenFilenameChars(WAddress) what returns to filename? – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Mar 29 '23 at 19:09
  • @TimWilliams that might be the case! How do you know, that they're not allowed in an Excel file name? – Geographos Mar 29 '23 at 19:22
  • eg: https://microsoft.public.office.misc.narkive.com/nqTHeN3N/can-t-use-or-characters-in-file-names – Tim Williams Mar 29 '23 at 19:27
  • How about if I would need these characters? Is there removing RemoveForbiddenFileNameChars the case? – Geographos Mar 29 '23 at 19:28
  • After removing the RemoveForbiddenFilenameChars is still the same. It works, when the "[""]" are removed, so are there other special characters, which I could use instead? – Geographos Mar 30 '23 at 08:49
  • Could use `-`, `_`, `.` or a space (though if it's at the end, I wouldn't recommend the space) – Notus_Panda Mar 31 '23 at 08:23

0 Answers0