如何使用循环的字符串列表创建VBS for循环，而不是数字？ [英] How to create VBS for loop with list of strings to loop through, rather than numbers?

问题描述

Hi Folks -

Hi Folks -

我需要了解如何为VBS构建for循环，迭代字符串列表而不是数字。

I need to understand how to build a for loop for VBS that iterates through a list of strings, rather than numbers.

例如，我的工作簿中有15个标签，我需要以相同的方式处理。

For instance, I have 15 tabs in my Workbook that I need processed the same way.

因此，我想将所有制表符名称放在for循环中，这样我就可以重复使用同一段代码：

Therefore, I want to put all tab names in a for loop so I can reuse the same piece of code:

I想要用我的for循环替换HC_EXCEL_TAB的每个引用。

I want to replace each reference of HC_EXCEL_TAB with my for loop.

Else

	'::-- Copy content from HC_Utility file from HC_EXCEL_TAB and paste in HC_ACT_FILE --::'
	
	'::-- Get LastCell in HC_EXCEL_TAB of HC_UTIL_WB --::'
	Set objWorksheet2 = objWorkbook2.Worksheets(HC_EXCEL_TAB)
	xlUp = -4162
	LastCell2 = objWorksheet2.Range("C" & objWorksheet2.Rows.Count).End(xlUp).Row
	xlUp = -4163
	
	'::-- Data starts on Row 6 in all Workbooks --::'
	'::-- If LastCell returns less than 6, it means no data exists for that tab --::'
	
	If LastCell2 > 5 Then
	
		WScript.Echo "Tab " & HC_EXCEL_TAB & " has valid rows of data to process"
		WScript.Echo " "
		WScript.Echo "Building " & HC_ACT_FILE & " Headcount Import file for Ascend..."
		
		Set objWorkbook3= objExcel.Workbooks.Open(HC_EXCEL_SUBPATH & HC_ACT_FILE)
			
		'::-- Get LastCell in HC_ACT_FILE --::'
		Set objWorksheet3 = objWorkbook3.Worksheets("Sheet1")
		xlUp = -4162
		LastCell3 = objWorksheet3.Range("A" & objWorksheet3.Rows.Count).End(xlUp).Row
						
		objRange = objWorkbook3.Worksheets("Sheet1").Range("A2:Z" & LastCell3).Clear
			
		objWorkbook2.Worksheets(HC_EXCEL_TAB).Range("H6:M" & LastCell2).Copy
		objWorkbook3.Worksheets("Sheet1").Range("A2:F" & LastCell2).PasteSpecial -4163
			
		objworkbook3.save
		objworkbook3.close

你能帮忙吗？ 谢谢！
$

Can you assist?  Thanks!

推荐答案

我想通了：

a=Split(HC_EXCEL_TAB,"

"）
b = ubound（a）
对于i = 0至b

HC_EXCEL_TAB = a（i）
HC_ACT_FILE = a（i）& FILEXT

':: - 从HC_EXCEL_TAB复制HC_Utility文件中的内容并粘贴到HC_ACT_FILE - ::'

':: - 在HC_UTIL_WB的HC_EXCEL_TAB中获取LastCell - - ::'
设置objWorksheet2 = objWorkbook2.Worksheets（HC_EXCEL_TAB）
xlUp = -4162
LastCell2 = objWorksheet2.Range（" C"& objWorksheet2.Rows.Count）.End（ xlUp）.Row
xlUp = -4163

':: - 数据从所有工作簿的第6行开始 - ::'
':: - 如果LastCell返回小于6，表示没有HC_EXCEL_TAB的数据 - ::'

如果LastCell2> 5然后

WScript.Echo" Entity" &安培; HC_EXCEL_TAB& "有人数数据"
WScript.Echo" "
WScript.Echo" Building" &安培; HC_ACT_FILE& " Ascend FTE提交文件"
WScript.Echo" "

设置objWorkbook3 = objExcel.Workbooks.Open（HC_EXCEL_SUBPATH& HC_ACT_FILE）

':: - 在HC_ACT_FILE中获取LastCell - ::'
设置objWorksheet3 = objWorkbook3.Worksheets（" Sheet1"）
xlUp = -4162
LastCell3 = objWorksheet3.Range（"& objWorksheet3.Rows.Count）.End（xlUp）.Row

objRange = objWorkbook3.Worksheets（" Sheet1"）。Range（" A2：Z"& LastCell3）.Clear

objWorkbook2.Worksheets（HC_EXCEL_TAB）.Range（" H6：M& LastCell2）。复制
objWorkbook3.Worksheets（" Sheet1"）。范围（" A2：F& LastCell2）.PasteSpecial -4163

objworkbook3。保存
objworkbook3.close

否则

WScript.Echo" Entity" &安培; HC_EXCEL_TAB& "没有人数数据"
WScript.Echo" "
WScript.Echo"将不会构建Ascend FTE提交文件"
WScript.Echo" "

结束如果
下一步

") b=ubound(a) For i=0 to b HC_EXCEL_TAB = a(i) HC_ACT_FILE = a(i) & FILEXT '::-- Copy content from HC_Utility file from HC_EXCEL_TAB and paste in HC_ACT_FILE --::' '::-- Get LastCell in HC_EXCEL_TAB of HC_UTIL_WB --::' Set objWorksheet2 = objWorkbook2.Worksheets(HC_EXCEL_TAB) xlUp = -4162 LastCell2 = objWorksheet2.Range("C" & objWorksheet2.Rows.Count).End(xlUp).Row xlUp = -4163 '::-- Data starts on Row 6 in all Workbooks --::' '::-- If LastCell returns less than 6, it means no data exists for that HC_EXCEL_TAB --::' If LastCell2 > 5 Then WScript.Echo "Entity " & HC_EXCEL_TAB & " has Headcount Data" WScript.Echo " " WScript.Echo "Building " & HC_ACT_FILE & " Ascend FTE Submission File" WScript.Echo " " Set objWorkbook3= objExcel.Workbooks.Open(HC_EXCEL_SUBPATH & HC_ACT_FILE) '::-- Get LastCell in HC_ACT_FILE --::' Set objWorksheet3 = objWorkbook3.Worksheets("Sheet1") xlUp = -4162 LastCell3 = objWorksheet3.Range("A" & objWorksheet3.Rows.Count).End(xlUp).Row objRange = objWorkbook3.Worksheets("Sheet1").Range("A2:Z" & LastCell3).Clear objWorkbook2.Worksheets(HC_EXCEL_TAB).Range("H6:M" & LastCell2).Copy objWorkbook3.Worksheets("Sheet1").Range("A2:F" & LastCell2).PasteSpecial -4163 objworkbook3.save objworkbook3.close Else WScript.Echo "Entity " & HC_EXCEL_TAB & " has no Headcount Data" WScript.Echo " " WScript.Echo "Ascend FTE Submission File will not be built" WScript.Echo " " End If Next

HC_EXCEL_TAB包含应该处理的所有excel选项卡的列表，分隔with

HC_EXCEL_TAB consists of a list of all of the excel tabs that should be processed, delimited with

。

谢谢！

这篇关于如何使用循环的字符串列表创建VBS for循环，而不是数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！