帮助运行时错误：未知的运行时错误（VB脚本） [英] Help with Runtime Error : Unknown runtime error (VB Script)

问题描述

嗨伙计 -

我这里有一个VB脚本，我遇到了运行时错误。 问题发生在代码的"Else"部分。 我在脚本中有其他类似的代码，所以不确定为什么这一部分会导致我出现问题？
b

请你推荐一些必要的改变吗？这行得通？

谢谢！

'错误恢复下一个
 set objexcel = createobject（" Excel.Application"）
 objExcel.Visible = True 
 
 Dim objexcel 
 dim objworkbook1，objworkbook2，objworkbook3，objworksheet，objWorksheet1，objWorksheet2，objWorksheet3 
 dim i，startrow ，intnew，endrow，countheet 
 dim Firstcell，Lastcell 
 
 Dim args，HC_EXCEL_PREPPATH，HC_EXCEL_SUBPATH，HC_TEMP_UTIL_WB，HC_UTIL_WB，HC_ACT_FILE，HC_EXCEL_TAB 
设置args = Wscript.arguments 
 
 HC_EXCEL_PREPPATH = args（0）
 HC_EXCEL_SUBPATH = args（1）
 HC_TEMP_UTIL_WB = args（2）
 HC_UTIL_WB = args（3）
 HC_ACT_FILE = args（4） 
 HC_EXCEL_TAB = args（5）
 
设置objWorkbook2 = objExcel.Workbooks.Open（HC_EXCEL_SUBPATH& HC_UTIL_WB）
 
如果HC_EXCEL_TAB =" Start"然后是
 
 WScript.Echo"正在更新" &安培; HC_UTIL_WB& "with" &安培; HC_TEMP_UTIL_WB 
 
设置objworkbook1 = objExcel.Workbooks.Open（HC_EXCEL_PREPPATH& HC_TEMP_UTIL_WB）
 
'::  - 清除HC_UTIL_WB中指定的范围 -  ::'
 objRange = objWorkbook2.Worksheets（" HC_Input_Data"）。范围（" A2：E30000"）。清除
 
'::  - 将所有表格放入HC_TEMP_UTIL_WB中的for循环 -  ::'
 countSheet = objworkbook1.Sheets.Count 
 intnewrow = 2 
i = 1 
 for i = 1 to countheet 
 Set objWorksheet = objWorkbook1.WorkSheets（i）
 objworksheet.Activate 
 startrow = 2 
 
'::  - 计算使用的行数 -  ::'
 endrow = objWorkbook1.Worksheets（i）.UsedRange.Rows .Count 
 
'::  - 复制数据 -  ::'
 objWorkbook1.Worksheets（i）.Range（"& startrow&"：E" & endrow）。复制
 
'::  - 粘贴在HC_UTIL_WB  -  ::'
 objWorkbook2.Worksheets（" HC_Input_Data"）。Cells（intNewRow，1）.Pastespecial 
 
'::  - 增加row  -  ::'
 intNewRow = intNewRow +（endrow  -  startrow + 1）
 
 objWorkbook2.RefreshAll 
 
'::  - 调整公式列以匹配A列上的最后一行 -  ::'
设置objWorksheet1 = objWorkbook2.Worksheets（" HC_Input_Data"）
 xlUp = -4162 
设置LastCell = objWorksheet1.Range（" A" &安培; objWorksheet1.Rows.Count）。End（xlUp）
 Set SourceRange = objWorksheet1.Range（" H2：O2"）
 Set FillRange = objWorksheet1.Range（" H2：O"& LastCell。行）
 SourceRange.AutoFill FillRange 
 
'::  -  TEMP_Utility可以保存并关闭::  - '
'::  - 无需再与TEMP_Utility交互对于这个过程 -  ::'
 
 objworkbook1.save 
 objworkbook1.close 
 objworkbook2.save 
 objworkbook2.close 
 
下一个
 
其他
 
 WScript.Echo"建筑物" &安培; HC_ACT_FILE& "文件..." 
 
设置objWorkbook3 = objExcel.Workbooks.Open（HC_EXCEL_SUBPATH& HC_ACT_FILE）
 objRange = objWorkbook3.Worksheets（" Sheet1"）。范围（" A2：Z10000"）。清除
 
'::  - 从HC_EXCEL_TAB复制HC_Utility文件中的内容并粘贴到HC_ACT_FILE  -  ::'
 
设置objWorksheet2 = objWorkbook2.Worksheets（HC_EXCEL_TAB）
 Set objWorksheet3 = objWorkbook3.Worksheets（" Sheet1"）
 
 xlUp = -4163 
设置LastCell = objWorksheet2.Range（" C"& objWorksheet2.Rows.Count）.End（xlUp ）
 Set SourceRange = objWorksheet2.Range（" H6：M10000"）
 Set FillRange = objWorksheet3.Range（" A2：F"&LastCell.Row）
 
 SourceRange.Copy 
 FillRange.PasteSpecial -4163 
 
'::  - 保存并关闭HC_ACT_FILE  -  ::'
 objworkbook3.save 
 objworkbook3.close 
 
结束如果
 
 objworkbook2.save 
 objworkbook2.close 
 objExcel.Application.Quit 
 WScript.Quit 

解决方案

在其他部分，您有

    xlUp = -4163

应该是

    xlUp = -4162

顺便说一句，我要更改

    objRange = objworkbook2.Worksheets（" HC_Input_Data"）。范围（" A2：E30000"）。清除

到

     objworkbook2.Worksheets（" HC_Input_Data"）。范围（" A2：E30000"）。清除

和

    objRange = objworkbook3.Worksheets（" Sheet1"）。范围（" A2：Z10000"）。清除

到

     objworkbook3.Worksheets（QUOT;工作表Sheet"）范围。（QUOT; A2：Z10000"）清除

Hi Folks -

I have a VB Script here that I'm getting a run-time error on.  The issue is happening within the 'Else' portion of the code.  I ave other similar code in the script so not sure why this section is causing me issues?

Can you please recommend the necessary change(s) to make it work?

Thank you!

'on error resume next
set objexcel = createobject ("Excel.Application")
objExcel.Visible = True

Dim objexcel
dim objworkbook1, objworkbook2, objworkbook3, objworksheet, objWorksheet1, objWorksheet2, objWorksheet3
dim i, startrow, intnewrow, endrow, countsheet
dim Firstcell, Lastcell

Dim args, HC_EXCEL_PREPPATH, HC_EXCEL_SUBPATH, HC_TEMP_UTIL_WB, HC_UTIL_WB, HC_ACT_FILE, HC_EXCEL_TAB
Set args = Wscript.arguments

HC_EXCEL_PREPPATH= args(0)
HC_EXCEL_SUBPATH= args(1)
HC_TEMP_UTIL_WB= args(2)
HC_UTIL_WB= args(3)
HC_ACT_FILE= args(4)
HC_EXCEL_TAB= args(5)

Set objWorkbook2= objExcel.Workbooks.Open(HC_EXCEL_SUBPATH & HC_UTIL_WB)
	
If HC_EXCEL_TAB = "Start" Then

	WScript.Echo "Updating " & HC_UTIL_WB & "with " & HC_TEMP_UTIL_WB
	
	Set objworkbook1= objExcel.Workbooks.Open(HC_EXCEL_PREPPATH & HC_TEMP_UTIL_WB)
	
	'::-- Clear specified range in HC_UTIL_WB --::'
	objRange = objWorkbook2.Worksheets("HC_Input_Data").Range("A2:E30000").Clear
	
	'::-- Put all Sheets in for loop in HC_TEMP_UTIL_WB --::'
	countSheet = objworkbook1.Sheets.Count
	intnewrow=2
	i = 1
	for i = 1 to countsheet
	Set objWorksheet = objWorkbook1.WorkSheets(i)
	objworksheet.Activate
	startrow = 2

	'::-- Count the number of used rows --::'
	endrow = objWorkbook1.Worksheets(i).UsedRange.Rows.Count

	'::-- Copy the data --::'
	objWorkbook1.Worksheets(i).Range("A" & startrow &":E"& endrow).Copy
	
	'::-- Paste in HC_UTIL_WB --::'
	objWorkbook2.Worksheets("HC_Input_Data").Cells(intNewRow,1).Pastespecial

	'::-- Increment the row --::'
	intNewRow = intNewRow + (endrow - startrow + 1)

	objWorkbook2.RefreshAll

	'::-- Adjust formula columns to match last row on Column A --::'
	Set objWorksheet1 = objWorkbook2.Worksheets("HC_Input_Data")
	xlUp = -4162
	Set LastCell = objWorksheet1.Range("A" & objWorksheet1.Rows.Count).End(xlUp)
	Set SourceRange = objWorksheet1.Range("H2:O2")
	Set FillRange = objWorksheet1.Range("H2:O" & LastCell.Row)
	SourceRange.AutoFill FillRange
	
	'::-- TEMP_Utility can be saved and closed ::--'
	'::-- No need to interact with TEMP_Utility anymore for this process --::'
	
	objworkbook1.save
	objworkbook1.close
	objworkbook2.save
	objworkbook2.close
	
	Next

Else

	WScript.Echo "Building " & HC_ACT_FILE & " file..."

	Set objWorkbook3= objExcel.Workbooks.Open(HC_EXCEL_SUBPATH & HC_ACT_FILE)
	objRange = objWorkbook3.Worksheets("Sheet1").Range("A2:Z10000").Clear
	
	'::-- Copy content from HC_Utility file from HC_EXCEL_TAB and paste in HC_ACT_FILE --::'
	
	Set objWorksheet2 = objWorkbook2.Worksheets(HC_EXCEL_TAB)
	Set objWorksheet3 = objWorkbook3.Worksheets("Sheet1")
	
	xlUp = -4163
	Set LastCell = objWorksheet2.Range("C" & objWorksheet2.Rows.Count).End(xlUp)
	Set SourceRange = objWorksheet2.Range("H6:M10000")
	Set FillRange = objWorksheet3.Range("A2:F" & LastCell.Row)

	SourceRange.Copy
	FillRange.PasteSpecial -4163
		
	'::-- Save and Close HC_ACT_FILE --::'
	objworkbook3.save
	objworkbook3.close

End If

objworkbook2.save
objworkbook2.close
objExcel.Application.Quit
WScript.Quit

解决方案

In the Else part, you have

    xlUp = -4163

It should be

    xlUp = -4162

By the way, I'd change

    objRange = objworkbook2.Worksheets("HC_Input_Data").Range("A2:E30000").Clear

to

    objworkbook2.Worksheets("HC_Input_Data").Range("A2:E30000").Clear

and

    objRange = objworkbook3.Worksheets("Sheet1").Range("A2:Z10000").Clear

to

    objworkbook3.Worksheets("Sheet1").Range("A2:Z10000").Clear

这篇关于帮助运行时错误：未知的运行时错误（VB脚本）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！