FileCopy VBA - Wrong Format

Question

I have this code that copies several files and save them on another location with another name. 

Set fso = VBA.CreateObject("Scripting.FileSystemObject")

For i = 1 To cantfilas Step 1

    Worksheets("Tool").Activate

    nombretemporal = Cells(j + 1, "H").Value

    fso.CopyFile Cells(j + 1, "G"), folder & "" & nombretemporal

    Cells(j + 1, "J").Value = "Done"

    j = j + 1

Next i

The problem is that the files are copied with no format. In the windows explorer they appear with just as "File" format. Any solution to this?

Answer 1

I think then that you must ensure that they retain their file type extension as shown in the original.  I'm not sure why the format should change at all - but if the file type extension is getting changed somehow, that definitely creates a problem.

Ron De Bruin has a lot of code on this page showing various ways both using the FileScriptingObject and 'plain' native VBA functions for copying files:

http://www.rondebruin.nl/win/s3/win026.htm

maybe something there will assist you.

Answer 2

Sorry, I misexplained! The files to-be-copied are in other formats (mostly .xlsb or .xlsm) but .xls . The problem relies in the fact that the new name for each file is on a spreadsheet. The macro only picks the string to be modified, all of them are nameoffile^YYYY-MM-DD.xls regardless the appropiate format of the file that needs its name changed (even some of them are in .zip format).

Answer 3

I can't leave the files with the ".xls" substring because they end up having Office 1997-2003 and that format is not desirable.

And that is the answer to the problem - the copied versions do not offer a clue to the system as to what application to open them with.  Since you are simply copying and renaming them, the file format itself is still going to be Excel 97-2003, so you might as well leave the .xls as part of the file name (or more accurately, add it back to the new filename).

The only way to get them into a later format would be to have your code actually open each one and save the file to the new destination as the proper format type you desire, be it .xlsx, .xlsm, .xlsb or other.

The internal file formats for Excel 97-2003 and later versions are completely incompatible with one another: in 97-2003 everything is actually in a single file; with 2007 and later, what you actually have is a zipped file with several folders within it and files within those folders.  If you want to see this for yourself, take a .xlsx or .xlsm file and change the .xlsx or .xlsm to .zip and then open it in WinZip or 7Zip.  Just change the name back and it'll "become" an Excel 2007-2013 usable file again.

Answer 4

Each j+1, "H" cell has the proper file name, according to each file that has been previously picked. The name for each file has this format: nameoffile^YYYY-MM-DD.xls 

The problem is that, although I delete the ".xls" sub-string, and replace YYYY-MM-DD with the proper date, I can open the files that have been copied in the new directory. 

I can't leave the files with the ".xls" substring because they end up having Office 1997-2003 and that format is not desirable.

Answer 5

What is?

Cells(j + 1, "H").Value

Do you have the correct file extensions?