VBA workbook open debug

[csilabgirl]

Excel 2002

I have some VBA code in a workbook (workbook #1) that runs when the workbook is opened. The code subsequently opens another workbook (workbook #2) and then copies data from it and pastes it into workbook #1. The problem is sometimes the user already has workbook #2 open when they open workbook #1, so they end up getting a debug error. Is there some different code to work around this? Below is my code. Thanks for the help.

Private Sub Workbook_Open()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Dim wbOpen As Workbook<o></o>
<o></o>
Set wbOpen = Workbooks.Open(Filename:="\\Server\Chromosomal Labs\ChromoLIMS\Client List.xls")<o></o>
<o></o>
wbOpen.Sheets("Clients").Columns("D:L").Copy<o></o>
ThisWorkbook.Sheets("Clients").Columns("D:L").PasteSpecial Paste:=xlPasteValues
<o> </o>
wbOpen.Close<o></o>
Sheets("Intro").Select
End Sub<o></o>

 

[schielrn]

One way is that you could test if the workbook is open and then go from there:

http://support.microsoft.com/kb/213299

Hope that helps.

 

[VoG]

Try like this

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

Sub test()
If Not WorksheetExists("Sheet1") Then
        MsgBox "Unable to proceed", vbExclamation, "Error"
        Exit Sub
End If

 

[csilabgirl]

VoG,

Thank you for the code, but to clarify, where am I putting this new code in relation to my workbook_open code

 

[VoG]

Hi. Scrub my earlier suggestion (that was checking for existence of a sheet not a workbook. Try like this

ThisWorkbook module

Private Sub Workbook_Open()

Dim wbOpen As Workbook

If IsOpen("Client List.xls") Then
    Set wbOpen = Workbooks("Client List.xls")
Else
    Set wbOpen = Workbooks.Open(Filename:="\\Server\Chromosomal Labs\ChromoLIMS\Client List.xls")
End If
wbOpen.Sheets("Clients").Columns("D:L").Copy
ThisWorkbook.Sheets("Clients").Columns("D:L").PasteSpecial Paste:=xlPasteValues

wbOpen.Close
Sheets("Intro").Select
End Sub

Regular module

Function IsOpen(anyName As String) As Boolean
Dim wb As Workbook
On Error GoTo noFilesOpen
For Each wb In Workbooks
    If LCase(wb.Name) = LCase(anyName) Then
        IsOpen = True
        Exit Function
    End If
Next wb
Exit Function
noFilesOpen:
IsOpen = False
End Function

 

[csilabgirl]

VoG,

I used the This Workbook code and when I open the workbook (that this code is in) I get a "compile error, sub or function not defined" and it highlights this portion of the code (see in red). When I opened my workbook the "client list" excel was not open already.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim wbOpen As Workbook
If IsOpen("Client List.xls") Then
Set wbOpen = Workbooks("Client List.xls")

Thank you

 

[VoG]

Try (regular module)

Public Function IsOpen(anyName As String) As Boolean
Dim wb As Workbook
On Error GoTo noFilesOpen
For Each wb In Workbooks
    If LCase(wb.Name) = LCase(anyName) Then
        IsOpen = True
        Exit Function
    End If
Next wb
Exit Function
noFilesOpen:
IsOpen = False
End Function

 

[csilabgirl]

Vog,

That works but there is one problem. If the user already has client list open, it closes it after the workbook_open code runs. If the user already has the client list open they would not want it to be closed on them as they are probably actively working in it. I generally only want it to close client list, if it was not already open to begin with, but I guess that is a whole new set of code.

 

[VoG]

Try this

Private Sub Workbook_Open()

Dim wbOpen As Workbook
Dim WasOpen As Boolean

If IsOpen("Client List.xls") Then
    Set wbOpen = Workbooks("Client List.xls")
    WasOpen = True
Else
    Set wbOpen = Workbooks.Open(Filename:="\\Server\Chromosomal Labs\ChromoLIMS\Client List.xls")
End If
wbOpen.Sheets("Clients").Columns("D:L").Copy
ThisWorkbook.Sheets("Clients").Columns("D:L").PasteSpecial Paste:=xlPasteValues

If Not WasOpen Then wbOpen.Close
Sheets("Intro").Select
End Sub

 

[bjurney]

Something like this might do the trick

Private Sub Workbook_Open()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
Dim wbOpen As Workbook<o:p></o:p>
<o:p>On Error Resume Next</o:p>
Windows("Name of the 2nd workbook.xls").Close
Set wbOpen = Workbooks.Open(Filename:="\\Server\Chromosomal Labs\ChromoLIMS\Client List.xls")<o:p></o:p>
<o:p></o:p>
wbOpen.Sheets("Clients").Columns("D:L").Copy<o:p></o:p>
ThisWorkbook.Sheets("Clients").Columns("D:L").PasteSpecial Paste:=xlPasteValues
<o:p></o:p>
wbOpen.Close<o:p></o:p>
Sheets("Intro").Select
End Sub