• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

Error on .Range ???

I'm getting an "unknown runtime error" on line 22 below.  Can someone help me out?
 
' Open the TOAD Excel Spreadsheet
Dim strPsoftExtract
strPsoftExtract = "Y:\HT_BI_HEE_EXTRACT_V2.xls"
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(strPsoftExtract)
Dim frmla
Dim i 
Dim rg 
objExcel.ScreenUpdating = False

'Insert column headers in col Z
objExcel.Cells(1, 25).Value = "ORG UNIT1 NAME"

With objExcel.ActiveSheet
	Set rg = .Range("Y2") 
	Set rg = .Range(rg, .Cells(.Rows.Count, rg.Column).End(-4162)) 'xLUp = -4162

'This inserts the VLOOKUP formula into cell Z2, AA2.
	For i = 1 To 2
    	frmla = "=VLOOKUP($Y2,'J:\[ManagerLevels.xls]sheet1!$A$2:$D$16," & (2 * i) & ",FALSE)"
    	.Range("Z2").Offset(0, i - 1).Formula = frmla
	Next
End With

'This copies the formula all the way down columns Z and AA.
'and then replaces the formula result with the value only (like doing Paste Special --> Values)
With rg.Offset(0, 25).Resize(, 2)
    .FillDown
    .Formula = .Value
End With

objExcel.ScreenUpdating = True

' Retrieve the separate Date components. 
Dim TodayYYYY, TodayMM, TodayDD
TodayYYYY = Year(Date) 
TodayMM   = Month(Date) 
TodayDD   = Day(Date) 

' Save the sheet - appending TodaysDate to the end of the file-name.
objWorkbook.SaveAs "Y:\HT_BI_HEE_EXTRACT_V2_" & TodayYYYY & "-" & TodayMM & "-" & TodayDD & ".xls"

' Delete the original Psoft Extract
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile strPsoftExtract, True

' Close Excel with the Quit method on the Application object.
objWorkbook.Application.Quit

' Release the object variable.
Set objExcel = Nothing

Open in new window

0
OGSan
Asked:
OGSan
  • 4
  • 2
1 Solution
 
Patrick MatthewsCommented:
Are you absolutely certain that on Drive J there is a file with that name?

Are you absolutely certain that, if that file exists, it has a worksheet named sheet1?

Is that file password protected?

If J is a mapped network drive, is it possible that the account running the VBScript does not have the same drive mapping for J?  (Have you tried using a UNC path instead?)
0
 
OGSanAuthor Commented:
Hmm, I assumed that line 14 ("With objExcel.ActiveSheet") was referring to the file defined on line 3 ("strPsoftExtract = "Y:\HT_BI_HEE_EXTRACT_V2.xls")...?
I took your suggestion and changed the mapped drive references into UNC path names.
I still get an error on the same line - but it says, "invalid flags."  ???
0
 
Patrick MatthewsCommented:
What does line 14 have to do with anything?  You asked about the error on Line 22.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OGSanAuthor Commented:
Line 14 is where the With loop begins that the .Range instruction refers to on Line 22...doesn't it?  Am I way off track?  Let me know because I certainly don't.  Thanks!
0
 
OGSanAuthor Commented:
Found it - Line 20 had a missing single quote, needed following the sheet1 reference.
0
 
OGSanAuthor Commented:
Found bad reference
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now