Solved

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

Posted on 2014-09-15
15
819 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 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

14 Experts available now in Live!

Get 1:1 Help Now