By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Read a List of Filenames from a Folder Into a Table

P: 7
I need to read the filenames from a designated folder into a table and convert them to hyperlinks so the file opens when the hyperlink is clicked.

I have tried searching the web but find the code segments offered are too complex and my VBA Access skills are inadequate too understand the solutions. I have done the job in Excel with ease but need the concept of records which Excel does not recognise. I was expecting my VBA Excel skills to be transferrable but it seems one is in Russian and the other in Japanese.

I have also searched for tutorials relating to this topic but without success.

Thanks to help from the readers of this site I can covert the filename to a hyperlink but I have found and lost the way to get the filenames from the folder.

Any help would be appreciated.
Aug 29 '18 #1

✓ answered by twinnyfo

Tegglet - wlecome back!

First, remember that you don't really need to convert the filenames into hyperlinks. If you have a path and a filename, that is all that is necessary to open that file.

Basically, your code would look like this (skeleton/conceptual):
  • Find the folder to you to get the files from
  • Find the first file using Dir([FolderName], FileSpecs)
  • Save the Path and Filename to your table
  • Continue finding additional files using Dir()
  • When Dir() returns a vbNullString, you are done

We don't typically write such a procedure for our posters, especially since we know absolutely nothing about your file structures, tables or anything else for that matter. However, the principle will be the same, regardless of your specifics.

Hope this hepps!

Share this Question
Share on Google+
8 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,135
Tegglet - wlecome back!

First, remember that you don't really need to convert the filenames into hyperlinks. If you have a path and a filename, that is all that is necessary to open that file.

Basically, your code would look like this (skeleton/conceptual):
  • Find the folder to you to get the files from
  • Find the first file using Dir([FolderName], FileSpecs)
  • Save the Path and Filename to your table
  • Continue finding additional files using Dir()
  • When Dir() returns a vbNullString, you are done

We don't typically write such a procedure for our posters, especially since we know absolutely nothing about your file structures, tables or anything else for that matter. However, the principle will be the same, regardless of your specifics.

Hope this hepps!
Aug 29 '18 #2

P: 7
Thanks for your prompt response.

I do understand the concept but I am having problems getting stated with the code. What is the significance of the square brackets [] round Foldername? I thought these were used to refer to field names in the table.

I have set the variable strPath to the full path of the target folder and tried:
Expand|Select|Wrap|Line Numbers
  1. Dir([strPath], strFileSpec) = strFilename
But get a compile error “Function call on left-hand side of assignment must return Variant or object. The help on this is so much gobbledygook to me I am afraid.
Expand|Select|Wrap|Line Numbers
  1. strFilename = Dir([strPath], strFileSpec)
gives a run time error 13 type mismatch.

Sorry to be such a thicko but this is my first attempt at VBA for Access. If you know of an online tutorial covering this I would really appreciate a link, so far I have found nothing.
Aug 29 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,135
1. [FolderName] is used to designate the folder you want to get the list of files from. It is a place holder and can be a variable or a string designating that folder. thus: Dir("C:\") gives you the name of the first file in the folder C:\.

You may or may not quite understand the purpose of FileSpecs, as it is unclear from what you have provided. It simply lists the type of files you are limiting your limiting your search to. For example, if you use vbHidden, you will include hidden files.

2. I am not sure you understand how Dir() works, from your first block of code. That's OK - because we have all had to learn. Dir() returns the name of a file. so, it is never on the left hand side of an expression. Your second block of code had the proper construction, but because you have used the square brackets, you are getting an error.

Hence, your code should be:

Expand|Select|Wrap|Line Numbers
  1. strFilename = Dir(strPath, strFileSpec)
Assuming you have the proper strFileSpecs.....

This will return the first file in your folder strPath. to get the next file in that folder, simply use Dir() with no arguments, as it assumes the most recent arguments fed to it.

Make sense? Hope this hepps!
Aug 29 '18 #4

NeoPa
Expert Mod 15k+
P: 31,277
I find it's always helpful to use named arguments when you can. Especially when learning or teaching - such as in here.

The parameters for Dir() are actually PathName & Attributes. The first is actually a template string that can contain all sorts of bits including, but neither limited to nor requiring, a folder address; wild-card characters.

The second is a numeric value that uses bit-flags to indicate which file attributes (EG. Hidden; Read Only; Directory; etc.) it should be expected to include (but not necessarily require - Read the docs for this at Dir Function).

To continue scanning for the same template use Dir() without any parameters. NB. This means you cannot use Dir() for anything else within this process (Otherwise it will use the wrong template of course).
Aug 29 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,135
"Attributes" - Yes! I was typing from memory and that keyword just wouldn't come to mind. Thanks, NeoPa for the memory jog!
Aug 29 '18 #6

zmbd
Expert Mod 5K+
P: 5,287
home > topics > microsoft access / vba > insights > select a file or folder using the filedialog object

Quite often I just use the Dialog box and file multi-select and parse the selected files out of the Dialog object.
Aug 31 '18 #7

P: 7
Hi Guys,
Cracked it!

It finally boiled down to a simple Do-while loop; see code segment
Expand|Select|Wrap|Line Numbers
  1. StrPath = "E:\!TVN-REE Data\!DocumentDayFile\" ' initialise path with required folder (Dir) name
  2.  
  3. strFileName = Dir(StrPath)
  4.     Do While strFileName <> vbNullString 'vbNullString is returned when the end of the folder is reached.
  5.          strHyperlinkFile = strFileName & "#" & StrPath & strFileName 'Display Text + Full hyperlink path
  6.          strSQL = "INSERT INTO Files " & " (FName) " & "SELECT """ & strHyperlinkFile & """" & ";"
  7.          CurrentDb.Execute strSQL
  8.          strFileName = Dir() 'Dir() without arguments picks up where it left off i.e. gets the next file in the list.
  9.     Loop
  10.  
The important thing is I actually understand what is going on!! :-)
Couldn't have done it without you guys, many thanks.

Just one point, why do I have to switch from Datasheet View to Design View and back before I can see the additions in the table? The refresh button doesn't seem to have any effect.

Not sure how to close this off as there were multiple contributors.

Tegglet
Aug 31 '18 #8

NeoPa
Expert Mod 15k+
P: 31,277
Tegglet:
The important thing is I actually understand what is going on!! :-)
Absolutely. That is the most important thing.
Tegglet:
Just one point, why do I have to switch from Datasheet View to Design View and back before I can see the additions in the table? The refresh button doesn't seem to have any effect.
Look up the difference between Me.Refresh() and Me.Requery(). Very important to understand. You need the latter to show your additional records.
Tegglet:
Not sure how to close this off as there were multiple contributors.
Leave that to me. It's about more than a simple thank you, but your thank you was seen and appreciated too ;-)
Aug 31 '18 #9

Post your reply

Sign in to post your reply or Sign up for a free account.