excel去掉跨文件引用

来源：互联网发布：试用软件赚钱破解编辑：程序博客网时间：2024/06/13 05:23

如果一个工作表的单元格内容引用了其他工作表的单元格的内容，这时如果将这个工作表的内容全部拷贝到另外一个文件的工作表中，引用的公式会改变，引用地址变为绝对地址，还是指向源文件，例如：='C:\Users\YANG\Desktop\新建文件夹\[a.xlsx]Sheet1'!A4

用vba 调用 javascript 脚本去掉引用，代码如下

<pre name="code" class="java">//返回当前激活的工作表所有选中的单元格function getSelectionCells(workbook) {var cells = [];//$H$18,$M$13,$O$27,$R$19:$T$29var address = workbook.application.selection.address;var addresses = address.split(",");for(var i=0; i<addresses.length; i++) {var add = addresses[i];var isRegion = add.indexOf(":");if(isRegion==-1) {//是单独的单元格cells.push(workbook.ActiveSheet.range(add));} else {//是一块区域var regExpSelectAll = /^\$[0-9]+:\$[0-9]+$/g;var isSelectAll = regExpSelectAll.test(add);if(isSelectAll) {continue;}var regions = add.split(":");var leftUpRegion = regions[0];var rightDownRegion = regions[1];var leftUpCol = leftUpRegion.split("$")[1];var leftUpRow = leftUpRegion.split("$")[2];leftUpCol = rowNumber(leftUpCol);leftUpRow = parseInt(leftUpRow);var rightDownCol = rightDownRegion.split("$")[1];var rightDownRow = rightDownRegion.split("$")[2];rightDownCol = rowNumber(rightDownCol);rightDownRow = parseInt(rightDownRow);for(var i=leftUpCol; i<=rightDownCol; i++) {for(var j=leftUpRow; j<=rightDownRow; j++) {cells.push(workbook.ActiveSheet.cells(j, i));}}}}return cells;}//将列字符串转换成数字，例如：AA >> 27function rowNumber(rowStr) {var rowStrUpperCase = rowStr.toUpperCase();var row = 0;for(var i=0; i<rowStrUpperCase.length; i++) {var charCode = rowStrUpperCase.charCodeAt(i);charCode -= 64;row += charCode * Math.pow(26, rowStrUpperCase.length - 1 - i);}return row;}//检查字符串是否是公式并且包含绝对地址function isFormulaAndAbsolutePath(formula) {if(formula.substr(0, 1)=="=" && formula.indexOf("'")!=-1 && formula.indexOf("[")!=-1 && formula.indexOf("]")!=-1) {return true;}return false;}//单元格公式去掉引用function formulaRemoveQuote(cell) {var formula = cell.formula;if(isFormulaAndAbsolutePath(formula)) {var reg = /['][^']*\[{1}[^']*\]{1}/g;formula = formula.replace(reg, "");formula = formula.replace(/\'/g, "");cell.formula = formula;}}//去掉跨文件引用function removeQuote(workbook) {var cells = getSelectionCells(workbook);for(var i=0; i<cells.length; i++) {formulaRemoveQuote(cells[i]);}}

直接调用removeQuote就会将当前激活的工作表的所有已经选中的单元格去掉引用，可以Ctrl多选，注意要将workbook传入

removeQuote(workbook)

0 0