Excel macro

[harri.kukkonen]

I have recorded two macroes, put them together and added if-then-else
statement, but there is something wrong. It does not work. Both macroes
work fine separately.

I try to filter data from a base workbook to an active workbook and
make it run automatically every time when the file is opened. Excel
should check if a base file is open and if so run the filter routine.
If that file is not open it should be opened and run filter.

Sub macroname()

If IsFileOpen("\\serverName\path\fileName.xls") Then

Workbooks("fileName.xls").Sheets("Sheet1").Range("rangeName1"). _
AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("rangeNames"), _
CopyToRange:=Range("rangeName3"), Unique:=False
Else

Workbooks.Open(Filename:= _
"\\serverName\path\fileName.xls").RunAutoMacros _
Which:=xlAutoOpen
ActiveWindow.WindowState = xlMaximized

Workbooks("tuotantoalueet.xls").Sheets("Sheet1").Range("trangeName1").
_
AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("rangeName2"), _
CopyToRange:=Range("rangeName3"), Unique:=False
End If
End Sub

It is not difficult or is it? Excel seems to need some function, but my
knowledge about programming excel macroes is rather weak.

Any hints or a piece of code.

Thanks in advange

hk

 

[harri.kukkonen]

Hi and thanks of the answer

I have solved my problem. There were needed a little bit more, a
function, which I copied from MS's article (ID 151336). The macro is
working correctly now, but...

Every time I run the macro and the base workbook/File is open Excel
asks me that 'if the workbook is opened again I'll lost someting
unsaved'. And I must answer 'YES'. There is no unsaved data in the
workbook, and that's why I'd like to give the answer inside the code.
How and where?

I copy my new code here.

Sub Kaavapoiminta()
'
' Kaavapoiminta Makro
' kukkoha on nauhoittanut makron 24.11.2005.
'
If
IsOpen("\\taskes000001\keto\Petri\Tuotantoalueet\tuotantoalueet.xls")
Then

Workbooks("tuotantoalueet.xls").Sheets("Taul1").Range("tuotantoalueet").
_
AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("Ehdot"), _
CopyToRange:=Range("Poimi"), Unique:=False
Else

Workbooks.Open(Filename:= _

"\\taskes000001\keto\Petri\Tuotantoalueet\tuotantoalueet.xls").RunAutoMacros
_
Which:=xlAutoOpen
ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized

Workbooks("tuotantoalueet.xls").Sheets("Taul1").Range("tuotantoalueet").
_
AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range("Ehdot"), _
CopyToRange:=Range("Poimi"), Unique:=False
End If
End Sub
Function IsOpen(Filename As String) As Boolean
Dim wb As Workbook
For Each wb In Application.Workbooks
If UCase(wb.Name) = UCase(Filename) Then
IsOpen = True
Exit Function
End If
Next wb
IsOpen = False
End Function