Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
workbooks.open function fails to open an existing excel file when used in ASP, but works in VB. | Excel Programming | |||
Open email windows can't open, excel shreadsheet file .xls ? | Excel Discussion (Misc queries) | |||
How do I stop Excel from closing the open file each time I open a. | Setting up and Configuration of Excel |