Solved

Run time Error

Posted on 2011-03-06
2
294 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now