Sign in to follow this  
Followers 0
the123punch

Excel UDF - Saving Worksheets separately

10 posts in this topic

Hi all,

I am using the Excel UDF to write a report to an Excel document.

My report consists of several sheets (~15 sheets).

I am able to generate the report programatically without any problem, and save the entire workbook as well. However, I want to be able to save each worksheet separately. Is that possible with the UDF?

I tried to manipulate code from the Excel UDF but I couldn't get with any result and kept getting errors.

Ideally, I would like to loop through the Workbook object and save each worksheet individually as an Excel Workbook document. Each file would be named according to the worksheet name.

Thanks.

the123punch

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Check into.

_ExcelReadSheetToArray
;Create New Book
_ExcelWriteSheetFromArray
;Save As using
_ExcelSheetNameSet
;As the name of the new book

Uh got bored and did it for you note that all excel windows are not visable to change that make them 1

#include <Excel.au3>
$path = @ScriptDir & '\checking.xls'
Local $oExcel = _Excelbookopen($path,0) ;Create new book, make it not visible
; 1 If visible
$aArray = _ExcelSheetList($oExcel)

For $i = 1 to $aArray[0] Step 1
    _ExcelSheetActivate($oExcel, $i) ;Using the Index of the Array
    
    ;-
    $Name = _ExcelSheetNameGet($oExcel)
    $RedAry = _ExcelReadSheetToArray($oExcel)

    $nBook = _ExcelBookNew(0);- 1 or Blank If visible
    _ExcelWriteSheetFromArray($nBook,$RedAry)
    _ExcelBookSaveAs($nBook,@ScriptDir & '\'& $Name & '.xls','xls',0,1)
    _ExcelBookClose($nBook)
Next

_ExcelBookClose($oExcel) ; And finally we close out
Edited by lordicast

[Cheeky]Comment[/Cheeky]

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Hi lordicast,

I just tried your code and it works like a charm! Thank you.

However, the only thing is that it doesn't keep the formatting. The sheet is copied with no formatting whatsoever. Do you know any way where we can keep the formatting including fonts, borders, and cell height-width?

Thanks.

the123punch

Edited by the123punch

Share this post


Link to post
Share on other sites

Hi again,

I tried doing it on my own and I did the following inside the looping through worksheets of the Excel document:

_ExcelSheetActivate($oExcel, $i) ;Using the Index of the Array

$Name = _ExcelSheetNameGet($oExcel)
_ExcelCopy($oExcel, 1, 1, 100, 20)

$nBook = _ExcelBookNew(0);- 1 or Blank If visible
_ExcelSheetActivate($nBook, 1) ;Using the Index of the Array
_ExcelPaste($nBook, 1)

This seems that it should work and keep the formatting as it is a usual copy/paste of the Excel cells.

However, I keep getting the following error message:

"Unable to get the Paste property of the Worksheet class"

Anyone knows how to solve this?

Thanks.

the123punch

Share this post


Link to post
Share on other sites

Ideally, I would like to loop through the Workbook object and save each worksheet individually as an Excel Workbook document. Each file would be named according to the worksheet name.

I use this with Excel 2003 (formatting is preserved)
Local $fReadOnly = True
Local $fVisible = False ; True  ; show excel

Global $oExcel = ObjCreate("Excel.Application")
If @error Then
    MsgBox(262144 + 16, @ScriptName, "MS Office may not be installed")
    Exit
EndIf

With $oExcel
    Local $sFileFullPath = FileOpenDialog("Select Excel Workbook", "", "xls (*.xls)", 3)
    If $sFileFullPath <> "" Then .Workbooks.Open($sFileFullPath, False, $fReadOnly)
    If @error Then Exit
    Local $sFileName = StringRegExpReplace($sFileFullPath, '[\w:]+\\', '')  ; remove path to get just file name
    Local $aSheets[.ActiveWorkbook.Sheets.Count +1]
    $aSheets[0] = .ActiveWorkbook.Sheets.Count
    For $i = 1 To $aSheets[0]
        $aSheets[$i] = .ActiveWorkbook.Sheets($i).Name
    Next
    .Visible = $fVisible
    .Application.DisplayAlerts = False
    For $i = 1 To $aSheets[0]
        .WorkBooks.Add
        ; insert sheet($i) from workbook $sFileName before sheet1 of newly created workbook
        .Application.Workbooks($sFileName).Sheets($i).Copy(.Application.ActiveWorkbook.Sheets(1), Default)
        For $j = 1 To 3
            .WorkSheets("Sheet" & $j).Delete
        Next
        .ActiveWorkBook.SaveAs (@ScriptDir & "\" & $aSheets[$i])
        .ActiveWorkbook.Close
    Next
    .Quit
EndWith

Exit

Share this post


Link to post
Share on other sites

Thanks picaxe! This works great!

The only and last thing that does bother me with that, is that if the file exists, it will keep asking me whether I want to overwrite it.

Since this is a script that I am developing to run automatically, I don't want to have to click on "Yes" to overwrite the file everytime.

Is there a flag that I can put to overwrite the file when saving it in the line that says:

.ActiveWorkBook.SaveAs (@ScriptDir & "\" & $aSheets[$i])

??

Many thanks!

the123punch

Share this post


Link to post
Share on other sites

Yes. Try this.

oExcel.Application.DisplayAlerts = False
$oExcel.Application.ActiveWorkBook.SaveAs(...)

Share this post


Link to post
Share on other sites

The only and last thing that does bother me with that, is that if the file exists, it will keep asking me whether I want to overwrite it.

The code I posted already does that, see the line
.Application.DisplayAlerts = False

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Based on the MSDN Excel reference, I think you can add an additional parameter to the SaveAs call to surpress the warning for the SaveAs function. The ConflictResolution parameter should probably be set to xlLocalSessionchange, which is the value 2.

Again, I did not test this myself but it might work using:

.ActiveWorkBook.SaveAs (@ScriptDir & "\" & $aSheets[$i], ".xls", "", "", False, False, 1, 2)

This is assuming the ConflictResolution does what I think it does.

~ edit

I took a look in the Excel UDF which already contains the function SaveAs with a parameter $fOverWrite. They simply check if the file already exists and, if $fOverWrite is True, delete the file before saving. So I think conflictresolution might be something different and you should simply do the same:

If FileExists($sFilePath) Then FileDelete($sFilePath)
Edited by d4ni

Share this post


Link to post
Share on other sites

Thank you both.

I tried both and they work just fine!

the123punch

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0