DO we need to format the path?

[JAtz_DA_WAY]

I am writing a macro in which I am passing the name of a file to a
function.
If I pass just the name of the file everything goes fine, but if i give
the ull path, it shows me error.

i.e .
pass("temp.xls") - > works fine
But pass("c:\temp.xls") -> shows error :"Run time error 9, Subscript
out of range"

DO we need to format the path in some special way or ???

Regards,

Jatz

 

[JAtz_DA_WAY]

full* pat

 

[Dave Peterson]

It sounds like your function pass() only wants the workbook's name--not the full
path.

If there's any code like:

dim wkbk as workbook
set wkbk = workbooks(variablenamehere)

then that could be the problem. Workbooks() likes just the workbook name--not
the full name. You could extract the name from the passed variable -- but it's
difficult to say what should be done without seeing the code and knowing what
should happen.

 

[JAtz_DA_WAY]

Thanks for the replies Dave !!

here is my code... i used the same code that you answered in my
previous post.

This code passes the name of the workbook as well as worksheet to the
the function.

It works fine if i pass the name of the file as temp.xls, but it gives
run time error 9 when I give the full path name.

Here is sample of the code:
-This statement calls the function-
CompareWorksheets
Workbooks("*c:\\temp\\temp.xls*").Worksheets("Sheet1"), _
Workbooks("c1.xls").Worksheets("Sheet1")


-This is the function that is called :-

Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long


Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
.......
........


Thanks in advance !!!
Regards,

Jatz

 

[JAtz_DA_WAY]

I also tried passing *"C:\temp\temp.xls", * but it does not work

 

[Dave Peterson]

Both Temp.xls and c1.xls must be open. So open them both (manually), then try
this line:

CompareWorksheets Workbooks("temp.xls").Worksheets("Sheet1"), _
Workbooks("c1.xls").Worksheets("Sheet1")

and both of these workbooks have to have a worksheet named sheet1.

 

[JAtz_DA_WAY]

Dave, it wroks if i open the files & then run the macro.

The only problem is that if i pass c1.xls it works.
But if i use c:\temp\c1.xls it starts giviing the runt ime error 9 i.e
Subscript out of range

Regards,

Jat

 

[Dave Peterson]

That is correct.

Both files must be opened before you run the macro.

and workbooks() needs just the filename--not the fullpath.

This requires xl2k or higher. It relies on InStrRev which was added in xl2k.

It looks to see if both workbooks are open. If either is not, it'll open it.

Option Explicit
Sub testme01()

Dim FileName1 As String
Dim FileName2 As String
Dim wkbk1 As Workbook
Dim wkbk2 As Workbook
Dim testStr As String

FileName1 = "c:\temp\temp.xls"
FileName2 = "C:\temp\c1.xls"

Set wkbk1 = Nothing
On Error Resume Next
Set wkbk1 = Workbooks(Mid(FileName1, InStrRev(FileName1, "\") + 1))
On Error GoTo 0

If wkbk1 Is Nothing Then
'filename1 isn't opened
testStr = ""
On Error Resume Next
testStr = Dir(FileName1)
On Error GoTo 0

If testStr = "" Then
MsgBox FileName1 & " isn't open and doesn't exist!"
Exit Sub
Else
Set wkbk1 = Workbooks.Open(FileName1)
End If
End If

Set wkbk2 = Nothing
On Error Resume Next
Set wkbk2 = Workbooks(Mid(FileName2, InStrRev(FileName2, "\") + 1))
On Error GoTo 0

If wkbk2 Is Nothing Then
'filename1 isn't opened
testStr = ""
On Error Resume Next
testStr = Dir(FileName2)
On Error GoTo 0

If testStr = "" Then
MsgBox FileName2 & " isn't open and doesn't exist!"
Exit Sub
Else
Set wkbk2 = Workbooks.Open(FileName2)
End If
End If

'if you've made it this far, you can compare the two sheets.
'but they both have to have a worksheet named sheet1!

CompareWorksheets _
wkbk1.Worksheets("Sheet1"), _
wkbk2.Worksheets("Sheet1")

End Sub
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
'your code here
End Sub

if you're using xl97, you can change both instances of instrrev to instrrev97
and add this function to the bottom of that module.

Function InStrRev97(mystr As Variant, mydelim As String) As Long
Dim i As Long
InStrRev97 = 0
For i = Len(mystr) To 1 Step -1
If Mid(mystr, i, 1) = mydelim Then
InStrRev97 = i
Exit Function
End If
Next i
End Function

 

[JAtz_DA_WAY]

Thanks a lot Dave for all your help. I really appreciate this!!

Regards,

Jatz