STORE USER ENTRY for Later Use & Replace Bat file with Access Funct.

[kev100 via AccessMonster.com]

I hope this is the right place to ask this question.....


I currently have a Macro that uses the RunApp option. The app that is run is
simply the command "c:\copyfile.bat"

It's a bat file that Prompts the user to enter a file name....then copies
that file to a certain folder.

I need to do 2 things: Use some other Access feature to get user input, then
use that input to copy the file from one location to another ......AND rename
it in the process.

The current DOS command to do such a thing is;

xcopy c:\%userinput%.txt f:\newfilename.txt /y

However, I'm really needing to STORE that user input to use in other queries
/ reports....so, I'm presuming that to do that....it's best to let some
Access feature take care of the input prompt and file copying as well.

And...it's okay if this stored variable dissappears after Access is closed...
it does not need to be stored premanently....only for that session.

Are there Access features that allow temporary storage of input.....which can
be used in other queries, etc.....and a feature to copy/rename a file...DOS-
style?

Any suggestions greatly appreciated.

Thanks

 

[freakazeud]

Hi,
sure...to store your value you can create a global variable which holds the
value until you clear it or close access.
For the moving renaming part either look into the FileSystem Object, or the
CopyFile Syntax e.g.:

Dim SourceFile, DestinationFile
SourceFile = "c:\test\yourfile.txt" ' Define source file name
DestinationFile = "c:\test01\yourfile_renamed.txt" ' Define target file name
FileCopy SourceFile, DestinationFile ' Copy source to target

If you want to delete the sourcefile then use the kill command e.g.:

Kill SourceFile

HTH
Good luck

 

[kev100 via AccessMonster.com]

Thanks very much...

I'm somewhat familiar with VB code....but still learning exactly where to put
it in Access in order for it to react in certain ways.

What I've got now is a form with 2 buttons.

One button triggers that bat file that pops up the ol' DOS window and prompts
the user for that new file name (they really on enter the first part of the
name.....the bat file adds the extension to the end).

After that runs...the DOS windows closes, and they click the next button
which opens a Macro. The Macro opens a report based on a query. That query
pulls data from the file that was just copied using the Bat file.

AND...that report actually needs to use the name of that file (entered by the
user when that .bat file runs) in the report heading.

So....what would be great is if that first button activated your VB code.....
and if that variable "DestinationFile" would be available to use in the
report heading.

Well....I'm not exactly sure where to put that VB code in order to tie it to
a command button.

Do I create a Module, then run a Macro which references the module...then tie
a command button to that macro?

If so....when it was run.....would a window pop up with an input dialog with
an entry box into which the user would enter the "Destination File" name?

And...if so...could I just somehow reference the value "Destination File" in
the report heading? I've found the "View Field List" in the report designer..
...but I'm not sure how to reference a variable created with just VB code.

Thanks again for this help. While good 'ol DOS is handy....getting this all
to stay in Access will be much cleaner.

-Kev100

Hi,
sure...to store your value you can create a global variable which holds the
value until you clear it or close access.
For the moving renaming part either look into the FileSystem Object, or the
CopyFile Syntax e.g.:

Dim SourceFile, DestinationFile
SourceFile = "c:\test\yourfile.txt" ' Define source file name
DestinationFile = "c:\test01\yourfile_renamed.txt" ' Define target file name
FileCopy SourceFile, DestinationFile ' Copy source to target

If you want to delete the sourcefile then use the kill command e.g.:

Kill SourceFile

HTH
Good luck

I hope this is the right place to ask this question.....

[quoted text clipped - 26 lines]

Thanks

 

[freakazeud]

Hi,
office applications use VBA (visual basic for applications). There is a
difference between VB and VBA.
Buttens use event procedures to execute VBA code...if you open the property
window of the button (in form design view) and go to the event tab you
probably see [event procedure] in the on click event area. Press the "..."
next to it, this will open the VBA editor and should have an on click sub
formatted for you. This is where you code probably is calling the batch file.
So take that out and replace it with the sample I gave you.
You will need to modify the code a little so it accepts the user input e.g.:

Option Compare Database
Public gbl_UserInput As String
Option Explicit

Dim strInput As String
Dim strMsg As String
Dim DestinationFile As String

strMsg = "Enter the name of the file!"

strInput = InputBox(Prompt:=strMsg, title:="File Name?")
gbl_UserInput = "c:\path to the file\ & strInput & ".doc"

DestinationFile = "c:\newpath\newfilename.doc"
FileCopy gbl_UserInput , DestinationFile

This is it...then you can call 'gbl_UserInput' anywhere in the db e.g. as
the control source of an unbound textbox control on your report!
HTH
Good luck

--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps -
http://www.oli-s.de

Thanks very much...

I'm somewhat familiar with VB code....but still learning exactly where to put
it in Access in order for it to react in certain ways.

What I've got now is a form with 2 buttons.

One button triggers that bat file that pops up the ol' DOS window and prompts
the user for that new file name (they really on enter the first part of the
name.....the bat file adds the extension to the end).

After that runs...the DOS windows closes, and they click the next button
which opens a Macro. The Macro opens a report based on a query. That query
pulls data from the file that was just copied using the Bat file.

AND...that report actually needs to use the name of that file (entered by the
user when that .bat file runs) in the report heading.

So....what would be great is if that first button activated your VB code.....
and if that variable "DestinationFile" would be available to use in the
report heading.

Well....I'm not exactly sure where to put that VB code in order to tie it to
a command button.

Do I create a Module, then run a Macro which references the module...then tie
a command button to that macro?

If so....when it was run.....would a window pop up with an input dialog with
an entry box into which the user would enter the "Destination File" name?

And...if so...could I just somehow reference the value "Destination File" in
the report heading? I've found the "View Field List" in the report designer..
...but I'm not sure how to reference a variable created with just VB code.

Thanks again for this help. While good 'ol DOS is handy....getting this all
to stay in Access will be much cleaner.

-Kev100

Hi,
sure...to store your value you can create a global variable which holds the
value until you clear it or close access.
For the moving renaming part either look into the FileSystem Object, or the
CopyFile Syntax e.g.:

Dim SourceFile, DestinationFile
SourceFile = "c:\test\yourfile.txt" ' Define source file name
DestinationFile = "c:\test01\yourfile_renamed.txt" ' Define target file name
FileCopy SourceFile, DestinationFile ' Copy source to target

If you want to delete the sourcefile then use the kill command e.g.:

Kill SourceFile

HTH
Good luck

I hope this is the right place to ask this question.....

[quoted text clipped - 26 lines]

Thanks

 

[kev100 via AccessMonster.com]

Thanks VERY much....I'm going to edit and try that now.

I did not mention that the actual bat file uses a DOS command with wildcards..
...and will automatically over-write the "to" file if it already exists.


The actual line of DOS is...

xcopy g:\rootfolder\subfolder1\%input%.* /s /y C:\rootfolder\subfolder1\

.....where %input% is the file name (without the extension).

The command will often copy multiple files (due to the * ), if there are ones
with different entensions, and with the name entered.

Are wildcards and "automatic over-writes" allowed in VBA code?

Thanks

Hi,
office applications use VBA (visual basic for applications). There is a
difference between VB and VBA.
Buttens use event procedures to execute VBA code...if you open the property
window of the button (in form design view) and go to the event tab you
probably see [event procedure] in the on click event area. Press the "..."
next to it, this will open the VBA editor and should have an on click sub
formatted for you. This is where you code probably is calling the batch file.
So take that out and replace it with the sample I gave you.
You will need to modify the code a little so it accepts the user input e.g.:

Option Compare Database
Public gbl_UserInput As String
Option Explicit

Dim strInput As String
Dim strMsg As String
Dim DestinationFile As String

strMsg = "Enter the name of the file!"

strInput = InputBox(Prompt:=strMsg, title:="File Name?")
gbl_UserInput = "c:\path to the file\ & strInput & ".doc"

DestinationFile = "c:\newpath\newfilename.doc"
FileCopy gbl_UserInput , DestinationFile

This is it...then you can call 'gbl_UserInput' anywhere in the db e.g. as
the control source of an unbound textbox control on your report!
HTH
Good luck

Thanks very much...

[quoted text clipped - 58 lines]

 

[kev100 via AccessMonster.com]

Oops.....CHECK THAT....


I'm confusing myself here......


That above code is for another situation (I actually have 2 bat files, one
simply copies a file to a new locataion, renaming it in the process....AND
replacing the existing one, if it exists). HERE's the current DOS code for
that...

g:\rootfolder\subfolder1\%input%*.doc /s/y c:\rootfolder\stats.doc

....(where %input% is what the user enters).

In the above, the user enters just a piece of the filename (there may be more,
but it always has the same extension....the wildcard * will get whatever full
name is there...e.g. the user may enter "FileName" and what is actually
copied is "Filename123.doc")

What the file is RENAMED is always the same (stats.doc).

So....ever time it runs...there is an old file with the same name that must
be overwritten.....(thus the /s /y switches)

****************

In SECOND bat file, (the messed up code presented first)....the user enters a
name that is actually the name of the FOLDER that the files are in. The
command will then copy every file in that folder to a NEW location....but NOT
renaming them.

Here's the CORRECT DOS code for THAT one (incorrectly posted in the above)....
..

xcopy g:\rootfolder\subfolder1\%input%\StaticFileName.* /s /y C:\rootfolder\
subfolder1\

.....where %input% is the name the user enters and is actually the name of the
FOLDER that the files reside...the file name with this command does NOT
change in the copy...just the location

But...in BOTH commands...the wildcard * is used and any old existing files
must be automatically overwritten.


Very sorry....I got confused and mixed these 2 conditions into 1 convoluted
one.

Thanks

 

[freakazeud]

Hi,
you can use the wildcard operator with the FileCopy Method I gave you as
well.
HTH
Good luck