Shell Error

  • Thread starter mattc66 via AccessMonster.com
  • Start date
M

mattc66 via AccessMonster.com

I am using the below code to create 5 temp text files. Then it will merge all
5 temp text files into one file.
It is failing at the end, the Shell CmdStr. I am getting an error 53. I am
not sure why. Can anyone help?

Private Sub cmdExp_Click()
Const DEST_NAME As String = "w:\files\Export"
Const DEST_EXT As String = ".txt"

Dim TempFolder As String
Dim FinalName As String
Dim CmdStr As String
Dim Temp1 As String, Temp2 As String, Temp3 As String, Temp4 As String,
Temp5 As String

'Build temp file names using user's temp folder
Temp1 = "c:\Files\Table1.txt"
Temp2 = "c:\Files\Table2.txt"
Temp3 = "c:\Files\Table3.txt"
Temp4 = "c:\Files\Table4.txt"
Temp5 = "c:\Files\Table5.txt"

'Export to temp files
DoCmd.TransferText acExportDelim, , "850_OrderRecord", Temp1, no
DoCmd.TransferText acExportDelim, , "850_DetailRecord", Temp2, no
DoCmd.TransferText acExportDelim, , "850_CommentRecord", Temp3, no
DoCmd.TransferText acExportDelim, , "850_CustomerRecord", Temp4, no
DoCmd.TransferText acExportDelim, , "850_AddressRecord", Temp5, no

'Concatenate using Windows COPY command
FinalName = DEST_NAME & Format(Date, "yyyymmdd") & DEST_EXT
CmdStr = "COPY /B /Y """ _
& Temp1 & """ + """ _
& Temp2 & """ + """ _
& Temp3 & """ + """ _
& Temp4 & """ + """ _
& Temp5 & """ " & FinalName
Shell CmdStr

End Sub
 
B

BruceS

Matt,

Try enclosing "FinalName" in quotes in the concatenate. If that doesn't do
it, add the full path for the filename (enclosed in quotes).

Just to be sure that the statement is construced properly, display it in a
message box before you execute it.

Bruce
 
M

mattc66 via AccessMonster.com

I tried that and still get a run time error 53.
Matt,

Try enclosing "FinalName" in quotes in the concatenate. If that doesn't do
it, add the full path for the filename (enclosed in quotes).

Just to be sure that the statement is construced properly, display it in a
message box before you execute it.

Bruce
I am using the below code to create 5 temp text files. Then it will merge all
5 temp text files into one file.
[quoted text clipped - 36 lines]
 
B

BruceS

Matt,
What is the exact text being show by the MsgBox?
Bruce

mattc66 via AccessMonster.com said:
I tried that and still get a run time error 53.
Matt,

Try enclosing "FinalName" in quotes in the concatenate. If that doesn't do
it, add the full path for the filename (enclosed in quotes).

Just to be sure that the statement is construced properly, display it in a
message box before you execute it.

Bruce
I am using the below code to create 5 temp text files. Then it will merge all
5 temp text files into one file.
[quoted text clipped - 36 lines]
 
D

Dirk Goldgar

mattc66 via AccessMonster.com said:
I am using the below code to create 5 temp text files. Then it will
merge all 5 temp text files into one file.
It is failing at the end, the Shell CmdStr. I am getting an error 53.
I am not sure why. Can anyone help?

Private Sub cmdExp_Click()
Const DEST_NAME As String = "w:\files\Export"
Const DEST_EXT As String = ".txt"

Dim TempFolder As String
Dim FinalName As String
Dim CmdStr As String
Dim Temp1 As String, Temp2 As String, Temp3 As String, Temp4 As
String, Temp5 As String

'Build temp file names using user's temp folder
Temp1 = "c:\Files\Table1.txt"
Temp2 = "c:\Files\Table2.txt"
Temp3 = "c:\Files\Table3.txt"
Temp4 = "c:\Files\Table4.txt"
Temp5 = "c:\Files\Table5.txt"

'Export to temp files
DoCmd.TransferText acExportDelim, , "850_OrderRecord", Temp1, no
DoCmd.TransferText acExportDelim, , "850_DetailRecord", Temp2, no
DoCmd.TransferText acExportDelim, , "850_CommentRecord", Temp3, no
DoCmd.TransferText acExportDelim, , "850_CustomerRecord", Temp4, no
DoCmd.TransferText acExportDelim, , "850_AddressRecord", Temp5, no

'Concatenate using Windows COPY command
FinalName = DEST_NAME & Format(Date, "yyyymmdd") & DEST_EXT
CmdStr = "COPY /B /Y """ _
& Temp1 & """ + """ _
& Temp2 & """ + """ _
& Temp3 & """ + """ _
& Temp4 & """ + """ _
& Temp5 & """ " & FinalName
Shell CmdStr

End Sub

Copy is not an independent program, so you have to shell to the command
interpreter "cmd.exe", and have that program interpret the command
string you built. Try this:

Shell "cmd.exe " & CmdStr
 
B

BruceS

Thanks, Dirk. I missed that.
Bruce

Dirk Goldgar said:
Copy is not an independent program, so you have to shell to the command
interpreter "cmd.exe", and have that program interpret the command
string you built. Try this:

Shell "cmd.exe " & CmdStr

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
M

mattc66 via AccessMonster.com

Okay thats getting closer. The CMD window opens, but it doesn't run the
CmdStr.

Dirk said:
I am using the below code to create 5 temp text files. Then it will
merge all 5 temp text files into one file.
[quoted text clipped - 36 lines]

Copy is not an independent program, so you have to shell to the command
interpreter "cmd.exe", and have that program interpret the command
string you built. Try this:

Shell "cmd.exe " & CmdStr
 
D

Dirk Goldgar

mattc66 via AccessMonster.com said:
Okay thats getting closer. The CMD window opens, but it doesn't run
the CmdStr.

Dirk said:
I am using the below code to create 5 temp text files. Then it will
merge all 5 temp text files into one file.
[quoted text clipped - 36 lines]

Copy is not an independent program, so you have to shell to the
command interpreter "cmd.exe", and have that program interpret the
command string you built. Try this:

Shell "cmd.exe " & CmdStr

Oh, drat. I left out the /C flag:

Shell "cmd.exe /C " & CmdStr
 
M

mattc66 via AccessMonster.com

The Cmd windows flashes, but the CmdStr still doesn't happen.

Dirk said:
Okay thats getting closer. The CMD window opens, but it doesn't run
the CmdStr.
[quoted text clipped - 10 lines]
Oh, drat. I left out the /C flag:

Shell "cmd.exe /C " & CmdStr
 
D

Dirk Goldgar

mattc66 via AccessMonster.com said:
The Cmd windows flashes, but the CmdStr still doesn't happen.

You're sure? I guess the command string must need some more quotes
embedded in it somewhere. Could you please post the value of CmdStr?
 
D

Dirk Goldgar

Dirk Goldgar said:
You're sure? I guess the command string must need some more quotes
embedded in it somewhere. Could you please post the value of CmdStr?

Note, I'm going offline for the day now, but maybe someone else will see
the problem before I get back.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top