_Excel_SheetCopyMove

[CiaronJohn]

Hi,

I would like to seek for your assistance regarding function "_Excel_SheetCopyMove", I read necessary files, other related topics in forum, and yet I cannot perform the function well. I expect that the selected sheet from Workbook1 should be moved to Workbook2, but nothing happens. Please see my code below, any assistance would be very much appreciated. Thanks.

expandcollapse popup

Func _BE_jnknsSeparateSheet(  )

    ; Local Variables
    Local   $sLogTextFile = @ScriptDir & "\..\Log.txt", _
                $sTestSheetFile, _
                $sDrive, _
                $sDir, _
                $sFileName, _
                $sExtension
    Local    $aFilePath
;~                 $aTestDesign
    Local    $fOldSheet, _
                $fNewSheet
    Local   $hTextFile
    Local    $oExcel, _
                $oBook, _
                $oSheet, _
                $oBookName

    ; Open log text file
    $hTextFile = FileOpen($sLogTextFile, $FO_READ)
    ; Open testsheet
    $sTestSheetFile = FileReadLine($hTextFile,2)
    $sTestSheetFile = StringTrimLeft($sTestSheetFile,20)
    Local $aTestDesign[ ] = [ $sTestSheetFile ]

    $oExcel = _Excel_Open()
    $oBook = _Excel_BookNew( $oExcel, 1 )

    For $i = 0 To UBound ( $aTestDesign, 1 ) - 1
        _PathSplit($aTestDesign[$i], $sDrive, $sDir, $sFileName, $sExtension)
    Next

    MsgBox(0, "", $sDrive & $sDir )

    $oBookName = $sDrive & $sDir & "testFile.xlsx"
    _Excel_BookSaveAs( $oBook, $oBookName, $xlWorkbookDefault, True )
    $sTestSheetFile = _Excel_BookOpen ( $oExcel, $sDrive & $sDir & $sFileName & $sExtension )
    $oBookName = _Excel_BookOpen ( $oExcel, True )
    _Excel_SheetCopyMove ( $sTestSheetFile, "1", $oBookName, False )
    _Excel_BookClose ( $sTestSheetFile )
    _Excel_BookClose ( $oBookName )

EndFunc

 

[CiaronJohn]

5 minutes ago, CiaronJohn said:

_Excel_SheetCopyMove ( $sTestSheetFile, "1", $oBookName, False )

Sorry this should be

_Excel_SheetCopyMove ( $sTestSheetFile, 1, $oBookName, False ). Nevertheless, output is still unsuccessful.

[CiaronJohn]

Disregard this, we found what caused the unsuccessful event. Thanks

[water]

Could you please describe the problem and - if not obvious - the solution?
So if someone else in the future hits the same problem he can easily find your post and save some time with the provided solution.

[CiaronJohn]

The problem I encountered was the new excel instance I created and used to be the container of sheet to be moved is not an object which is required by the function _Excel_SheetCopyMove.

I  just changed my snippet from

$oBookName = _Excel_BookOpen ( $oExcel, True )

to

$oBookName = _Excel_BookOpen ( $oExcel, oBookName )

Thank you @water for your UDF btw.

[water]

[CiaronJohn]

@water in relation with excel, is there any way to rename the whole workbook using autoit?

[Nine]

4 hours ago, CiaronJohn said:

rename the whole workbook

What do you mean ?  Change the file name ?  Rename all the sheets ?

[seadoggie01]

11 hours ago, CiaronJohn said:

rename the whole workbook

_Excel_BookSaveAs

[CiaronJohn]

4 hours ago, seadoggie01 said:

Excel_BookSaveAs

Haven't thought of that I'm sorry. Thank you @water

[seadoggie01]

3 hours ago, CiaronJohn said:

I'm sorry

No need to be sorry, we all miss things... Speaking of, have you seen my sanity?