hi

Are file operations possible as part of Excel macros?

Looking for something similar to what's available in a batch file.

e.g.

create folder
move or copy file

Vivek

Vivek, check out this page from Tek Tips, it has some really good info.
http://www.tek-tips.com/faqs.cfm?fid=4116

Charles

kk wrote:
hi

Are file operations possible as part of Excel macros?

Looking for something similar to what's available in a batch file.

e.g.

create folder
move or copy file

Vivek

Hey Charles,

Looks good. Thanks.

Vivek

"Die_Another_Day" wrote in message
ups.com...
Vivek, check out this page from Tek Tips, it has some really good info.
http://www.tek-tips.com/faqs.cfm?fid=4116

Charles

kk wrote:
hi

Are file operations possible as part of Excel macros?

Looking for something similar to what's available in a batch file.

e.g.

create folder
move or copy file

Vivek

There are also built in function in VBA

mkdir
chdir
kill
filecopy
name (moves a file)
many others.

--
Regards,
Tom Ogilvy


"kk" wrote:

Hey Charles,

Looks good. Thanks.

Vivek

"Die_Another_Day" wrote in message
ups.com...
Vivek, check out this page from Tek Tips, it has some really good info.
http://www.tek-tips.com/faqs.cfm?fid=4116

Charles

kk wrote:
hi

Are file operations possible as part of Excel macros?

Looking for something similar to what's available in a batch file.

e.g.

create folder
move or copy file

Vivek

Tom, what is the best way to check for an existing file/directory? The
DIR function?

Charles
Tom Ogilvy wrote:
There are also built in function in VBA

mkdir
chdir
kill
filecopy
name (moves a file)
many others.

--
Regards,
Tom Ogilvy


"kk" wrote:

Hey Charles,

Looks good. Thanks.

Vivek

"Die_Another_Day" wrote in message
ups.com...
Vivek, check out this page from Tek Tips, it has some really good info.
http://www.tek-tips.com/faqs.cfm?fid=4116

Charles

kk wrote:
hi

Are file operations possible as part of Excel macros?

Looking for something similar to what's available in a batch file.

e.g.

create folder
move or copy file

Vivek

the most robust way is with the filesystem object, but dir can be used as
well. The problem with dir is that if used thoughtlessly it can provide
misleading results if there is a file name with no extension that matches the
directory inquired about (and that directory does not exist).


Option Explicit


Sub Tester2()
Dim rw As Long
Dim ilevel As Long
Dim sPath As String
Dim Drive As String
Dim AnyName As String
Dim Volume As String
Dim i As Integer


Drive = InputBox("Enter Drive to Catalog" & _
vbCrLf & "(ie. D or d) ")
If Trim(Drive) = "" Then Exit Sub
Drive = StrConv(Drive, vbUpperCase)
Drive = Drive & ":\"


Dim sArr() As String
ReDim sArr(1 To 1)
rw = 1
ilevel = 1
Cells(rw, ilevel) = Dir(Drive, vbVolume)
rw = rw + 1
Cells(rw, ilevel) = "\"
rw = rw + 1
AnyName = Dir(Drive, vbDirectory)
Do While AnyName < ""
If AnyName < "." And AnyName < ".." _
And GetAttr("H:\" & AnyName) = vbDirectory Then
sArr(UBound(sArr)) = AnyName
ReDim Preserve sArr(1 To UBound(sArr) + 1)
End If
AnyName = Dir()
Loop
ilevel = ilevel + 1
For i = 1 To UBound(sArr) - 1
AnyName = sArr(i)
Cells(rw, ilevel) = AnyName
rw = rw + 1
sPath = Drive & AnyName & "\"
GetSubs sPath, rw, ilevel
Next


AnyName = Dir(Drive, vbNormal)
Do While AnyName < ""
Cells(rw, ilevel) = AnyName
rw = rw + 1
AnyName = Dir()
Loop
End Sub


Sub GetSubs(sPath As String, _
rw As Long, ilevel As Long)
Dim sName As String
Dim i As Long
Dim sArr()


ReDim sArr(1 To 1)
sName = Dir(sPath, vbDirectory)
Do While sName < ""
If sName < "." And sName < ".." And _
GetAttr(sPath & sName) = vbDirectory Then
sArr(UBound(sArr)) = sName
ReDim Preserve sArr(1 To UBound(sArr) + 1)
End If
sName = Dir()
Loop
For i = 1 To UBound(sArr) - 1
sName = sArr(i)
Cells(rw, ilevel + 1) = sName
rw = rw + 1
GetSubs sPath & sName & "\", rw, ilevel + 1
Next i
sName = Dir(sPath, vbNormal)
Do While sName < ""
Cells(rw, ilevel + 1) = sName
rw = rw + 1
sName = Dir()
Loop
End Sub


is some old code that is illustrative.

--
Regards,
Tom Ogilvy



"Die_Another_Day" wrote:

Tom, what is the best way to check for an existing file/directory? The
DIR function?

Charles
Tom Ogilvy wrote:
There are also built in function in VBA

mkdir
chdir
kill
filecopy
name (moves a file)
many others.

--
Regards,
Tom Ogilvy


"kk" wrote:

Hey Charles,

Looks good. Thanks.

Vivek

"Die_Another_Day" wrote in message
ups.com...
Vivek, check out this page from Tek Tips, it has some really good info.
http://www.tek-tips.com/faqs.cfm?fid=4116

Charles

kk wrote:
hi

Are file operations possible as part of Excel macros?

Looking for something similar to what's available in a batch file.

e.g.

create folder
move or copy file

Vivek

of course if your intent is to create it if it doesn't, there is no need to
check

On Error Resume Next
MkDir "C:\Mydir"
On Error goto 0

of course if it is multi tiered and you don't know if all the tiers are
there, it is more complex.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

Tom, what is the best way to check for an existing file/directory? The
DIR function?

Charles
Tom Ogilvy wrote:
There are also built in function in VBA

mkdir
chdir
kill
filecopy
name (moves a file)
many others.

--
Regards,
Tom Ogilvy


"kk" wrote:

Hey Charles,

Looks good. Thanks.

Vivek

"Die_Another_Day" wrote in message
ups.com...
Vivek, check out this page from Tek Tips, it has some really good info.
http://www.tek-tips.com/faqs.cfm?fid=4116

Charles

kk wrote:
hi

Are file operations possible as part of Excel macros?

Looking for something similar to what's available in a batch file.

e.g.

create folder
move or copy file

Vivek