Loop through the folder with PDF`s (1 Viewer)

alihussain

New member
Local time
Today, 19:34
Joined
Jul 1, 2020
Messages
19
Hi,
I created a table which called Table1 to contain 2 fields ID= auto number & Path=long text, then i created a form on it ID, Path, and command button which will function returns the path of files

Code:
Private Sub Command10_Click()
    On Error Resume Next
    Dim fso As New Scripting.FileSystemObject
    Dim folder As Scripting.folder            
    Dim file As Scripting.file              
    Dim fileList As Scripting.Files         
    Dim fullPath As String                   
    dim folderPath as string
 

    folderPath = "d:\scan"
    Set folder = fso.GetFolder(folderPath)
    Set fileList = folder.Files
    For Each file In fileList
        fullPath = folder.path & "\" & file.Name
   
       Dim Ttb2 As Recordset
       Set Ttb2 = CurrentDb.OpenRecordset("Table1")
       Ttb2.AddNew
       Ttb2![ID] = Forms![Table1]![ID]
       Ttb2![path] = fullPath
       Ttb2.Update
    Next file
 
    End Function





Nothing happens after I run this code I hope u guys help me with this.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 16:34
Joined
Sep 21, 2011
Messages
14,044
So what actually happens.?
Do you get the last file added to table1?

Walk though the code with F8.
Put a debug.print statement in to see what variables are at certain locations.
 

alihussain

New member
Local time
Today, 19:34
Joined
Jul 1, 2020
Messages
19
After i click the command button nothing happens, when I press run to test it, it prompts me for a macro name.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:34
Joined
Sep 21, 2011
Messages
14,044
After i click the command button nothing happens, when I press run to test it, it prompts me for a macro name.
And that is? :(

FWIW the filelist code works OK.
However you need to add the records within the loop of the filelist process, but the recordset outside that loop.
You also do not need the ID, that gets created when you add a record

Is the button called Command10

Plus, do not change the code without advising members. That code showing is NOT what was originally posted. :(
 
Last edited:

alihussain

New member
Local time
Today, 19:34
Joined
Jul 1, 2020
Messages
19
@Gasman thx for ur help but i have one question let say we want to open the target folder and get the path of 1 file in it without any loop?
 

alihussain

New member
Local time
Today, 19:34
Joined
Jul 1, 2020
Messages
19
I wrote this code and nothing happens 😁 again


Code:
Sub loop()
   Dim MyFolder As String 'Path collected from the folder picker dialog
   Dim MyFile As String 'Filename obtained by DIR function

On Error Resume Next


With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Filters.Clear
.Filters.Add "All Files", "*.*"
.AllowMultiSelect = False
   If .Show = True Then
        MyFolder = .SelectedItems(1) & “ \ ”
    
 'If no folder is selected, abort
   Else
      MsgBox "You did not select a folder"
      Exit Sub
   End If
 'Assign selected folder to MyFolder
End With
MyFile = Dir(MyFolder)

End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:34
Joined
Sep 21, 2011
Messages
14,044
Comment out the On Error and walk though your code.
If you want to return something, you need it as a function and the variable returned needs to be the name of the function.

Loop is probably not the greatest name either?
You start with Sub and end with Function?
 

alihussain

New member
Local time
Today, 19:34
Joined
Jul 1, 2020
Messages
19
Still my path field empty, but when I run this the only thing happens is:- added 1 new field and the path empty in the Table1
Code:
Public Function files()
   Dim MyFolder As String 'Path collected from the folder picker dialog
   Dim MyFile As String 'Filename obtained by DIR function

'On Error Resume Next

'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Please select a folder"
.Filters.Clear
.Filters.Add "All Files", "*.*"
.AllowMultiSelect = False
   If .Show = True Then
        MyFolder = .SelectedItems(1) & " \ "
    
 'If no folder is selected, abort
   Else
      MsgBox "You did not select a folder"
      Exit Function
   End If
 'Assign selected folder to MyFolder
End With
 MyFile = Dir(MyFolder) 'DIR gets the first file of the folder


    Dim Ttb2 As Recordset
    Set Ttb2 = CurrentDb.OpenRecordset("Table1")
    Ttb2.AddNew
    Ttb2![path] = MyFile
    Ttb2.Update

End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:34
Joined
Sep 21, 2011
Messages
14,044
As I have stated before, walk through the code (line by line if need be) and inspect the variables.
 

alihussain

New member
Local time
Today, 19:34
Joined
Jul 1, 2020
Messages
19
walk through the code ?? well i done that multiple times but I figure it out by googling the problem and thx for ur help
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:34
Joined
Sep 21, 2011
Messages
14,044
Well I walked through the code, did what I advised you to do and found the problem immediately? :unsure:

I did that for both pieces of code.?
 

Isaac

Lifelong Learner
Local time
Today, 09:34
Joined
Mar 14, 2017
Messages
8,738
@alihussain
As a general rule, do not use On Error Resume Next in your code, unless you have an extremely specific reason (rare) to do so. All that does is lead you to the situation you found yourself at the beginning of this post, where nothing happened. On Error Resume Next is telling the code that even though there be an error which it would like to raise and present to you for display. rather, ignore that and continue executing subsequent lines of code. There is no good reason you would want this to be the case (except in very limited circumstances when you are more experienced and these do not apply here).
See this discussion if it may help.
Gasman was right. You will do yourself a big favor (and save yourself a lot of time asking) if you learn to troubleshoot and debug. Lots of content available if you research how to debug vba.
 

Users who are viewing this thread

Top Bottom