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
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
Answers:
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?
Answers:
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
Answers:
Iterate the Workbooks collection. Something like:
Code:
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 ?
Answers:
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 ?
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:
Code:
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.
Answers:
Based on Chip's code, my humble offering -
Code:
Code:
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.
Answers:
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
Answers:
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:
Code:
Code:
Sub CopyInfo(sBookName as String)And then later uses:
Code:
Last one - promise ;-)
The CopyInfo code shown is just a portion of the entire CopyInfo code. I have made all the necessary changes to the entire code and it all works
However, when my code shuts down the variable workbook it asks if I want to keep the information previously copied on the clipboard. I don't need this kept - can I
a) remove it from the clipboard automatically and prevent this message
OR
b) just prevent this message appearing and keep the info on the clipboard (it really won't matter that much
Thanks in advance
Answers:
It seems from a brief search that clearing the clipboard is rather difficult using VBA.
You could try (add in code and use API calls)
Or you could try an (add in a forms reference and use it)
Answers:
Not sure if this will work, but try:
Application.CutCopyMode = False
Answers:
Thanks All
Mobeer's first suggestion did the trick - many thanks
Now have my spreadsheet working exactly as I want it.
This is a great forum - ask a question, get it sorted - just what it says on the tin
Absolutely fantastic
:beer;