Automation

[Bob]

Related to my prior post.

Here is my situation. I have flat text files which appear
to be excel files, but which only contain the .xls
extension and no properties inherent to true excel files.
I need to automate the importation of those files into an
pre-existing access database. I wrote a batch file to
automatically "move" the data.xls to data.txt files. My
question is how can I use a macro or vb code to execute
that batch file, then automatically import all of the
newly created text files (probably around 15 files) into
access. Any help would be greatly appreciated.

 

[Steve Schapel]

Bob,

You can use the RunApp macro action to run the batch file. Just put the
path and name of your batch file, enclosed in ""s, in the Command line
argument of the macro, for example...
"C:\MyFolder\MyMove.bat"

 

[Guest]

I have tried that. The command prompt opens briefly, but
the macro doesnt execute. The file does not change its
extension.

 

[Steve Schapel]

Bob,

What version of Access? I just tested with Access 97 and Access 2000
and it works fine for me. Please post back with exact details of the
contents of the batch file, and the command line prompt you are using
with the RunApp macro.

 

[Guest]

I am running Access 2000 SR-1. The batch file contains
the 'move' command. Its contents are:

move data.xls data.txt

"C:\Documents and Settings\u26g\My
Documents\reusability\chngExtension.bat" is the command
line

When I try to execute the RunApp macro either as the only
part of a macro or the first command in a macro, the
target file does not change extension. I can double click
on the file or run it from the command prompt and almost
immediately the file changes extensions.

 

[Steve Schapel]

Bob,

My first suggestion here would be to use the full path in the batch file
for your references to data.xls and data.txt.

 

[Brad]

If all the batch file does is rename the file, then you could just use the VB Name statement.

Sub foo()
Name "c:\test.txt" As "c:\test.yay"
End Sub

-Brad