VBA & batch files

[Nemesiss]

Hi,

I was wondering if anyone else has done what I need. My vba app has alot of batch files within it. Problem is, they run in the background and the user doesn't know if a part of that task the batch file executes fails or not. Is there is a way to track if something fails and to give the user a 'friendly' messsage to convey that something went wrong (or right).

Thanks.

 

[PHV]

What is a VBA batch file ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bong]

PHV: I think he means a 'shell script' (probably DOS).

OP: Assuming that you're using "shell" to execute the scripts, set the return to a variable: dblR = shell("blah blah"). Then, if dblR = 0, the script failed.

_________________
Bob Rashkin

 

[Nemesiss]

Sorry for not being more clear on the subject. I've since went away from the batch files and decided to create the same tasks within VBA itself as to the functions that were happening in the batch files. The functions were simple, copy and pasting of files from different locations and folders.

What I did was created a Sub that now does this. It works great when copying only single files, but what if I want to copy multiple files? I'd define them in a string varialbe, but I don't know how to change my existing code to allow for muliple files:

Sub Copy_File(fileNameDefinition, fileFromFolderDefinition, fileToFolderDefinition)

'This example copy all Excel files from FromPath to ToPath.
'Note: If the files in ToPath already exist it will overwrite
'existing files in this folder
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim File As String

FromPath = fileFromFolderDefinition '<< Path Defined in files definition module
ToPath = fileToFolderDefinition '<< Path Defined in files definition module

File = FromPath & fileNameDefinition '<< Name Defined in files definition module
'You can use *.* for all files or *.doc for word files

If Right(FromPath, 1) <> "\" Then
FromPath = FromPath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FileExists(File) = False Then
MsgBox File & " is missing please confirm name and location."
Exit Sub
End If

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " is missing, please confirm the network path is correct."
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " is missing, please confirm the local folder is correct."
Exit Sub
End If

FSO.CopyFile Source:=File, Destination:=ToPath
MsgBox "Successful Completion." & vbCrLf & vbCrLf & "Source files were copied from: " & vbCrLf & vbCrLf & File _
& vbCrLf & vbCrLf & " to: " & vbCrLf & vbCrLf & ToPath
End Sub

 

[Bong]

You already have a "file system object":
Set FSO = CreateObject("scripting.filesystemobject").

Now you want to get a files collection from a folder. It looks like FromPath is the folder spec for the directory you want to search.
Set f = FSO.GetFolder(FromPath)
Set fc = f.Files

Now fc is a collection of filenames of all the files in FromPath. Use For Each fn in fc ... Next to move through them.



_________________
Bob Rashkin

 

[Nemesiss]

Thanks very much for the response. Ok, so heres the thing, the multiple files that are getting copied will be very specific.

So in the batch file I would have had:

copy c:/documents and settings/temp/file1.txt
copy c:/documents and settings/temp/AnotherFile.txt
copy c:/documents and settings/temp/OneMoreFile.txt

Those are essencially hardcoded into that batch file. What I'm trying to do with the above VBA code is to define those files that are to be copied in a variable instead.

So when I'm passing mulitple files to the above code, I would have to define that as a comma delimited string or something like that wouldn't I?

fileNameDefinition = "file1.txt, AnotherFile.txt, OneMoreFile.txt"

I would then need to find those files in the collection that you suggested, wouldn't I?

 

[PHV]

A starting point:
fileNameDefinition = "file1.txt, AnotherFile.txt, OneMoreFile.txt"
For Each strFile In Split(fileNameDefinition, ",")
MsgBox Trim(strFile)
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Nemesiss]

Maybe the way I'm doing this isn't the best ideal. I have many batch files that I'm trying to get rid of, but within those batch files they copy and move files that are different names to different directories, but they must be done at the same time.

For example, this batch file backsup a file and copies the new one to other locations on the network:

copy c:/documents and settings/temp/file1.txt D:\Other_network_local\Latest_Source\
copy c:/documents and settings/temp/AnotherFile.txt D:\Different_network_local\Source\
copy c:/documents and settings/temp/OneMoreFile.txt D:\network_local\Source\

So thats what I'm trying to replace with this code. Can this be done with my code I'm attempting to build, and is this the best method or is there a better way of doing this?

thanks for the replies, its much appreciated.

 

[Nemesiss]

Actually, I think I was trying to over simplify this for users.

Instead, I'm just going to call the above sub mulitple times with the filedirectory and folder strings as need with each call; really its' the same thing anyway.

Thanks for the help.