What is VBA syntax for "Write these files to CD"

[bk]

I've searched and can't find a thread that has the answer for this.

I'm wanting to automate the process of moving some files off of a hard drive and burn them onto a CD (actually a DVD, but I'm assuming it's the same syntax).

Can anybody tell me? Surely, this is possible.

 

[tusharm]

Search XL VBA help for 'copy' (w/o the quotes). One of the suggested topics will be the FileCopy Statement (Visual Basic for Applications). Another will be the CopyFile method (Visual Basic for Applications).

 

[bk]

Thanks

I'm not having any luck, though.

Since CDs are 'unique' and it's a 2-step process to get them burned to a CD, I can only get them into the buffer state before one clicks "Write these files to CD".

Although I'm not very familiar with these commands, I have used MoveFiles before and everything worked fine. This caused me to have to figure out FileSystemObject, checking the box by Scripting under Tools\References , etc.

Of the commands you mentioned, although I tried them all, I decided CopyFile was the best shot. While looking at these, I found CopyFolder also and am trying it as well.

It's not working. One thing that may help, if somebody can guide me: CopyFile and CopyFolder are not automatically capitalizing themselves like VBA commands have always done for me. I typed in all lower-case and that's how the Editor left them. Not sure how important this is, but it's never happened before and causes me to feel that I don't have something write from the get-go. And thus, can't expect it to work.

I'll be greatly appreciative of any help on getting this to work. TIA.

 

[tusharm]

Re: Thanks

If the CD is write-enabled and visible as a drive you should be able to just drag-and-copy files to it with none of that 'write these files' mumbo jumbo.

As far as CopyFile goes it's a method of the FileSystemObject class. You have to have a reference to the Microsoft Scripting Runtime (in the VBE Tools | References...) So, you would have something like

Dim x as filesystemobject
x.copyfile
and that should become
x.CopyFile

unless, of course, there's a way to disable Intellisense in the VBE (and it has been disabled).

 

[bk]

Yeah,

I don't think Intellisense is disabled b/c it is working normally everywhere other than when typing these commands

I'm been also attempting this from one part of my hard drive to another to make sure I understand all the syntax. No dice.

I have the Scripting Reference checkbox checked.

I'm using CD-Rs in a CD-RW drive.

I've tested that I can do this manually with the drag/drop.

I'm looking for the syntax because I need to automate this process. It's part of a much larger project, that I've pretty much knocked out except for this.

I've automated for files to be saved to the hard drive of the machine during the normal work during the workday. These are critical files and need to be saved in case of a crash or whatever on the PC. Placing these on a network drive is not an option and it's unlikely we can go the jump drive route. A CD (actually, a DVD, but I phrased my Q in terms of CD, assuming command-wise, they'd be the same) is the most viable option for this back-up location for our operation. I plan to have this intiate at midnight or something when the machine is idle.

Unfortunately, putting files onto a CD isn't as straight-forward and simple as it is putting these files onto a hard drive, network folder, etc. Thus, the need for the syntax. I can get the files onto the CD, but only in that buffer state, not actually written/burnt to the CD. It's maddening; surely, somebody else has had the need to do something like this. I'm not the leader on this project and we're married to using a CD (DVD). If I can't automate this, then I guess I'll force messages on the user to just pester the hell out of them until they do a manual burn. But so many things similar to this and more difficult than this are possible and since CDs are not new, surely this is doable.

I really appreciate the/any help. Thanks.