Open second Excel file with VBA
 

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

Open second Excel file with VBA
 

just try with ""

Upendra

On Oct 3, 4:42*pm, 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

Open second Excel file with VBA
 

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

Open second Excel file with VBA
 

Dave Peterson wrote:

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

Open second Excel file with VBA
 

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.



Gustaf wrote:

Dave Peterson wrote:

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

--

Dave Peterson

Open second Excel file with VBA
 

Dave Peterson wrote:

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