Pulling DIR information into Access

[JohnV]

I want to pull directory information into Access to do
week to week comparisons. I have a number of files that
get populated. In DOS I my command looks like:

g:\data dir /s [filename].xls

I gather the following information:
1. Count of Files
2. Size of each file
3. Date/Time of each file

Is there a way to run this command and grab the input into
an array (or the result of any similar command)? I want
to populate a table for historical tracking.

Thanks,
JohnV

 

[Phobos]

You can use redirection in DOS to re-direct output to a file rather than the
default display device, the syntax for a dir command is:

dir a: >> textfile.txt

You could then import this file into Access.

P

 

[TC]

Hi, could you make your posts appear as replies to the original posts,
instead of *as* original posts?

TC

Use the File object of the Scripting.FileSystemObject to
gather the information. The FileSystemObject exposes
objects and methods that let you traverse directories and
gather all manner of information about files. To start,
you will need to add the Scripting RunTime library to the
list of References for your applicatiion.

The FSO is documented at
http://msdn.microsoft.com/library/en-
us/script56/html/sgprogrammingfilesystemobject.asp.

Kind regards,
David A. Gray
http://www.p6c.com/

You are more important than any technology we may employ.

-----Original Message-----
I want to pull directory information into Access to do
week to week comparisons. I have a number of files that
get populated. In DOS I my command looks like:

g:\data dir /s [filename].xls

I gather the following information:
1. Count of Files
2. Size of each file
3. Date/Time of each file

Is there a way to run this command and grab the input into
an array (or the result of any similar command)? I want
to populate a table for historical tracking.

Thanks,
JohnV
.

 

[D. A. Gray]

Use the File object of the Scripting.FileSystemObject to
gather the information. The FileSystemObject exposes
objects and methods that let you traverse directories and
gather all manner of information about files. To start,
you will need to add the Scripting RunTime library to the
list of References for your applicatiion.

The FSO is documented at
http://msdn.microsoft.com/library/en-
us/script56/html/sgprogrammingfilesystemobject.asp.

Kind regards,
David A. Gray
http://www.p6c.com/

You are more important than any technology we may employ.

 

[Jon]

You can use redirection in DOS to re-direct output to a file rather than the
default display device, the syntax for a dir command is:

dir a: >> textfile.txt

You could then import this file into Access.

P

I want to pull directory information into Access to do
week to week comparisons. I have a number of files that
get populated. In DOS I my command looks like:

g:\data dir /s [filename].xls

I gather the following information:
1. Count of Files
2. Size of each file
3. Date/Time of each file

Is there a way to run this command and grab the input into
an array (or the result of any similar command)? I want
to populate a table for historical tracking.

Thanks,
JohnV

John

You could also do this in Access, using built-in functions:


Public Sub sFileInfo(strFolder As String)
' Procedure to get files in a directory, together with size (in
bytes) and file date/time
' Accepts:
' strFolder - The name of the folder to start at
' On Error GoTo E_Handle
Dim astrFile() As String ' Array to hold file names
Dim alngFileSize() As Long ' Array to hold size of files
Dim adtmFileTime() As Date ' Array to hold the date of files
Dim strFile As String
Dim intLoop As Integer
If Right(strFolder, 1) <> "/" Then strFolder = strFolder & "/"
intLoop = 1
ReDim astrFile(1 To 100)
ReDim alngFileSize(1 To 100)
ReDim adtmFileTime(1 To 100)
strFile = Dir(strFolder, vbNormal)
Do Until strFile = ""
astrFile(intLoop) = strFolder & strFile
alngFileSize(intLoop) = FileLen(astrFile(intLoop))
adtmFileTime(intLoop) = FileDateTime(astrFile(intLoop))
intLoop = intLoop + 1
strFile = Dir
Loop
ReDim Preserve astrFile(1 To intLoop - 1)
ReDim Preserve alngFileSize(1 To intLoop - 1)
ReDim Preserve adtmFileTime(1 To intLoop - 1)
' Now just display the information found
Debug.Print intLoop - 1 & " files in " & strFolder
For intLoop = 1 To UBound(astrFile)
Debug.Print astrFile(intLoop) & vbTab & alngFileSize(intLoop)
& vbTab & adtmFileTime(intLoop)
Next intLoop
sExit:
On Error Resume Next
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf & "sFileInfo", vbOKOnly +
vbCritical, "Error: " & Err.Number
Resume sExit
End Sub

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring -
http://a.webring.com/hub?ring=microsoftaccess

 

[Douglas J. Steele]

My guess is that he's using the web interface to the newsgroups at
http://msdn.microsoft.com/newsgroups/ and it doesn't put the RE: in it as
it's supposed to. Microsoft is aware of that problem, and claim they're
fixing it in the next release. (My preference would be to scrap the web
interface, but I realize that some people don't have newsgroup access
through firewalls and the like.)

--
Doug Steele, Microsoft Access MVP

Hi, could you make your posts appear as replies to the original posts,
instead of *as* original posts?

TC

Use the File object of the Scripting.FileSystemObject to
gather the information. The FileSystemObject exposes
objects and methods that let you traverse directories and
gather all manner of information about files. To start,
you will need to add the Scripting RunTime library to the
list of References for your applicatiion.

The FSO is documented at
http://msdn.microsoft.com/library/en-
us/script56/html/sgprogrammingfilesystemobject.asp.

Kind regards,
David A. Gray
http://www.p6c.com/

You are more important than any technology we may employ.

-----Original Message-----
I want to pull directory information into Access to do
week to week comparisons. I have a number of files that
get populated. In DOS I my command looks like:

g:\data dir /s [filename].xls

I gather the following information:
1. Count of Files
2. Size of each file
3. Date/Time of each file

Is there a way to run this command and grab the input into
an array (or the result of any similar command)? I want
to populate a table for historical tracking.

Thanks,
JohnV
.