Excel Visual Basic Help

jghal

Hi all

I am hoping that you excel wizards will be able to help me out (again). I got the basis of the code below online in a free excel help webpage.

The code checks to see if a particular excel file is open and if it is then calls another macro (to copy and past some of the contents of the target file) into action.

However what I want to do is to make the file name which is checked for variable - ie in the example below as long as "2830 V 6 dec 05.xls" OR "2830 V 6 dec 06.xls" (or any other year) is open then the 'CopyInfo' macro should be called.

Many thanks in advance

:beer:



Sub IsWorkBookOpenDec()


'Test to see if a Workbook is open.


Dim wBook As Workbook

On Error Resume Next
Set wBook = Workbooks("2830 V 6 Dec.xls")

If wBook Is Nothing Then 'Not open
Range("f3").Select
MsgBox "'2830 V 6 Dec.xls' is not open", _
vbCritical, ""
Set wBook = Nothing
On Error GoTo 0
Else 'It is open
Call CopyInfo
On Error GoTo 0
End If
End Sub

crox

Sub IsWorkBookOpenDec()


'Test to see if a Workbook is open.


Dim wBook As Workbook
Dim wBookName As String

On Error Resume Next

wBookName = InputBox("Enter a name")
Set wBook = Workbooks(wBookName)


or

Sub IsWorkBookOpenDec(wBookName As String)


'Test to see if a Workbook is open.


Dim wBook As Workbook

On Error Resume Next

Set wBook = Workbooks(wBookName)

kind of thing?

Wombat

If the range of years is known, then maybe something like this?:

Sub IsWorkBookOpenDec()


'Test to see if a Workbook is open.


Dim wBook As Workbook

On Error Resume Next
For i=2006 to 2020
strFileName = "2830 V 6 Dec" & STR(i) & ".xls"
Set wBook = Workbooks(strFileName)

If wBook Is Nothing Then 'Not open
Range("f3").Select
MsgBox "'2830 V 6 Dec.xls' is not open", _
vbCritical, ""
Set wBook = Nothing
On Error GoTo 0
Else 'It is open
Call CopyInfo
On Error GoTo 0
End If
Next i
End Sub

Chippy_Minton

Iterate the Workbooks collection. Something like:

Sub IsWorkBookOpenDec()

Dim wBook As Workbook

For Each wBook In Workbooks
    If Left(wBook.Name, 12) = "2830 V 6 Dec" Then
        'Workbook open
    Else
        'Not open
    End If
Next

End Sub

Wombat

Nice one, Chippy. I prefer yours to mine. No hard coding of the year values needed. :beer:

jghal

Thanks crox, Wombat and Chippy

I have got Chippy's verson to sort of work. My coding is now:


Sub IsWorkBookOpenDec()

Dim wBook As Workbook

For Each wBook In Workbooks
If Left(wBook.Name, 12) = "2830 V 6 Dec" Then
'Workbook open
Call CopyInfo
Else
'Not open
Range("f3").Select
MsgBox "'2830 V 6 Dec.xls' is not open", _
vbCritical, ""
End If
Next

End Sub

However, this coding is contained in a Workbook called "2830 V 6 GWS.xls" which is open alongside the Workbook "2830 V 6 Dec 06.xls". Because of the

For Each wBook In Workbooks

in the code, the "2830 V 6 GWS.xls" is being checked and throws up the message box. any way I can get the coding to ignore the "2830 V 6 GWS.xls" workbook ?

:beer:

Mobeer

jghal wrote:

Thanks crox, Wombat and Chippy

I have got Chippy's verson to sort of work. My coding is now:

...

However, this coding is contained in a Workbook called "2830 V 6 GWS.xls" which is open alongside the Workbook "2830 V 6 Dec 06.xls". Because of the

For Each wBook In Workbooks

in the code, the "2830 V 6 GWS.xls" is being checked and throws up the message box. any way I can get the coding to ignore the "2830 V 6 GWS.xls" workbook ?

:beer:

You could just rename the "2830 V 6 GWS.xls" workbook to something else?
Or you could put another If statement into your code to just ignore this filename:

Sub IsWorkBookOpenDec()

Dim wBook As Workbook

For Each wBook In Workbooks
    If wBook.Name = "2830 V 6 GWS.xls" Then
        'do nothing - this is the special workbook
    Else
        If Left(wBook.Name, 12) = "2830 V 6 Dec" Then
            'Workbook open
            Call CopyInfo
        Else
            'Not open
            Range("f3").Select
            MsgBox "'2830 V 6 Dec.xls' is not open", _
            vbCritical, ""
        End If
    End If
Next

End Sub

Note I haven't tested this myself.

spotter

Based on Chip's code, my humble offering -

Sub IsWorkBookOpenDec()
    Dim wBook As Workbook
    Dim wBOpen As Boolean

    For Each wBook In Workbooks
        If Left(wBook.Name, 12) = "2830 V 6 Dec" Then wBOpen = True
    Next wBook
    
    If wBOpen Then
        CopyInfo
    Else
        Range("f3").Select
        MsgBox "'2830 V 6 Dec.xls' is not open"
    End If

End Sub

Chippy_Minton

Just ignore the current workbook:

Sub IsWorkBookOpenDec()

Dim wBook As Workbook

For Each wBook In Workbooks
    If Left(wBook.Name, 12) = "2830 V 6 Dec" Then
        'Workbook open
    ElseIf wBook.Name <> ActiveWorkbook.Name Then
        'Not open
    End If
Next

End Sub

And fill in the rest.

jghal

Many thanks all - have got this portion of my code working exactly as I want it now...however...

I know I'm taking the biscuit now, but...

The CopyInfo macro which is referred to in the earlier posts looks like this;

Windows("2830 V 6 Dec 06.xls").Activate
Range("K2:M2").Select
Selection.Copy
Windows("2830 V 6 GWS.xls").Activate
Range("K2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


in the first line you can see the code refers to a specific spreadsheet "2830 V 6 Dec 06.xls"

for similar reasons to the first query...how can I make this code simply refer to any other open workbook which can have similar names with a varying year at the end

Thanks again :beer:

Mobeer

I think you need a variable to represent the file name in the CopyInfo macro, which can then be passed in by othe other code.

So the first piece of code would change. For the moment I'll use the code I posted earlier though you could do the same sort of thing with the other suggestions as well:

Sub IsWorkBookOpenDec()

Dim wBook As Workbook
Dim sBookName as String

For Each wBook In Workbooks
    sBookName = wBook.Name
    If sBookName = "2830 V 6 GWS.xls" Then
        'do nothing - this is the special workbook
    Else
        If Left(sBookName, 12) = "2830 V 6 Dec" Then
            'Workbook open
            Call CopyInfo(sBookName)
        Else
            'Not open
            Range("f3").Select
            MsgBox "'2830 V 6 Dec.xls' is not open", _
            vbCritical, ""
        End If
    End If
Next

End Sub

Then the code in CopyInfo changes to start:

Sub CopyInfo(sBookName as String)

And then later uses:

    Windows(sBookName).Activate
    Range("K2:M2").Select
    Selection.Copy
    Windows("2830 V 6 GWS.xls").Activate
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False