SaveAs CSV fails when saving to desktop

[plsm5882]

Hi.

SaveAs CSV saving to the desktop fails with a 1004 error File cannot be accessed... It works fine on other folders. Haven't been able to tweak why but I am wondering if its a Windows desktop special folder issue. Here is the Function that errors:

Function SaveSheetToCSV(sNurse, sVenue, sSheet, sSavePath)
Dim sCSVFileName As String, wSh As Worksheet, bRtn As Boolean, wWb As Workbook

Set wWb = ThisWorkbook
Set wSh = wWb.Worksheets(sSheet)

bRtn = True

sSavePath = Trim(sSavePath)

If Right(sSavePath, 1) <> "\" Then sSavePath = sSavePath & "\"

sNurse = Replace(Trim(sNurse), " ", "")
sVenue = Replace(Trim(sVenue), " ", "")

sCSVFileName = Chr(34) & sSavePath & sNurse & "-" & sVenue & "-" & Format(Now(), "ddMMMyyyy-hhmm") & ".csv" & Chr(34)

Application.DisplayAlerts = False
Application.ScreenUpdating = False

On Error Resume Next
wSh.Copy
If err.Number = 0 Then
With wWb
.SaveAs Filename:=sCSVFileName, FileFormat:=xlCSV, CreateBackup:=False ' Fails here with 1004
If err.Number = 0 And CheckPath(sCSVFileName, "File") = True Then
On Error GoTo 0
.Close
Else
bRtn = False
On Error GoTo 0
End If
End With
Else
bRtn = False
On Error GoTo 0
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True

SaveSheetToCSV = bRtn
End Function

Thanks

 

[Joe4]

Get rid of the Chr(34) values in your formula:

sCSVFileName = Chr(34) & sSavePath & sNurse & "-" & sVenue & "-" & Format(Now(), "ddMMMyyyy-hhmm") & ".csv" & Chr(34)

sCSVFileName is already formatted as String. You do not need to literally add the quotations marks.
That may be causing your error.

If not, after that line, add this one:

MsgBox sCSVFileName

and confirm that it is returning a valid file path and file name.

 

[plsm5882]

Spot on! Thanks... gotten too used to vbs and autoit scripting.

 

[Joe4]

You are welcome.