How do I save an Excel Binary file as a .xlsx file in VBA ?

Hi,

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
    Next

Open in new window


Thanks
Toco
TocogroupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KimputerCommented:
I think the code doesn't contain any errors?

What you want though, is not possible. It's a safety feature. You want to "hide" VBA code in a "normal" excel file .xlsx (which was possible in the old scheme, have VBA code in a "normal" .xls file). As a safety precaution, IF you want an excel file to contain code, you MUST save it as .xlsm or .xlsb (or alternatively, use the old file format and save it as .xls).
0
TocogroupAuthor Commented:
Hi Kimputer,

The code doesn't contain errors because I replaced my erroneous code with comments.

I'm sorry but I think I've confused you. Let me explain my requirements in another way.

1. Copy the data file (.xlsb) in the Source folder to the destination Archive folder as a .xlsb file
2. Copy the same data file in the Source folder to the destination Archive folder as a .xlsx file
(In other words, copy the input file in its original .xlsb format and also as a .xlsx workbook)
3. Copy the first sheet of the data file in the Source folder to a workbook called 'Output.xlsx' (this workbook already exists and is open).
4. Delete the original .xlsb data file from the Source folder.

Hope that's clearer
0
KimputerCommented:
Sorry, still confused! Because your previous code didn't care about the files, it copied all existing files from one location to another.
But now your requirements seems to be focused on just one file?
Also, point 3 is not so clear. The first sheet of data from the source, is always called Sheet1? How should the sheet name be in Output.xlsx? Is it adding a sheet, or replacing it?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

TocogroupAuthor Commented:
Hah sorry. It's my poor explanation. I'm confusing myself here.

Yes, you are right. There will only ever be one file in the source folder. This file is in .xlsb format. I want to save it as a backup to my Archive folder. I also want to save it in .xlsx format to the Archive folder.

I then want to copy the first sheet of the source file (it may be called anything, not necessarily Sheet1) to Output.xlsx with a sheet name of 'Data'. It will be adding the sheet to the Output workbook (in no particular position in the workbook).
0
KimputerCommented:
So to be sure, the file in the source folder, has a fixed name? Or could be any file? If so, what happens if by accident there are two files in the source folder?
So you know saving the .xlsb to .xlsx will lose some VBA data in the process, and you're okay with that?
Adding data to the data sheet of output.xlsx, means, if the source has 5 rows, and the output.xlsx data sheet already has 1000 row, the 5 rows should be added under the 1000?
0
TocogroupAuthor Commented:
The file in the source folder will have a name which begins "report" followed by a number of digits (there is no fixed number of digits). For example, "report1640932667" or "report333"

There will only ever be one (or none) file in the source folder. You'll have to trust me on this one. And when I've backed it up to the Archive folder and copied the data sheet over to the Output workbook, I then delete it.

No I didn't know saving a .xlsb workbook as a .xlsx workbook results in lost data ? From where ? From the actual sheets ? Why does this happen ?

I want to copy the whole sheet from the source file to the output file, not the individual rows. This is why I've referred to the sheet, rather than the rows. Anyway, this "Data" sheet does not exist on the output file, hence why I will be copying the sheet and not adding rows as you suggested.

thanks
0
KimputerCommented:
.xlsb to .xlsx only loses VBA code. If you DON'T have VBA code in the original .xlsb, then there's nothing to worry about.
0
TocogroupAuthor Commented:
Ok...that's fine then. And how do I save a .xlsb file as a .xlsx file ?
0
KimputerCommented:
Sub test()

Dim wb1 As Workbook
Dim wb2 As Workbook
    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
 
       Set wb1 = Workbooks.Open(strSourceFolder & "\" & f.Name)
        Set wb2 = Workbooks.Open(strDestinationFolder & "\Output.xlsx")
        Application.DisplayAlerts = False
        ' The code for saving the file with a .xlsx extension should go here
        wb1.SaveAs Filename:=strDestinationFolder & "\" & Replace(f.Name, ".xlsb", ".xlsx"), CreateBackup:=False, FileFormat:=xlOpenXMLWorkbook
        ' The code for saving the first sheet in a workbook called 'Output.xlsx' should go here

        wb1.Sheets(1).Copy Before:=wb2.Sheets(1)
        wb2.Sheets(1).Name = "DATA"
        wb2.SaveAs Filename:=strDestinationFolder & "\Output.xlsx", CreateBackup:=False, FileFormat:=xlOpenXMLWorkbook
        wb1.Close (False)
        wb2.Close (False)
        Application.DisplayAlerts = True
        fs.DeleteFile strSourceFolder & "\" & f.Name
    Next
    
    
End Sub

Open in new window


Actually works better if you don't have Output.xlsx open (because of opening and closing action)! Also assumes there's no DATA sheet.
0
TocogroupAuthor Commented:
I'm getting a 'Run-time error 1004: Application-defined or object-defined error" on the 'Set wb1...' statement.
0
KimputerCommented:
I only get the error when Output.xlsx already has a "DATA" sheet, which you said it wouldn't. Can you double check Output for a DATA sheet?
0
TocogroupAuthor Commented:
Well it's falling over on line 14, which is before it tries to copy the sheet called "Data".
0
TocogroupAuthor Commented:
By that I mean, the problem precedes the "Data" sheet copy so therefore, it can't be that statement that is the problem.
0
KimputerCommented:
Try the code after deleting:

Dim wb1 As Workbook
Dim wb2 As Workbook

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TocogroupAuthor Commented:
Excellent.  We got there eventually. Thanks for your help.
I'm submitting your points award via IE 8 browser. Please let me know if there are issues.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.