Solved

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

Posted on 2014-09-15
15
915 Views
Last Modified: 2014-09-17
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
0
Comment
Question by:Tocogroup
  • 8
  • 7
15 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 40322785
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
 

Author Comment

by:Tocogroup
ID: 40322824
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
 
LVL 35

Expert Comment

by:Kimputer
ID: 40322935
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:Tocogroup
ID: 40322975
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
 
LVL 35

Expert Comment

by:Kimputer
ID: 40323050
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
 

Author Comment

by:Tocogroup
ID: 40323103
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
 
LVL 35

Expert Comment

by:Kimputer
ID: 40324840
.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
 

Author Comment

by:Tocogroup
ID: 40325051
Ok...that's fine then. And how do I save a .xlsb file as a .xlsx file ?
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40325173
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
 

Author Comment

by:Tocogroup
ID: 40325356
I'm getting a 'Run-time error 1004: Application-defined or object-defined error" on the 'Set wb1...' statement.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 40325459
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
 

Author Comment

by:Tocogroup
ID: 40325476
Well it's falling over on line 14, which is before it tries to copy the sheet called "Data".
0
 

Author Comment

by:Tocogroup
ID: 40325517
By that I mean, the problem precedes the "Data" sheet copy so therefore, it can't be that statement that is the problem.
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 40325670
Try the code after deleting:

Dim wb1 As Workbook
Dim wb2 As Workbook

Open in new window

0
 

Author Closing Comment

by:Tocogroup
ID: 40327491
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question