Importing a list of files into a database

[judy]

I have a folder that contains over 20000 files. I want to create a list of
those files (not print them!) and import that list into Excel or Access.

If possible I want the spreadsheet to include a hyperlink to the original
file, but if that can't be done just the name/date/size information will do.

Any help gratefully received - the thought of typing out 20000 filenames is
scaring me!

 

[Herb Tyson [MVP]]

One way... open a command line window and navigate to that folder using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I would edit
that file using Word (because I'm good at editing with Word... you might be
equally adept in Excel), and use find/replace and other tools to convert the
listing into a table. At the same time, I'd use find/replace to add the
necessary information to the file names so that they'd become links rather
than static text. Finally, once it's in the form of a Word table, I would
copy/paste it into Excel, and (if desired), import the .xls into Access.

 

[judy]

Many thanks! That gives me a list that I can manipulate - you've saved me
days of work

If anyone knows a way to get the filenames to show as hyperlinks, please let
me know.

One way... open a command line window and navigate to that folder using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I would edit
that file using Word (because I'm good at editing with Word... you might be
equally adept in Excel), and use find/replace and other tools to convert the
listing into a table. At the same time, I'd use find/replace to add the
necessary information to the file names so that they'd become links rather
than static text. Finally, once it's in the form of a Word table, I would
copy/paste it into Excel, and (if desired), import the .xls into Access.

--
Herb Tyson MS MVP
http://www.herbtyson.com
Please respond in the newsgroups so everyone can follow along.

I have a folder that contains over 20000 files. I want to create a list of
those files (not print them!) and import that list into Excel or Access.

If possible I want the spreadsheet to include a hyperlink to the original
file, but if that can't be done just the name/date/size information will
do.

Any help gratefully received - the thought of typing out 20000 filenames
is
scaring me!

 

[Beth Melton]

If you are using Access and if the file names include the path you
should be able to change the data type to Hyperlink to convert them to
hyperlinks. If they don't have the path then here's a Word macro you
can use to generate a list of file names with the path in a Word
document. Then, as you did before, copy/paste the data into Excel (you
don't need to covert it into a table first), then from Excel into
Access.

If you don't know how to use this macro then take a look at this
article:
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Sub ListFolderContents()
'Macro created by Beth Melton
Dim NewDoc As Document
Dim DocList As String, DocDir As String
Dim Msg As String, Msg2 As String, Title As String
Dim MsgType As Integer
Msg = "List Folder Contents cancelled"
Msg2 = "No documents found in selected folder"
Title = "Folder Contents"
MsgType = vbInformation
With Dialogs(wdDialogCopyFile)
If .Display <> 0 Then
DocDir = .directory
DocDir = Replace(DocDir, Chr(34), "")
DocList = Dir(DocDir & "*.*")
Set NewDoc = Documents.Add
Do Until DocList = ""
NewDoc.Range.InsertAfter DocDir & "\" & DocList & vbCr
DocList = Dir()
Loop
If NewDoc.Characters.Count = 1 Then
MsgBox Msg2, MsgType, Title
Exit Sub
End If
Set NewDoc = Nothing
Else
MsgBox Msg, MsgType, Title
End If
End With
End Sub

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

Many thanks! That gives me a list that I can manipulate - you've
saved me
days of work

If anyone knows a way to get the filenames to show as hyperlinks,
please let
me know.

One way... open a command line window and navigate to that folder
using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I
would edit
that file using Word (because I'm good at editing with Word... you
might be
equally adept in Excel), and use find/replace and other tools to
convert the
listing into a table. At the same time, I'd use find/replace to add
the
necessary information to the file names so that they'd become links
rather
than static text. Finally, once it's in the form of a Word table, I
would
copy/paste it into Excel, and (if desired), import the .xls into
Access.

--
Herb Tyson MS MVP
http://www.herbtyson.com
Please respond in the newsgroups so everyone can follow along.

I have a folder that contains over 20000 files. I want to create a
list of
those files (not print them!) and import that list into Excel or
Access.

If possible I want the spreadsheet to include a hyperlink to the
original
file, but if that can't be done just the name/date/size
information will
do.

Any help gratefully received - the thought of typing out 20000
filenames
is
scaring me!

 

[judy]

Thank you!

I've never used this community feature before, you people are lifesavers LOL

judy

If you are using Access and if the file names include the path you
should be able to change the data type to Hyperlink to convert them to
hyperlinks. If they don't have the path then here's a Word macro you
can use to generate a list of file names with the path in a Word
document. Then, as you did before, copy/paste the data into Excel (you
don't need to covert it into a table first), then from Excel into
Access.

If you don't know how to use this macro then take a look at this
article:
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Sub ListFolderContents()
'Macro created by Beth Melton
Dim NewDoc As Document
Dim DocList As String, DocDir As String
Dim Msg As String, Msg2 As String, Title As String
Dim MsgType As Integer
Msg = "List Folder Contents cancelled"
Msg2 = "No documents found in selected folder"
Title = "Folder Contents"
MsgType = vbInformation
With Dialogs(wdDialogCopyFile)
If .Display <> 0 Then
DocDir = .directory
DocDir = Replace(DocDir, Chr(34), "")
DocList = Dir(DocDir & "*.*")
Set NewDoc = Documents.Add
Do Until DocList = ""
NewDoc.Range.InsertAfter DocDir & "\" & DocList & vbCr
DocList = Dir()
Loop
If NewDoc.Characters.Count = 1 Then
MsgBox Msg2, MsgType, Title
Exit Sub
End If
Set NewDoc = Nothing
Else
MsgBox Msg, MsgType, Title
End If
End With
End Sub

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

Many thanks! That gives me a list that I can manipulate - you've
saved me
days of work

If anyone knows a way to get the filenames to show as hyperlinks,
please let
me know.

One way... open a command line window and navigate to that folder
using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I
would edit
that file using Word (because I'm good at editing with Word... you
might be
equally adept in Excel), and use find/replace and other tools to
convert the
listing into a table. At the same time, I'd use find/replace to add
the
necessary information to the file names so that they'd become links
rather
than static text. Finally, once it's in the form of a Word table, I
would
copy/paste it into Excel, and (if desired), import the .xls into
Access.

--
Herb Tyson MS MVP
http://www.herbtyson.com
Please respond in the newsgroups so everyone can follow along.
I have a folder that contains over 20000 files. I want to create a
list of
those files (not print them!) and import that list into Excel or
Access.

If possible I want the spreadsheet to include a hyperlink to the
original
file, but if that can't be done just the name/date/size
information will
do.

Any help gratefully received - the thought of typing out 20000
filenames
is
scaring me!

 

[Beth Melton]

Glad to help out. Hope our suggestions have worked out for you.

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Word FAQ: http://mvps.org/word
TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/

 

[PJ]

I have a somewhat similar dilemma - is there a way to copy detailed file
lists (showing doc name, modify date, creation date and maybe size) from
Windows Explorer directories or from some other source and import them into
Word/Excel (preferably Excel) so that they can later be sorted by date/time
once files from various directories have been pasted into one long list?
Peggy Jo (technically challenged)

One way... open a command line window and navigate to that folder using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I would edit
that file using Word (because I'm good at editing with Word... you might be
equally adept in Excel), and use find/replace and other tools to convert the
listing into a table. At the same time, I'd use find/replace to add the
necessary information to the file names so that they'd become links rather
than static text. Finally, once it's in the form of a Word table, I would
copy/paste it into Excel, and (if desired), import the .xls into Access.

--
Herb Tyson MS MVP
http://www.herbtyson.com
Please respond in the newsgroups so everyone can follow along.

I have a folder that contains over 20000 files. I want to create a list of
those files (not print them!) and import that list into Excel or Access.

If possible I want the spreadsheet to include a hyperlink to the original
file, but if that can't be done just the name/date/size information will
do.

Any help gratefully received - the thought of typing out 20000 filenames
is
scaring me!

 

[Susan]

One way... open a command line window and navigate to that folder using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I would edit
that file using Word (because I'm good at editing with Word... you might be
equally adept in Excel), and use find/replace and other tools to convert the
listing into a table. At the same time, I'd use find/replace to add the
necessary information to the file names so that they'd become links rather
than static text. Finally, once it's in the form of a Word table, I would
copy/paste it into Excel, and (if desired), import the .xls into Access.

--
Herb Tyson MS MVP
http://www.herbtyson.com
Please respond in the newsgroups so everyone can follow along.

I have a folder that contains over 20000 files. I want to create a list of
those files (not print them!) and import that list into Excel or Access.

If possible I want the spreadsheet to include a hyperlink to the original
file, but if that can't be done just the name/date/size information will
do.

Any help gratefully received - the thought of typing out 20000 filenames
is
scaring me!

Herb's suggestion to Judy may answer my question of how to print a list of
filenames. I'm eager to tackle it, but first I need to know what and where a
"command line window" is.

 

[Bob I]

From Windows Help and Support:

To open a command prompt, click Start, point to Programs, point to
Accessories, and then click Command Prompt.

:

One way... open a command line window and navigate to that folder using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I would edit
that file using Word (because I'm good at editing with Word... you might be
equally adept in Excel), and use find/replace and other tools to convert the
listing into a table. At the same time, I'd use find/replace to add the
necessary information to the file names so that they'd become links rather
than static text. Finally, once it's in the form of a Word table, I would
copy/paste it into Excel, and (if desired), import the .xls into Access.

--
Herb Tyson MS MVP
http://www.herbtyson.com
Please respond in the newsgroups so everyone can follow along.

I have a folder that contains over 20000 files. I want to create a list of
those files (not print them!) and import that list into Excel or Access.

If possible I want the spreadsheet to include a hyperlink to the original
file, but if that can't be done just the name/date/size information will
do.

Any help gratefully received - the thought of typing out 20000 filenames
is
scaring me!

Herb's suggestion to Judy may answer my question of how to print a list of
filenames. I'm eager to tackle it, but first I need to know what and where a
"command line window" is.

 

[Susan]

Wow! Thank you, Bob!

From Windows Help and Support:

To open a command prompt, click Start, point to Programs, point to
Accessories, and then click Command Prompt.

:

One way... open a command line window and navigate to that folder using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I would edit
that file using Word (because I'm good at editing with Word... you might be
equally adept in Excel), and use find/replace and other tools to convert the
listing into a table. At the same time, I'd use find/replace to add the
necessary information to the file names so that they'd become links rather
than static text. Finally, once it's in the form of a Word table, I would
copy/paste it into Excel, and (if desired), import the .xls into Access.

--
Herb Tyson MS MVP
http://www.herbtyson.com
Please respond in the newsgroups so everyone can follow along.

I have a folder that contains over 20000 files. I want to create a list of
those files (not print them!) and import that list into Excel or Access.

If possible I want the spreadsheet to include a hyperlink to the original
file, but if that can't be done just the name/date/size information will
do.

Any help gratefully received - the thought of typing out 20000 filenames
is
scaring me!

Herb's suggestion to Judy may answer my question of how to print a list of
filenames. I'm eager to tackle it, but first I need to know what and where a
"command line window" is.

 

[Bob I]

You're welcome, have a good day!

Wow! Thank you, Bob!

:

From Windows Help and Support:

To open a command prompt, click Start, point to Programs, point to
Accessories, and then click Command Prompt.


Susan wrote:

:



One way... open a command line window and navigate to that folder using CD
[disk]:\{foldername}

THen, at the command line, type:

DIR >filelist.txt

That will redirect the file listing into filelist.txt. Next, I would edit
that file using Word (because I'm good at editing with Word... you might be
equally adept in Excel), and use find/replace and other tools to convert the
listing into a table. At the same time, I'd use find/replace to add the
necessary information to the file names so that they'd become links rather
than static text. Finally, once it's in the form of a Word table, I would
copy/paste it into Excel, and (if desired), import the .xls into Access.

--
Herb Tyson MS MVP
http://www.herbtyson.com
Please respond in the newsgroups so everyone can follow along.


I have a folder that contains over 20000 files. I want to create a list of
those files (not print them!) and import that list into Excel or Access.

If possible I want the spreadsheet to include a hyperlink to the original
file, but if that can't be done just the name/date/size information will
do.

Any help gratefully received - the thought of typing out 20000 filenames
is
scaring me!



Herb's suggestion to Judy may answer my question of how to print a list of
filenames. I'm eager to tackle it, but first I need to know what and where a
"command line window" is.