Description: Copies an entire worksheet from one Excel file to another, or the same Excel file. This includes values, formulas, formatting etc.
Parameters:
CopySheet(sPathOriginal As String, sSheetOriginal As String, sPathTarget As String, sSheetTarget As String, sSheetBefore As String, sSheetAfter As String, lActionIfAlreadyExists As Long) As Long
|
Parameter |
Meaning |
|
sPathOriginal |
Excel file from which to copy the sheet. |
|
sSheetOriginal |
Sheet Name or number to copy. |
|
sPathTarget |
Excel file to which data is copied. |
|
sSheetTarget |
Renames the copied sheet to string specified in sSheetTarget. This is only available when a single sheet is being copied. |
|
sSheetBefore |
Copied sheet will be placed before this sheet (Name or #). If sSheetBefore = "firstfirst" then it is placed as the first sheet in the workbook. |
|
sSheetAfter |
Copied sheet will be placed after this sheet (Name or #). If sSheetAfter = "lastlast" then it is placed as the last sheet. |
|
lActionIfAlreadyExists |
If sheet name being copied already exists in the target workbook, 1 of 4 actions will be done: 0 = Copy sheet and give similar name as original sheet. 1 = Overwrite duplicates 2 = Skip duplicates 3 = Stop Copying and report an error |
Notes: sPathOriginal AND sPathTarget can be the same Excel file, in which case the positioning information (sSheetBefore, sSheetAfter) is used. Otherwise, a new workbook file is created, and saved as sPathTarget. If sPathOriginal and sPathTarget are different, all positioning information is ignored, since it will be the only sheet in the new workbook. If both sSheetBefore and sSheetAfter are specified, sSheetAfter is used.. When a single sheet is being copied, a new name may be specified in sSheetTarget for the new sheet. This is not available when copying multiple sheets.
Return Values:
0: Success
-3: Unable to create Excel Application. Is it installed?
-4: Unable to destroy Excel Application.
-200: Excel reported an error
-201: Shareware Expired
-202: Source File does not exist
-203: Target File does not exist
-204: Source Sheet does not exist
-208: Target Path folder does not exist, even after attempting to create it.
-210: SheetBefore or SheetAfter does not exist
-211: "SheetBefore AND SheetAfter have not been specified. Use different target file to create a new workbook with worksheet copy."
-212: "Target Sheet already exists. Set overwrite to TRUE to copy over existing sheet."
Example Code:
Taken from the sample VB program, the component is passed the necessary arguments to copy a worksheet.
frmXLSCopySheet.Setup 'calls an ancillary form to 'collect data
If (frmXLSCopySheet.bErrorOccurred = True) Then
Exit Sub
End If
'assign data from form to variables
strSheetOriginal = frmXLSCopySheet.sOriginalSheet
strSheetTarget = frmXLSCopySheet.sTargetSheet
strSheetBefore = frmXLSCopySheet.sSheetBefore
strSheetAfter = frmXLSCopySheet.sSheetAfter
'call the component with the necessary arguments
lngResult = XLSConv1.CopySheet(strSourceFile,
strSheetOriginal, strTargetFile,
strSheetTarget, strSheetBefore, strSheetAfter,
lOverwrite)
Unload frmXLSCopySheet