Passing data from a table thru to Dos

[meagain]

Hi,
I want to rename an excel file to the contents of of a data field via batch file. Is it possible to pass the field data from access thru to dos during the rename?

Thanks,
Lori

 

[Andrzejek]

What DOS commend are you trying to run? And what field would you like to use?
A code example would be nice...


---- Andy

There is a great need for a sarcasm font.

 

[meagain]

Hi Andy,

The specific dos command in the batch file would be:

rename path\md.xlsx "%curdate%-md-ZZ9.xlsx"

where ZZ9 would be the data from access
table = ClaimNumber
field = Order#

The Order# is a 20 character text field.

The batch file would be executed via the following Event Procedure:

Private Sub Form_Open(Cancel As Integer)
RetVal = Shell("J:\Blah\Blah\RenameClaim.bat", 1)

End Sub

To summarize, I'm looking to populate the ZZ9 from the Order# field, within the Access Claim Number table into the dos renamed file.

Thank you!
Lori

 

[Andrzejek]

So you have a simple text file [tt]RenameClaim.bat[/tt] where you want to replace something with something else from your Access data base, right?

If that's the case, could you show the [tt]RenameClaim.bat[/tt] file and what you want to replace in it?

BTW - you do know that you can rename files straight in VBA code, no DOS / *.bat files required.


---- Andy

There is a great need for a sarcasm font.

 

[meagain]

Hi Andy,

No I wasn’t aware. Please do share.

As indicated above, I’m looking to replace the ZZ9 portion of the new file name in the first green line above, with the up to 20 character Order# field data in the ClaimNumber table from an access database that will be open at the time.

If I can do it without running a batch file terrific.

Thank you.

 

[Andrzejek]

You can either do this or try this code:

(Assuming you have some txt files in C:\TEMP\ directory that you want to rename as 1.txt, 2.txt, 3.txt, etc.)

Dim strPath As String
Dim strFile As String
Dim intX As Integer

strPath = "C:\TEMP\"

strFile = Dir(strPath & "*.txt")

Do While strFile <> ""
    intX = intX + 1
    Name strPath & strFile As strPath & intX & ".txt"
    strFile = Dir
Loop

Instead of the integer as a new portion of your file name, you can get the data from your Access table via DLookup function

You can also investigate FileSystemObject capabilities.


---- Andy

There is a great need for a sarcasm font.

 

[meagain]

Thank you Andy. I'll try them out over the next week or so.