Solved

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

Posted on 2014-09-15
15
866 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now