Copy two text files to a third text file in VBA 1

[nodrog77]

Hi All,

I have two fixed width text files which I have created from an excel workbook and I'm trying to append one to another and output the result to a third text file. I've been using the shell to do it as it seemed to be the simplest way. The following works fine:

OutputFolderName = "C:\"
ShellStr = "command.com /c Copy " & OutputFolderName & "TypeH.txt + " & OutputFolderName & "Type1.txt " & OutputFolderName & "NewType1.txt"
Shell ShellStr, vbHide


The shell command works out as: command.com /c Copy C:\TypeH.txt + C:\Type1.txt C:\NewType1.txt

This works perfectly fine, the problem occurs when I try to change the variable OutputFolderName to a networked drive,
eg G:\VG\State Tax\Dev\XX01.12.SP\ or even G:\VG
I have included extra quotes around the directory names, eg.

Qu = Chr(34) 'Double Quotes
OutputFolderName = “G:\VG\State Tax\Dev\XX01.12.SP\”
ShellStr = "command.com /c Copy " & Qu & OutputFolderName & Qu & "TypeH.txt + " & Qu & OutputFolderName & Qu & "Type1.txt " & _
Qu & OutputFolderName & Qu & "NewType1.txt"
Shell ShellStr, vbHide

- the copy commands work fine when I test them using a dos prompt but I can’t get them to work in VBA – I get the error “Invalid procedure call or argument (Error 5)

I’m using excel 2003 and Windows XP.

Regards,

Lea.

 

[PHV]

What about this ?

OutputFolderName = "G:\VG\State Tax\Dev\XX01.12.SP\"
ShellStr = "command.com /c  Copy """ & OutputFolderName & "TypeH.txt"" + """ & OutputFolderName & "Type1.txt"" """ & _
       OutputFolderName & "NewType1.txt"""
Shell ShellStr, vbHide

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nodrog77]

Thanks PHV but your solution isn't working either - maybe it's something to do with dos/network drives. I'm stumped. I may have to work out another way to stick the files together.

 

[mintjulep]

Dos is not happy with long path and file names.

Get the short names.

http://www.vbusers.com/code/codeget.asp?ThreadID=633&PostID=1

 

[nodrog77]

Got the short path name working but still no luck - very good idea though. MintJulep

 

[mintjulep]

Maybe try

http://bytes.com/topic/visual-basic/answers/693766-vb-shell

 

[Andrzejek]

PHV's sollution gives you:
[tt]
command.com /c Copy "G:\VG\State Tax\Dev\XX01.12.SP\TypeH.txt" + "G:\VG\State Tax\Dev\XX01.12.SP\Type1.txt" "G:\VG\State Tax\Dev\XX01.12.SP\NewType1.txt"
[/tt]
and you say it does not work.
Do you know why? Do you need all those " (double quotes)?



Have fun.

---- Andy

 

[PHV]

your solution isn't working
What happens ?
Any error message, computer crash, unexpected behaviour, ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nodrog77]

PHV,

the copy commands work fine when I test them by copying the string from the watch window and into a dos prompt but I can't get them to work in VBA – I get the error "Invalid procedure call or argument (Error 5).

Andrzejek, I get the same error with or without the double quotes.

Lea.

 

[macropod]

the problem occurs when I try to change the variable OutputFolderName to a networked drive

Have you tried:
• a Dir test on that drive to see if you can access it?
• changing to that drive and doing the copying there, without including the path in the copy code?

Cheers
Paul Edstein
[MS MVP - Word]

 

[nodrog77]

I worked it out!!!

I'm not sure exactly why but I had a look at the Wikipedia articles for "command.com" and it referred me to "cmd.exe" as being possibly a better alternative.

This solution works whether I use a shortened folder name as suggested by MintJulep above or enclose the folder name in quotes, so the solution is:

Qu = Chr(34) 'Double Quotes
OutputFolderName = "G:\VG\State Tax\Dev\XX01.12.SP\"
ShellStr = "cmd.exe /c Copy " & Qu & OutputFolderName & Qu & "TypeH.txt + " & Qu & OutputFolderName & Qu & "Type1.txt " & _
Qu & OutputFolderName & Qu & "NewType1.txt"
Shell ShellStr, vbHide

OR

ShortName = GetShortPath(OutputFolderName)
OutputFolderName = "G:\VG\State Tax\Dev\XX01.12.SP\"
ShellStr = "cmd.exe /c Copy " & ShortName & "TypeH.txt + " & ShortName & "Type1.txt " & _
ShortName & "NewType1.txt"
Shell ShellStr, vbHide

for the code for GetShortPath see MintJulep's post above.
The Wikipedia article is here:

http://en.wikipedia.org/wiki/Command_Prompt

as I think what works may depend on the operating system (I'm using windows XP)

Thanks for all of your help,

Lea.