Create a table from a Windows directory

[Yacolt]

First this is about Access VBA code - so I hope I’m in the right forum?

I’m a mechanical drafter so I don’t much about programming. However I want to use Access to organize drafting files - since most offices have this program.

Usually they have a folder that they put their all drawing files in - but seldom is there any real organization to that folder.

First step would be to get all their file names into a table.

Can VBA Access code take the file name of every file in a folder and list that in a table?

There will be sub folders inside of the overall folder

Most likely there will be some files with the same file name in different sub folders - so I would need the long path name in one column and the just the file name in another

Then the next step would to have a form where the file name is typed into a text box then a button clicked and the file row will come up. But since there will likely be duplicate files in different folders I would need to have the file in the long path also come up so I can find out if the files are the same drawing - just in different folders

I have done this by using a program that will list the file names and then I organize them in Excel then import the data into Access, a process that is a hassle and usually obsolete by the end of the week.

I still don’t really understand exactly how to get forms to do what I want but I can type the file name in a query and find out if the file is in the table!

Anyway an easy way of getting the file names and the paths into a table would be a big step forward.

Thanks a lot!

 

[CaptainD]

You could use Microsofts Common Dialog to get the file path and name to put it in your textbox and then save it to the field in your table

Reference Microsofts Common Dialog 6.0

Put a command button and a textbox on a form.

Use the names I dod and run this, it should point you in the right direction.

(This one gets Excel files)

Option Compare Database

Private Sub cmdGetFile_Click()


CommonDialog1.DialogTitle = "Select the File You Want"

CommonDialog1.Flags = cdlOFNFileMustExist

CommonDialog1.Filter = "Microsoft Excel Workbooks (*.xls)|*.xls"

CommonDialog1.ShowOpen

If Len(CommonDialog1.FileName) <> 0 Then
txtFileName.SetFocus
txtFileName.Text = CommonDialog1.FileName

End If

End Sub

 

[Yacolt]

Thanks CaptainD

I know so little about this ---- I’m sure it shows. I did make a text box and a command button. In the properties right click the command button is named is Command0.

In the command buttons event tab I have the ‘on click’ set to event procedure and that goes to the code you wrote

However I guess the text box isn’t linked to the command button because all I get is -----Runtime error ‘424’ object required.

I had the text box called CommonDialog1

I also tried naming it default because I saw somewhere in help that if you have only one text box it can be called that.

Debug always highlights --- CommonDialog1.DialogTitle = "Select the File You Want"

Help seems to really suck in Access VBA because I can’t find any really informative help.

Do I have to declare the text box name or something?

Thanks

 

[Yacolt]

Ok I see I made lot of dumb mistakes

I now have the command button named cmdGetFile like you said.

Also I see that CommonDialog1 is a command not reference to the text box.

Can I have the text box named default and do I have to DIM the text box?

Also I’ve been searching on the internet and someone has said you have to add the Microsoft CommonDialog Control to your form.

Is that necessary and if so how is that done?

Thanks

 

[HarleyQuinn]

The commondialog is a control.

If you've added the reference to the commondialog that CaptianD suggested, go to your 'More Controls' (the toolbox icon on the controls toolbar) and scroll down until you reach 'Micrsoft Common Dialog Control, version 6.0', select it and then draw it on your form (calling it CommonDialog1 for the minute) and you're away

As for the textbox, you can call it whatever you want (though I'd honestly go for something descriptive as to what goes in it rather than a particularly ambiguous 'Default'). Just be sure to change all occurances of txtFileName in the code to the name you've given you're textbox.

You also don't need to dim (dimension) the textbox.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

While that should solve the problems with the code you have at the moment I think this way of doing things would be a bit laborious.

I would (if I were you) have a look in the help files at FOR..NEXT loops and then use the search function in this(forum705) forum (which you can also post any related questions that come up along the way) and search for either FileSystemObject or FSO (which will be used in the finding of the files). Doing it this way would make the process alot more automated.

Although I admit it may be a bit advanced but could be worth a look as it will save you time in the long run.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Yacolt]

Well…..HarleyQuinn

I don’t know how up how up and away I am. When I try to add the common command from the more commands button on the toolbox I get ‘not licensed to use this command’ instead of the tool button.

I think that is why I’m hanging up at the common command part of the code.

I looked around on the net and other people have had the same problem.

I tried to find a download of CommonDialog1 but the only thing I could find with that name on it is something that says it is a printer driver and I don’t know if that will work for VBA code.

I also see a problem if I go somewhere and have to have an add in or put a special program on the computer - a lot of places don’t let the drafter mess with those kind of changes on the computer.

I will look at the link you suggested and see if I can find a way to do what I need with the regular tools that come with Access.

Thanks a lot for your help

I’ll let you know what happens!!