G
Gustaf
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
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