Shell Error

[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

 

[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

 

[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]

End Sub

 

[BruceS]

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

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]

End Sub

 

[Dirk Goldgar]

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

 

[BruceS]

Thanks, Dirk. I missed that.
Bruce

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)

 

[mattc66 via AccessMonster.com]

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

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]

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

 

[Dirk Goldgar]

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

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]

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

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

Shell "cmd.exe /C " & CmdStr

 

[mattc66 via AccessMonster.com]

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

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

 

[Dirk Goldgar]

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?

 

[Dirk Goldgar]

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.