Solved

Run time Error

Posted on 2011-03-06
2
297 Views
Last Modified: 2012-05-11
Hi,

While using the below code in am getting a run time error 3075 because of a special character '(Apostrophe) in the FileItem.Name and FileItem.Path.

Your help on this is highly appreciated.

Sub List_Of_Files_In_Folder()
    ListFilesInFolder "I:\", True
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
          CurrentDb.Execute "INSERT INTO MyTable (CurrentFilePath,DestinationFilePath,ParentFolder,FileName,FileSize,FileType,DateCreated,DateLastAccessed,DateLastModified,Attributes,ShortFileName) " & _
                "VALUES (" & _
                "'" & FileItem.Path & "'," & _
                "'" & "D:\Archiving " & Left(FileItem.Path, 1) & " Drive" & Mid(FileItem.Path, 3, (Len(FileItem.Path))) & "'," & _
                "'" & Mid(FileItem.Path, 4, (InStr(4, FileItem.Path, "\") - 4)) & "'," & _
                "'" & FileItem.Name & "'," & _
                "'" & FileItem.Size & "'," & _
                "'" & FileItem.Type & "'," & _
                "'" & FileItem.DateCreated & "'," & _
                "'" & FileItem.DateLastAccessed & "'," & _
                "'" & FileItem.DateLastModified & "'," & _
                "'" & FileItem.Attributes & "'," & _
                "'" & FileItem.ShortPath & FileItem.ShortName & "'" & _
                ")"
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
End Sub
0
Comment
Question by:Saichand
2 Comments
 
LVL 1

Assisted Solution

by:lalitgada
lalitgada earned 100 total points
ID: 35053440
use replace function and replace ' by "
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 400 total points
ID: 35054874
lalitgada is right, change this section:
                "VALUES (" & _
                "'" & FileItem.Path & "'," & _
                "'" & "D:\Archiving " & Left(FileItem.Path, 1) & " Drive" & Mid(FileItem.Path, 3, (Len(FileItem.Path))) & "'," & _
                "'" & Mid(FileItem.Path, 4, (InStr(4, FileItem.Path, "\") - 4)) & "'," & _
                "'" & FileItem.Name & "'," & _
                "'" & FileItem.Size & "'," & _
                "'" & FileItem.Type & "'," & _
                "'" & FileItem.DateCreated & "'," & _
                "'" & FileItem.DateLastAccessed & "'," & _
                "'" & FileItem.DateLastModified & "'," & _
                "'" & FileItem.Attributes & "'," & _
                "'" & FileItem.ShortPath & FileItem.ShortName & "'" & _
                ")"

Open in new window


to this:

                "VALUES (" & _
                "'" & Replace(FileItem.Path, "'", "''") & "'," & _
                "'" & Replace("D:\Archiving " & Left(FileItem.Path, 1) & " Drive" & Mid(FileItem.Path, 3, (Len(FileItem.Path))), "'", "''") & "'," & _
                "'" & Replace(Mid(FileItem.Path, 4, (InStr(4, FileItem.Path, "\") - 4)), "'", "''") & "'," & _
                "'" & Replace(FileItem.Name, "'", "''") & "'," & _
                "'" & FileItem.Size & "'," & _
                "'" & FileItem.Type & "'," & _
                "'" & FileItem.DateCreated & "'," & _
                "'" & FileItem.DateLastAccessed & "'," & _
                "'" & FileItem.DateLastModified & "'," & _
                "'" & FileItem.Attributes & "'," & _
                "'" & Replace(FileItem.ShortPath & FileItem.ShortName, "'", "''") & "'" & _
                ")"

Open in new window


Regards,

Rob.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question