I'd use:

sheetName = ActiveWorkbook.ActiveSheet.Name
set ws = nothing
on error resume next
Set ws = xlTmp.Sheets(sheetName)
on error goto 0
If ws is nothing Then



Gustaf wrote:

My code aims to open a temp copy of another Excel file (declared globally as 'file') and get values from the sheet with the same name as the active sheet in the first document. My attempt:

Private Sub cmdOpen_Click()

Dim fso As New FileSystemObject ' Requires reference to Microsoft Scripting Runtime
Dim path As String
Dim xlTmp As Excel.Application
Dim ws As Worksheet
Dim temp As String
Dim sheetName As String

' Create a temp file
path = fso.GetParentFolderName(file)
temp = path & "\temp.xls"
fso.CopyFile file, temp

' Open temp file in read-only mode
Set xlTmp = New Excel.Application
xlTmp.Workbooks.Open temp, , True

' Find the corresponding sheet in temp file
sheetName = ActiveWorkbook.ActiveSheet.Name
Set ws = xlTmp.Sheets(sheetName)
If ws = Null Then
MsgBox "This workbook doesn't have a sheet named '" & sheetName & "'.", vbExclamation, "Error"
End If

End Sub

VBA won't understand the line

If ws = Null Then

and I have no idea how to make it happy. I get the object doesn't support property/method error. Tried 'Nothing' too. Any suggestions? Other suggestions on the code are welcome too.

Gustaf

--

Dave Peterson