Sunday, May 25, 2008

Excel VBA - 1004 -- The file could not be accessed

Solution for 1004 -- The file could not be accessed while saving the File

1004 Microsoft Office Excel cannot access the file 'C:\temp'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

1004 -- The file could not be accessed. Try one of the following:

• Make sure the specified folder exists.
• Make sure the folder that contains the file is not read-only.
• Make sure the file name does not contain any of the following characters: < > ? [ ] : or *
• Make sure the file/path name doesn't contain more than 218 characters.

This error occurs because of unwanted characters in the File. The following function would help in removing those characters:

Function ClearCharacters(ByVal sDirtyString As String) As String

Dim arUnWantedCharacter(1 To 6) As String
Dim IsClear As Boolean
Dim i As Integer
Dim strCleanString As String
Dim j As Integer

arUnWantedCharacter(1) = "\"
arUnWantedCharacter(2) = "/"
arUnWantedCharacter(3) = "?"
arUnWantedCharacter(4) = "*"
arUnWantedCharacter(5) = "["
arUnWantedCharacter(6) = "]"

IsClear = True

strCleanString = vbNullString
For i = 1 To UBound(arUnWantedCharacter)
If InStr(1, sDirtyString, arUnWantedCharacter(i)) Then
IsClear = False
For j = 1 To Len(sDirtyString)
If Mid$(sDirtyString, j, 1) <> arUnWantedCharacter(i) Then
strCleanString = strCleanString & Mid$(sDirtyString, j, 1)
End If
Next j
sDirtyString = strCleanString
End If
Next i

If IsClear = True Then strCleanString = sDirtyString


ClearCharacters = strCleanString

End Function

See also :

Excel VBA - 1004 -- The file could not be accessed

Save and Reopen all Workbooks (Excel VBA)

Save copy of the workbook

SaveAs Dialog - Controlled Save

Save RTF document as word

No comments:

Post a Comment

Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.