How to transfer path name from fNameAndPath to open this workbook in the same macro

[varan]

Hi I've requirement wherein the user chooses the workbook to be open from the excel sheet and then a mathematical calculation is made. The issue is I'm able to run the first part of the code properly and the fNameAndPath has the workbooks name and path but I need to open this workbook in the next line of the code to run the next part of the code. Need help in solving this issue as i get this error [h=1]Subscript out of range (Error 9)[/h]when I try to open the workbook



Private Sub CommandButton1_Click()
Dim fNameAndPath As Variant
Dim wb As Workbook
Dim wbb As Workbook


Dim ary1 As Variant
Dim ary2 As Variant
Dim sum As Double
Dim i As Long


fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
sFileName = fNameAndPath
MsgBox Workbooks("pxwebreport.xls").Path


Workbooks.Open ("sFileName")
With ThisWorkbook.Worksheets("pxwebreport")
ary1 = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
ary2 = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
For i = 1 To UBound(ary1)
If IsNumeric(ary1(i, 1)) And IsNumeric(ary2(i, 1)) Then
sum = sum + ary1(i, 1) * ary2(i, 1)
sum1 = WorksheetFunction.sum(ary2)
End If
Next
.Range("G22") = sum / sum1
End With
End Sub

 

[gallen]

I'm confused, You want to open the workbook that is running the code (meaning it must already be open?)

 

[Norie]

Where in the code do you get the error?

By the way, I'm pretty sure sFileName shouldn't have quotes around it here.

Workbooks.Open ("sFileName")

 

[varan]

Hi Gallen,
The user first chooses the file and it opens but the idea is to not create a macro in the opened file to make the calculation. Thus I thought I will copy the path of the file opened and then reopen it again in the code and compute it.

 

[varan]

Hi Norie
It is in Workbooks.Open ("sFileName") line