?
Solved

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

Posted on 2014-09-15
15
Medium Priority
?
1,180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 36

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 36

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 36

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 36

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 36

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 36

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 36

Accepted Solution

by:
Kimputer earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

801 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