Excel UDF - Saving Worksheets separately

[the123punch]

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

[lordicast]

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 February 16, 2010 by lordicast

[the123punch]

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 February 18, 2010 by the123punch

[the123punch]

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

[picaxe]

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

[the123punch]

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

[Juvigy]

Yes. Try this.

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

[picaxe]

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

[dani]

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 February 20, 2010 by d4ni

[the123punch]

Thank you both.

I tried both and they work just fine!

the123punch