Open second Excel file with VBA

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
 
D

Dave Peterson

I'd use:

sheetName = ActiveWorkbook.ActiveSheet.Name
set ws = nothing
on error resume next
Set ws = xlTmp.Sheets(sheetName)
 
G

Gustaf

Dave said:
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

Great, it works! But what's the meaning of "On Error GoTo 0" here? It appears the only error I need to look out for is that sheetName is out of range.

Gustaf
 
D

Dave Peterson

The "on error goto 0" line is the way you tell VBA that you don't want to ignore
any more errors. And that you don't want to handle them if they occur. You're
willing to accept whatever excel/vba does when it sees an error.

There are lots of errors that could arise in your code. You may not have enough
space on the disk to do the copy. You may not have access rights to do the
copy. Maybe the user didn't install Scripting (as a security measure???).

You'll have to decide if those are important--and how you'd want to handle them
if they are.
 
G

Gustaf

Dave said:
The "on error goto 0" line is the way you tell VBA that you don't want to ignore
any more errors. And that you don't want to handle them if they occur. You're
willing to accept whatever excel/vba does when it sees an error.

Got it. Thanks!

Gustaf
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top