I have a procedure which checks a folder for any existing file and if successful copies the file to a backup destination folder. The file is then deleted from the source folder. The file will always be in Excel Binary format (.xlsb).
However, I now also want to save the file as an Excel workbook (.xlsx) and copy the first sheet to another workbook (Output.xlsx). I thought this would be straightforward using a Set statement, but I'm getting runtime errors.
My code is as follows.
Set fs = CreateObject("Scripting.FileSystemObject")
strSourceFolder = "Z:\SourceDocs\Status Report\Data\Input"
Set r = fs.GetFolder(strSourceFolder)
strDestinationFolder = "Z:\SourceDocs\Status Report\Data\Archive\Input"
For Each f In r.Files
fs.CopyFile strSourceFolder & "\" & f.Name, strDestinationFolder & "\" & f.Name
' The code for saving the file with a .xlsx extension should go here
' The code for saving the first sheet in a workbook called 'Output.xlsx' should go here
fs.DeleteFile strSourceFolder & "\" & f.Name