Test if workbook already opened, still able to write to it?

Dougmeister · Mar 7, 2011

The Background

I have code that will open a Microsoft Excel workbook from Microsoft Access and copy values to it.

I added code last week to test if the Excel workbook was already open. If it is, it will of course not reopen it, but rather switch focus to the workbook.

The Problem

Can I somehow tweak the code that I have to write data to the "already-opened" workbook?

In a nutshell, this is what I'm doing to the workbook already:

Code:

    Dim xl      As Excel.Application
    Dim xlw     As Excel.Workbook
    Dim xls     As Excel.Worksheet

    Set xl = New Excel.Application
    Set xlw = xl.Workbooks.Open(strFileName)
    xl.Visible = True
    xl.UserControl = True
    Set xls = xlw.Worksheets(1)
    
    With xls <do stuff="" to="" the="" workbook=""><do stuff="" to="" the="" workbook="">
      .
      .
      .
    End With

But (and maybe it's just Monday morning and my brain is foggy...), if I use the *following* code to bring the already-opened window into focus FIRST, then I don't have any context via which to write to the file, correct?

Code:

        Dim hndl As Long
        Dim wPos As WINDOWPLACEMENT
        hndl = FindWindow(vbNullString, <name of="" my="" workbook="" w="" out="" .xls="">)
        If (hndl = 0) Then
            hndl = FindWindow(vbNullString, <what shows="" up="" my="" workbook="" title="" bar,="" in="" quotes,="" of="" course="">)
        End If
        
        WinToMax = VBGetWindowTextLine(hndl)                  
        MaximizeWindow = True
        hndl = FindWindow(vbNullString, WinToMax)
        If (hndl = False) Or (WinToMax = "") Then
            'not sure what to do here, but it works...
        Else
            GetWindowPlacement hndl, wPos
            wPos.showCmd = 1 ' 3 or 2
            SetWindowPlacement hndl, wPos
            'AppActivate WinToMax ' ? don't need this?
        End If

Am I making any sense?

I could always close it </what></name></do></do>programmatically <do stuff="" to="" the="" workbook=""><do stuff="" to="" the="" workbook=""><name of="" my="" workbook="" w="" out="" .xls=""><what shows="" up="" my="" workbook="" title="" bar,="" in="" quotes,="" of="" course="">then re-open it, but that seems rather kludgy. Is there a better way?

Thanks.
</what></name></do></do>

xenou · Mar 7, 2011

This is untested, but you may be able to grab the current instance of Excel and find out if it has the open workbook already:

UNTESTED:

Code:

[COLOR="Navy"]Sub[/COLOR] Foo()
    [COLOR="Navy"]Dim[/COLOR] XL           [COLOR="Navy"]As[/COLOR] Excel.Application
    [COLOR="Navy"]Dim[/COLOR] xlw          [COLOR="Navy"]As[/COLOR] Excel.Workbook
    [COLOR="Navy"]Dim[/COLOR] xls          [COLOR="Navy"]As[/COLOR] Excel.Worksheet
    [COLOR="Navy"]Dim[/COLOR] strName      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] strPath      [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    
    strName = "MyWorkbook.xls"
    strPath = "C:\Folder\MyWorkbook.xls"
    
    [COLOR="Navy"]Set[/COLOR] XL = GetObject(, "Excel.Application")
    [COLOR="Navy"]With[/COLOR] XL
        [COLOR="Navy"]If[/COLOR] WorkbookIsOpen(XL, strName) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] xlw = XL.Workbooks(strName)
            [COLOR="Navy"]Set[/COLOR] xls = xlw.Worksheets(1)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] xlw = XL.Workbooks.Open(strPath)
            XL.Visible = True
            XL.UserControl = True
            [COLOR="Navy"]Set[/COLOR] xls = xlw.Worksheets(1)
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
    [COLOR="Navy"]With[/COLOR] xls
        [COLOR="SeaGreen"]'[/COLOR]
        [COLOR="SeaGreen"]'[/COLOR]
        [COLOR="SeaGreen"]'[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'--------------------------------------------------[/COLOR]
[COLOR="Navy"]Function[/COLOR] WorkbookIsOpen(ByRef XL [COLOR="Navy"]As[/COLOR] Excel.Application, sWBName [COLOR="Navy"]As[/COLOR] String)
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    s = XL.Workbooks(sWBName).Name
    [COLOR="Navy"]If[/COLOR] Err [COLOR="Navy"]Then[/COLOR]
        WorkbookIsOpen = False
    [COLOR="Navy"]Else[/COLOR]
        WorkbookIsOpen = True
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Function[/COLOR]

I think this is not a good idea though. Why would it be open? What's someone doing with it? Maybe they are editing it or maybe you will be overwriting data that was just entered.

ξ

Test if workbook already opened, still able to write to it?

Dougmeister

New Member

Excel Facts

xenou

MrExcel MVP

Similar threads

Forum statistics

Share this page

Test if workbook already opened, still able to write to it?

Dougmeister

New Member

Excel Facts

xenou

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock