none
Help with Runtime Error : Unknown runtime error (VB Script) RRS feed

  • Question

  • 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


    Sunday, July 2, 2017 10:11 PM

All replies

  • 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


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 3, 2017 5:42 AM
  • 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


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thank you so much for your reply!  So, yes that is it.  However, the reason I used 4163 is so I could paste as values? I source range I"m taking it from use formulas therefore I need to paste as values in the target.  Any ideas?

    I'll go ahead and change the other peices of code as you mentioned, thank you!!!!

    Monday, July 3, 2017 7:30 AM
  • I was able to acheive it with this code:

    xlUp = -4162
    LastCell = objWorksheet2.Range("C" & objWorksheet2.Rows.Count).End(xlUp).Row
    xlUp = -4163
    objWorkbook2.Worksheets(HC_EXCEL_TAB).Range("H6:M10000").Copy
    objWorkbook3.Worksheets("Sheet1").Range("A2:F" & LastCell).PasteSpecial -4163
    

    But not sure if this is best practice?  Thanks!
    Monday, July 3, 2017 10:49 AM
  • You don't use the second value of xlUp - you already used the literal value -4163 in the PasteSpecial line. So you can omit the line

    xlUp = -4163


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 3, 2017 3:27 PM
  • Works great, thank you so much!!!
    Monday, July 3, 2017 6:01 PM
  • Works great, thank you so much!!!

    Hello,

    I suggest you mark helpful post as answer to close this thread.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 2:03 AM
    Moderator