Description: Copies data (values or formulas) you specify from a worksheet to the same/different worksheet. The data can be also be copied to the same or different Excel workbook file.
Parameters:
CopySheetData(sPathOriginal As String, sSheetOriginal As String, sRangeToCopyFrom As String, sPathTarget As String, sSheetTarget As String, sRangeToCopyTo As String, bDoFormula As Boolean, bCopyNameOfSheet As Boolean, bAddSheetsIfNecessary As Boolean) As Long
|
Parameter |
Meaning |
|
sPathOriginal |
Excel file which data is copied FROM. |
|
sSheetOriginal |
Sheet Name or number FROM which data is copied. |
|
sRangeToCopyFrom |
Range of cells FROM which to copy data. If empty, it copies the range of used cells. |
|
sPathTarget |
Excel file TO which data is copied. If folder does not exist, XLSConverterX will attempt to create it. NOTE: sPathOriginal and sPathTarget can be the same or different Excel file. |
|
sSheetTarget |
Sheet Name or number TO which data is copied. |
|
sRangeToCopyTo |
Range of cells from which to copy data TO. Typical Excel syntax is acceptable. For example "A1:B10", "J5:K20", etc. If this parameter is empty, it will use the range specified by: 1) sRangeToCopyFrom only if sRangeToCopyFrom is not empty otherwise 2) it will use the same used range of cells as found in sSheetOriginal. If this range does not have the same dimensions as the sRangeToCopyFrom data may be lost in the copy operation. If set to "Below", the copied data is placed below the currently used range. If set to "Right", the copied data is placed to the right of the currently used range. HINT: "Below" and "Right" can be used to concatenate/append multiple sheets of data into one. Specifying a single cell (i.e. "B10") will cause the data to be placed beginning at that cell. If you specify a smaller range than the copied range, the data will be truncated. Specifying a larger range than the copied range causes invalid data to be copied to the target sheet. |
|
bDoFormula |
If TRUE, CopySheetData() will copy the formula vs. value of each cell. |
|
bCopyNameOfSheet |
Typically only used if the sSheetOriginal specifies a sheet number (vs. Name). The target sheet name will be the same as the original. |
|
bAddSheetsIfNecessary |
If sheets must be added to accommodate the sSheetTarget request, they will be if this parameter is set to TRUE. Otherwise, this function returns –205. |
Notes: sPathOriginal combined with sSheetOriginal and sRangeToCopyFrom are used to specify what data to copy. sPathTarget combined with sSheetTarget and sRangeToCopyTo specify the where to copy to.
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: Original File does not exist
-203: Target File does not exist
-204: Original Sheet does not exist
-205: Target Sheet does not exist, be sure the 'Add sheets if necessary option' is set to True.
-206: Target Workbook cannot have two similarly named sheets. Original sheet name already exists in Target.
-207: Target Path file cannot have a wild card (i.e. *.XLS)
-208: Target Path folder does not exist, even after attempting to create it.
-200: Excel reported an error
Example Code:
Taken from the sample VB program, this code snippet demonstrates a call to the component to copy specific data from a spreadsheet after collecting the necessary data and passing it to the component.
Case SP_XLS_COPY_SHEET_DATA
frmCopySheetData.Setup 'calls an ancillary form to collect data
If (frmCopySheetData.bErrorOccurred = True) Then
Exit Sub
End If
'assign data from the form to variables
strSheetOriginal = frmCopySheetData.sOriginalSheet
strRangeToCopyFrom = frmCopySheetData.sSpecifiedRangeToCopyFrom
strSheetTarget = frmCopySheetData.sTargetSheet
strRangeToCopyTo = frmCopySheetData.sSpecifiedRangeToCopyFromTo
blnDoFormula = frmCopySheetData.bCopyFormula
blnCopyNameOfSheet = frmCopySheetData.bCopySheetName
blnAddSheetsIfNecessary = True
'call the component with necessary arguments
lngResult = XLSConv1.CopySheetData(strSourceFile,
strSheetOriginal, strRangeToCopyFrom,
strTargetFile, strSheetTarget, strRangeToCopyTo,
blnDoFormula, blnCopyNameOfSheet,
blnAddSheetsIfNecessary)
Unload frmCopySheetData 'the ancillary form is unloaded