[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Run time Error

Posted on 2011-03-06
2
Medium Priority
?
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 1

Assisted Solution

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

Accepted Solution

by:
RobSampson earned 1600 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

650 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